SQL Code

Cursor Details

Great for understanding queries from Tableau.  The first query finds all of the active cursors, good for know what is still active, and the number of records being transfered.  The second query allows you to find the queries that Tableau generates.

select a.xid,
          a.pid,
          a.userid AS user_id,
          usr.usename AS username,
          min(a.starttime) AS start_time,
          DATEDIFF(second, min(a.starttime), getdate()) AS run_time,
          min(a.byte_count) AS bytes_in_result_set,
          round(min(a.byte_count) / pow(1024, 2), 2) AS mb_in_result_set,
          round(min(a.byte_count) / pow(1024, 3), 2) AS gb_in_result_set,
          min(a.row_count) AS row_count,
          min(a.row_count – a.fetched_rows) AS remaining_rows_to_fetch,
          min(a.fetched_rows) AS fetched_rows,
          listagg(b.text) WITHIN group (order by b.starttime, b.sequence) AS query
from STV_ACTIVE_CURSORS a join
         STL_UTILITYTEXT b on a.pid = b.pid and a.xid = b.xid and b.text != ‘begin;’ join 
         PG_USER usr on usr.usesysid = a.userid
group by a.userid, a.xid, a.pid, usr.usename;

select a.userid,
          trim(a.query) as query,
          a.xid,
          a.pid,
          a.starttime,
          a.endtime,
          a.aborted,
          c.rows,
          to_char(timestamp with time zone ‘epoch’ + datediff(s, a.starttime, a.endtime) * interval ‘1 second’, ‘MI:SS’) run_time, listagg(b.text) WITHIN GROUP (ORDER BY b.starttime, b.sequence) AS querydetails
from stl_query a inner join
         stl_utilitytext b on a.pid = b.pid and a.xid = b.xid left outer join
         (select query, sum(rows) as rows from stl_return where tasknum = 0 group by 1) c on a.query = c.query
where a.starttime > dateadd(m, -15, sysdate)                     –queries in last 15 minutes
    and a.xid in (select xid from stl_query where lower(querytxt) like ‘fetch%’)
    and sequence < 100
group by 1,2,3,4,5,6,7,8,9
order by 5 desc;

Table Details

We use this query daily for understanding the size of tables, number of deleted rows, and distribution styles.  Many more details included….

select table_id, schema, name, visible_rows, total_rows, sorted_rows, deleted_rows,
          case when total_rows > 0 and deleted_rows > 0 then ((deleted_rows::numeric(18,2) / total_rows)*100)::numeric(18) else 0 end as pct_deleted,
          tbl_size, columns, “scans:rr:fil:sel:del”, last_scan, encoded, owner, max_varchar, min_rows, max_rows, skew_rows, stats_off, diststyle, sortkey1
FROM (SELECT c.oid as table_id,
                  BTRIM(s.nspname) AS “schema”,
                  BTRIM(c.relname) AS NAME,
                  BTRIM(((u.usename))) AS “owner”,
                  col.col_coding || ‘/’ || col.columns AS columns,
                  (COALESCE(c.reltuples, 0))::BIGINT AS visible_rows,
                  CASE WHEN (d.”diststyle” = ‘ALL’)
                            THEN (COALESCE(t.”rows”, 0) / (SELECT COUNT(DISTINCT stv_slices.node) AS COUNT FROM stv_slices))
                            ELSE COALESCE(t.”rows”, 0) END::BIGINT AS total_rows,
                  CASE WHEN (d.”diststyle” = ‘ALL’)
                            THEN (COALESCE(t.”rows”, 0) / (SELECT COUNT(DISTINCT stv_slices.node) AS COUNT FROM stv_slices))
                            ELSE COALESCE(t.”rows”, 0) END – (COALESCE(c.reltuples, 0))::BIGINT AS deleted_rows,
                  COALESCE(t.sorted_rows, 0)::BIGINT AS sorted_rows,
                  NVL(tbl_scans.num_scans,0) ||’:’|| NVL(rr_scans.rr_scans,0) ||’:’|| NVL(rr_scans.filtered_scans,0) ||’:’|| NVL(scan_alerts.selective_scans,0) ||’:’|| NVL(scan_alerts.delrows_scans,0) AS “scans:rr:fil:sel:del”,
                  tbl_scans.last_scan,
                  NVL(d.diststyle, ”) as diststyle,
                  NVL(d.sortkey1, ”) as sortkey1,
                  NVL(d.size, 0) as tbl_size,
                  NVL(d.encoded, ‘N’) as encoded,
                  NVL(d.max_varchar, 0) as max_varchar,
                  COALESCE(t.min_rows, 0) AS min_rows,
                  COALESCE(t.max_rows, 0) AS max_rows,
                  NVL(d.skew_rows, 0) as skew_rows,
                  NVL(d.stats_off, 0) as stats_off
     FROM PG_CLASS c JOIN
                  PG_NAMESPACE s ON s.oid = c.relnamespace AND s.nspname NOT IN (‘pg_catalog’, ‘information_schema’, ‘pg_toast’, ‘pg_bitmapindex’, ‘pg_internal’, ‘pg_aoseg’) JOIN
                  PG_USER u ON u.usesysid = c.relowner LEFT JOIN
                  svv_table_info d ON c.oid = d.table_id LEFT JOIN
                              (SELECT id, SUM(rows) as rows FROM stv_tbl_perm GROUP BY 1) perm ON c.oid = perm.id LEFT JOIN
                              (SELECT tbl, MAX(endtime) last_scan, NVL(COUNT(DISTINCT query || LPAD(segment,3,’0′)),0) num_scans FROM stl_scan s WHERE s.userid > 1 GROUP BY 1) tbl_scans ON tbl_scans.tbl = d.table_id LEFT JOIN

                  (SELECT tbl,
                            NVL(SUM(CASE WHEN is_rrscan=’t’ THEN 1 ELSE 0 END),0) rr_scans,
                            NVL(SUM(CASE WHEN p.info like ‘Filter:%’ and p.nodeid > 0 THEN 1 ELSE 0 END),0) filtered_scans,
                            NVL(COUNT(DISTINCT s.query || LPAD(s.segment,3,’0′)),0) num_scans
                  FROM stl_scan s JOIN
                            stl_plan_info i on (s.userid=i.userid and s.query=i.query and s.segment=i.segment and s.step=i.step) JOIN
                            stl_explain p on ( i.userid=p.userid and i.query=p.query and i.nodeid=p.nodeid)
                  WHERE s.userid > 1 AND s.type = 2 AND s.slice = 0
                  GROUP BY tbl) rr_scans ON rr_scans.tbl = d.table_id LEFT JOIN

                  (SELECT s.tbl AS TABLE,
                            NVL(SUM(CASE WHEN TRIM(SPLIT_PART(l.event,’:’,1)) = ‘Very selective query filter’ THEN 1 ELSE 0 END),0) AS selective_scans,
                            NVL(SUM(CASE WHEN TRIM(SPLIT_PART(l.event,’:’,1)) = ‘Scanned a large number of deleted rows’ THEN 1 ELSE 0 END),0) AS delrows_scans
                  FROM stl_alert_event_log AS l JOIN stl_scan AS s ON s.query = l.query AND s.slice = l.slice AND s.segment = l.segment AND s.step = l.step
                  WHERE l.userid > 1 AND s.slice = 0 AND l.event_time >= DATEADD(DAY,-7,CURRENT_DATE)
                  AND TRIM(SPLIT_PART(l.event,’:’,1)) IN (‘Very selective query filter’,’Scanned a large number of deleted rows’)
                  GROUP BY 1) scan_alerts ON scan_alerts.table = d.table_id LEFT JOIN

                  (SELECT attrelid, SUM(CASE WHEN attencodingtype = 0 THEN 0 ELSE 1 END) col_coding, COUNT(*) as columns FROM pg_attribute WHERE attnum > 0 GROUP BY 1) col on col.attrelid = c.oid LEFT JOIN
                  (SELECT p.id, p.db_id,
                            SUM(COALESCE(p.”rows”, 0)) AS “rows”,
                            SUM(COALESCE(p.sorted_rows, 0)) AS sorted_rows,
                            SUM(COALESCE(p.”temp”, 0)) AS “temp”,
                            MIN(COALESCE(p.”rows”, 0)) AS min_rows,
                            MAX(COALESCE(p.”rows”, 0)) AS max_rows,
                            SUM(b.blocknum) AS “blocks”
                  FROM (SELECT DISTINCT stv_blocklist.slice FROM stv_blocklist) bl LEFT JOIN
                            stv_tbl_perm p ON p.slice = bl.slice LEFT JOIN
                            pg_database d ON d.datname = current_database() LEFT JOIN
                            (SELECT bl.tbl, bl.slice, COUNT(bl.blocknum) AS blocknum FROM stv_blocklist bl GROUP BY bl.tbl, bl.slice) b ON b.tbl = p.id AND b.slice = p.slice
                  GROUP BY p.id, p.db_id) t ON t.id::oid = c.oid
WHERE c.relkind = ‘r’
–AND nvl(rr_scans.rr_scans,0) + nvl(rr_scans.filtered_scans,0) + nvl(scan_alerts.selective_scans,0) + nvl(scan_alerts.delrows_scans,0) = 0 AND tbl_size is not null
)