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'));
