TOP SQL reporting

TOP SQL reporting

TOP SQL reporting

---- mon_topsql.sql 


https://github.com/vishaldesai/Oracle_Scripts/blob/master/Exadata/mon_topsql.sql

-- Author:      Tanel Poder ( http://blog.tanelpoder.com | tanel@tanelpoder.com )





SET LINES 999 PAGES 5000 TRIMSPOOL ON TRIMOUT ON TAB OFF 

COL pct FOR A10 JUST RIGHT
COL cpu_pct FOR 999.9
COL io_pct FOR 999.9
col OWNER for a20
col OBJECT_NAME    for a20                                                                                                                   
col PROCEDURE_NAME for a20 

BREAK ON day SKIP 1

DEF days=1

PROMPT Displaying daily top SQL for last &days days...

WITH ash AS (
    SELECT 
        day
      , owner
      , object_name
      , procedure_name
      , sql_id
      , sql_plan_hash_value
      , total_seconds
      , io_seconds
      , cpu_seconds
      , LPAD(TRIM(TO_CHAR(RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100, '999.9'))||'%', 10) pct
      , RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100 pct_num
    FROM (
        SELECT
            TO_CHAR(sample_time, 'YYYY-MM-DD') day
          , sql_id
          , sql_plan_hash_value 
          , p.owner
          , p.object_name
          , p.procedure_name
          , SUM(10) total_seconds
          , SUM(CASE WHEN wait_class = 'User I/O' THEN 10 ELSE 0 END) io_seconds
          , SUM(CASE WHEN wait_class IS NULL THEN 10 ELSE 0 END) cpu_seconds
        FROM
            dba_hist_active_sess_history a
          , dba_procedures p
        WHERE
            a.plsql_entry_object_id = p.object_id (+)
        AND a.plsql_entry_subprogram_id = p.subprogram_id (+)
        AND sample_time > SYSDATE - &days
        AND session_type != 'BACKGROUND' -- ignore for now
        GROUP BY
            sql_id
          , sql_plan_hash_value 
          , p.owner
          , p.object_name
          , p.procedure_name
          , TO_CHAR(sample_time, 'YYYY-MM-DD')
    )
)
, sqlstat AS (
    SELECT /*+ MATERIALIZE */ 
        TO_CHAR(begin_interval_time, 'YYYY-MM-DD') day
      , sql_id
      , plan_hash_value
      , SUM(executions_delta) executions
      , SUM(rows_processed_delta) rows_processed
      , SUM(disk_reads_delta) blocks_read
      , SUM(disk_reads_delta)*8/1024 mb_read
      , SUM(buffer_gets_delta) buffer_gets
      , SUM(iowait_delta)/1000000 awr_iowait_seconds
      , SUM(cpu_time_delta)/1000000 awr_cpu_seconds 
      , SUM(elapsed_time_delta)/1000000 awr_elapsed_seconds
    FROM
        dba_hist_snapshot
      NATURAL JOIN
        dba_hist_sqlstat
    WHERE
        begin_interval_time > SYSDATE - &days
    GROUP BY
        TO_CHAR(begin_interval_time, 'YYYY-MM-DD') 
      , sql_id
      , plan_hash_value
)
SELECT /*+ MONITOR */
        day
      , pct
      , owner
      , object_name
      , procedure_name
      , sql_id
      , sql_plan_hash_value plan_hash
      , ROUND(total_seconds / 3600,1) total_hours
      , total_seconds
      , executions
      , ROUND(total_seconds / NULLIF(executions,0),2) seconds_per_exec
      , io_pct
      , cpu_pct
      , mb_read
      , ROUND(mb_read / NULLIF(executions,0),2) mb_per_exec
      , buffer_gets
      , ROUND(buffer_gets / NULLIF(executions,0),2) bufget_per_exec
      , CASE WHEN sql_id IS NOT NULL THEN 
            'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('''||sql_id||''','||CASE WHEN sql_plan_hash_value = 0 THEN 'NULL' ELSE TO_CHAR(sql_plan_hash_value) END||', format=>''ADVANCED''));'
        END extract_plan_from_awr
FROM (
    SELECT
        day
      , pct
      , owner
      , object_name
      , procedure_name
      , sql_id
      , sql_plan_hash_value
      , total_seconds
      , io_seconds/total_seconds*100 io_pct
      , cpu_seconds/total_seconds*100 cpu_pct
      , (SELECT executions FROM sqlstat s  WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) executions
      , (SELECT mb_read FROM sqlstat s     WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) mb_read
      , (SELECT buffer_gets FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) buffer_gets
    FROM 
        ash
    WHERE 
        ash.pct_num >= 1
)
ORDER BY
    day DESC
  , total_seconds DESC
/




DAY               PCT OWNER                OBJECT_NAME          PROCEDURE_NAME       SQL_ID         PLAN_HASH TOTAL_HOURS TOTAL_SECONDS EXECUTIONS SECONDS_PER_EXEC IO_PCT CPU_PCT    MB_READ MB_PER_EXEC BUFFER_GETS BUFGET_PER_EXEC EXTRACT_PLAN_FROM_AWR
---------- ---------- -------------------- -------------------- -------------------- ------------- ---------- ----------- ------------- ---------- ---------------- ------ ------- ---------- ----------- ----------- --------------- --------------------------------------------------------------------------------------------------------------------------
2023-08-12      78.6%                                                                ctyc1af1abg5u  200298931          91        327510                                 .0   100.0                                                    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
                 4.4%                                                                                       0         5.1         18340                               22.3    72.7
                 4.2%                                                                3xjw1ncw5vh27 2487977020         4.9         17500                               88.3    11.7                                                    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('3xjw1ncw5vh27',2487977020, format=>'ADVANCED'));
                 4.2%                                                                c7ngymcfjtb1c 1658157256         4.8         17450                               43.8    56.2                                                    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('c7ngymcfjtb1c',1658157256, format=>'ADVANCED'));

2023-08-11      55.1%                                                                ctyc1af1abg5u  200298931        55.9        201130                                 .0   100.0                                                    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));




                 8.7%                                                                1zuyhdtsmmx5s 3008400300         8.8         31680    





define sql_id='ctyc1af1abg5u'

SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON


COL exec_per_sec    FOR 99999990
COL ela_ms_per_sec  FOR 99999990
COL rows_per_sec    FOR 99999990
COL lios_per_sec    FOR 99999990
COL blkrd_per_sec   FOR 99999990
COL cpu_ms_per_sec  FOR 99999990
COL iow_ms_per_sec  FOR 99999990
COL clw_ms_per_sec  FOR 99999990
COL apw_ms_per_sec  FOR 99999990
COL ccw_ms_per_sec  FOR 99999990


SELECT
    CAST(begin_interval_time AS DATE) begin_interval_time
,st.con_id
  , sql_id
  , plan_hash_value
  , ROUND(SUM(executions_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec
  , ROUND(SUM(elapsed_time_delta  ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec
  , ROUND(SUM(rows_processed_delta)        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec
  , ROUND(SUM(buffer_gets_delta   )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec
  , ROUND(SUM(disk_reads_delta    )        / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec
  , ROUND(SUM(cpu_time_delta      ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec
  , ROUND(SUM(iowait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec
  , ROUND(SUM(clwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec
  , ROUND(SUM(apwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec
  , ROUND(SUM(ccwait_delta        ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec
FROM
    dba_hist_snapshot sn
  , dba_hist_sqlstat st
WHERE
    sn.snap_id = st.snap_id
AND sn.dbid    = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&sql_id'
--AND plan_hash_value LIKE '2'
--AND begin_interval_time >= 3
--AND end_interval_time   <= 4
-- AND begin_interval_time > sysdate -1
GROUP BY
    CAST(begin_interval_time AS DATE)
,st.con_id
  , CAST(end_interval_time AS DATE)
  , sql_id
  , plan_hash_value
ORDER BY
    begin_interval_time
  , sql_id
  , plan_hash_value
/           



connnect to pdbs

col PLAN_TABLE_OUTPUT for a150
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));