/* Program : top15_sql_summary.sql Purpose : Top 15 summary listing of the resource consumption statistics for sql statements in a given snapshot range. It is ordered by elapsed time, but lists at least the top 15 for each resource category Author : Daniel W. Fink OptimalDBA.com Created : June 8, 2007 Update : Parameters : 1 - Begin snapshot id (begin_snap) 2 - End snapshot id (end_snap) Exit Codes : Not Used Comments : Disclaimer : No warranty is provided for any use of the script, statements or logic included. This script, statements and logic are for personal use only and may not be included as part of a commercial product. Please address any comments to scripts@optimaldba.com This comment block and all lines above must be included. */ DEFINE begin_snap = &1 DEFINE end_snap = &2 -- The code below adapts the columns based on the 9i or 10g version of stats$sql_summary COLUMN sql_summ_col_name NEW_VALUE sql_summ_col_name NOPRINT COLUMN old_hv_print NEW_VALUE old_hv_print NOPRINT SET VERIFY OFF ECHO OFF PAGESIZE 0 TTITLE OFF BTITLE OFF SELECT DECODE((SELECT COUNT(1) FROM user_tab_columns WHERE table_name = 'STATS$SQL_SUMMARY' AND column_name = 'OLD_HASH_VALUE'), 1,'old_hash_value', 'hash_value') sql_summ_col_name, DECODE((SELECT COUNT(1) FROM user_tab_columns WHERE table_name = 'STATS$SQL_SUMMARY' AND column_name = 'OLD_HASH_VALUE'), 1,'PRINT', 'NOPRINT') old_hv_print FROM dual / SET LINESIZE 250 PAGESIZE 45 FEEDBACK OFF TRIMSPOOL ON VERIFY OFF ECHO OFF COLUMN sql_hash_value FORMAT 999999999999 HEADING 'SQL Hash Value' COLUMN old_hash_value FORMAT 999999999999 HEADING 'Old Hash Value' &old_hv_print COLUMN sql_exec_sum FORMAT 999,999,999,999 HEADING 'Executions' COLUMN sql_lio_sum FORMAT 999,999,999,999 HEADING 'Memory Reads' COLUMN sql_pio_sum FORMAT 999,999,999,999 HEADING 'Disk Reads' COLUMN sql_rows_sum FORMAT 999,999,999,999 HEADING 'Rows' COLUMN sql_cpu_sec_sum FORMAT 999,999,999 HEADING 'CPU|Seconds' COLUMN sql_ela_sec_sum FORMAT 999,999,999 HEADING 'Elapsed|Seconds' COLUMN dr_lio FORMAT 9999 HEADING 'LIO' COLUMN dr_pio FORMAT 9999 HEADING 'PIO' COLUMN dr_cpu FORMAT 9999 HEADING 'CPU' COLUMN dr_ela FORMAT 9999 HEADING 'ELA' WITH sql_deltas AS ( SELECT ss.hash_value, ss.old_hash_value, st.snap_id, CASE WHEN NVL(ss.executions,0) < NVL(LAG(ss.executions,1,ss.executions) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) THEN NVL(ss.executions,0) WHEN NVL(ss.executions,0) != 0 THEN NVL(ss.executions,0) - NVL(LAG(ss.executions,1,ss.executions) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) ELSE 0 END AS delta_execs, CASE WHEN NVL(ss.buffer_gets,0) < NVL(LAG(ss.buffer_gets,1,ss.buffer_gets) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) THEN NVL(ss.buffer_gets,0) WHEN NVL(ss.buffer_gets,0) != 0 THEN NVL(ss.buffer_gets,0) - NVL(LAG(ss.buffer_gets,1,ss.buffer_gets) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) ELSE 0 END AS delta_lio, CASE WHEN NVL(ss.disk_reads,0) < NVL(LAG(ss.disk_reads,1,ss.disk_reads) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) THEN NVL(ss.disk_reads,0) WHEN NVL(ss.disk_reads,0) != 0 THEN NVL(ss.disk_reads,0) - NVL(LAG(ss.disk_reads,1,ss.disk_reads) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) ELSE 0 END AS delta_pio, CASE WHEN NVL(ss.rows_processed,0) < NVL(LAG(ss.rows_processed,1,ss.rows_processed) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) THEN NVL(ss.rows_processed,0) WHEN NVL(ss.rows_processed,0) != 0 THEN NVL(ss.rows_processed,0) - NVL(LAG(ss.rows_processed,1,ss.rows_processed) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) ELSE 0 END AS delta_rows, CASE WHEN NVL(ss.cpu_time,0) < NVL(LAG(ss.cpu_time,1,ss.cpu_time) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) THEN NVL(ss.cpu_time,0) WHEN NVL(ss.cpu_time,0) != 0 THEN NVL(ss.cpu_time,0) - NVL(LAG(ss.cpu_time,1,ss.cpu_time) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) ELSE 0 END AS delta_cpu_time, CASE WHEN NVL(ss.elapsed_time,0) < NVL(LAG(ss.elapsed_time,1,ss.elapsed_time) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) THEN NVL(ss.elapsed_time,0) WHEN NVL(ss.elapsed_time,0) != 0 THEN NVL(ss.elapsed_time,0) - NVL(LAG(ss.elapsed_time,1,ss.elapsed_time) OVER (PARTITION BY ss.hash_value ORDER BY st.snap_id),0) ELSE 0 END AS delta_ela_time FROM (SELECT s1.hash_value, s1.&sql_summ_col_name old_hash_value, s1.snap_id snap_id, s1.executions executions, s1.buffer_gets, s1.disk_reads, s1.rows_processed, s1.cpu_time, s1.elapsed_time FROM stats$sql_summary s1 WHERE s1.snap_id BETWEEN &begin_snap AND &end_snap ) ss PARTITION BY (ss.hash_value) RIGHT OUTER JOIN ( SELECT st.snap_id FROM stats$snapshot st WHERE st.snap_id BETWEEN &begin_snap AND &end_snap ORDER BY st.snap_id ) st ON (st.snap_id = ss.snap_id) ), sql_resource_sum AS ( SELECT so.hash_value sql_hash_value, so.old_hash_value old_hash_value, SUM(so.delta_execs) sql_exec_sum, SUM(so.delta_rows) sql_rows_sum, ROUND(SUM(so.delta_ela_time)/1000000) sql_ela_sec_sum, dense_rank() OVER (ORDER BY SUM(so.delta_ela_time) DESC) dr_ela, ROUND(SUM(so.delta_cpu_time)/1000000) sql_cpu_sec_sum, dense_rank() OVER (ORDER BY SUM(so.delta_cpu_time) DESC) dr_cpu, SUM(so.delta_lio) sql_lio_sum, dense_rank() OVER (ORDER BY SUM(so.delta_lio) DESC) dr_lio, SUM(so.delta_pio) sql_pio_sum, dense_rank() OVER (ORDER BY SUM(so.delta_pio) DESC) dr_pio FROM sql_deltas so GROUP BY so.hash_value, so.old_hash_value ) SELECT srs.sql_hash_value, srs.old_hash_value, srs.sql_exec_sum, srs.sql_rows_sum, srs.sql_ela_sec_sum, srs.dr_ela, srs.sql_cpu_sec_sum, srs.dr_cpu, srs.sql_lio_sum, srs.dr_lio, srs.sql_pio_sum, srs.dr_pio FROM sql_resource_sum srs WHERE srs.dr_lio <= 15 OR srs.dr_pio <= 15 OR srs.dr_cpu <= 15 OR srs.dr_ela <= 15 ORDER BY srs.dr_ela SPOOL top15_sql.lst / SPOOL OFF