/* Program : cust_awr_10g.sql Purpose : Customized AWR report for 10g Author : Daniel W. Fink OptimalDBA.com Created : March 17, 2011 Update : Parameters : None Exit Code : 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 script_feedback@optimaldba.com This comment block and all lines above must be included. */ SET LINESIZE 152 PAGESIZE 45 FEEDBACK off VERIFY off TTITLE off CLEAR SCREEN -- Generic Columns COLUMN rpt_db_time_pct FORMAT A7 HEADING 'DB Time' JUSTIFY RIGHT COLUMN rpt_event_name FORMAT A50 HEADING 'Event' COLUMN rpt_spacer FORMAT A32 HEADING "" COLUMN rpt_sql_id FORMAT A14 HEADING 'SQL ID' COLUMN rpt_sql_elapsed FORMAT A26 HEADING 'Elapsed Time (us)' COLUMN rpt_sql_cpu FORMAT A26 HEADING 'CPU Time (us)' COLUMN rpt_sql_lios FORMAT A26 HEADING 'Logical I/O' COLUMN rpt_sql_pios FORMAT A26 HEADING 'Physical I/O' COLUMN rpt_sql_execs_num FORMAT 999,999,999 HEADING 'Executions' COLUMN rpt_sql_rows_num FORMAT 999,999,999,999 HEADING 'Rows' COLUMN rpt_stat_name FORMAT A30 HEADING 'Statistic' COLUMN rpt_stat_value FORMAT 999,999,999,999 HEADING 'Value' COLUMN rpt_val_minutes FORMAT 999,999,999.99 HEADING 'Minutes' COLUMN rpt_val_seconds FORMAT 999,999,999.99 HEADING 'Seconds' COLUMN rpt_val_centi FORMAT 999,999,999 HEADING 'Centiseconds' COLUMN rpt_val_milli FORMAT 999,999,999,999 HEADING 'Milliseconds' COLUMN rpt_val_micro FORMAT 999,999,999,999,999 HEADING 'Microseconds' COLUMN rpt_val_count FORMAT 999,999,999 HEADING 'Count' COLUMN rpt_val_name FORMAT A40 COLUMN rpt_val_pct FORMAT 999,990.99 HEADING 'Pct' COLUMN rpt_val_per_second FORMAT 999,999,999.99 HEADING 'Per Second' COLUMN rpt_val_per_tx FORMAT 999,999,999.99 HEADING 'Per Trans' COLUMN sort_order FORMAT 99999 NOPRINT SET PAGESIZE 0 /* Get Current database and instance to use as default values */ COLUMN curr_db_id FORMAT 999999999999 HEADING 'DBID' NEW_VALUE curr_db_id NOPRINT COLUMN curr_db_name FORMAT A10 HEADING 'DB Name' NEW_VALUE curr_db_name NOPRINT COLUMN curr_inst_id FORMAT 999 HEADING 'InstID' NEW_VALUE curr_inst_id NOPRINT COLUMN curr_inst_name FORMAT A10 HEADING 'Instance Name' NEW_VALUE curr_inst_name NOPRINT SELECT d.dbid curr_db_id , d.name curr_db_name FROM sys.v$database d / SELECT i.instance_number curr_inst_id , i.instance_name curr_inst_name FROM v$instance i / SET VERIFY OFF SET PAGESIZE 45 /* Database Selection */ COLUMN awr_db_id FORMAT A12 HEADING 'DBID' NEW_VALUE awr_db_id COLUMN awr_db_name FORMAT A10 HEADING 'DB Name' NEW_VALUE awr_db_name COLUMN awr_db_version FORMAT A10 HEADING 'Version' COLUMN awr_db_rac FORMAT A03 HEADING 'RAC' PROMPT PROMPT *** Databases in AWR Repository *** PROMPT SELECT wdi.db_name||CASE WHEN wdi.db_name = '&curr_db_name' THEN '*' END awr_db_name , RPAD(wdi.dbid,12) awr_db_id , wdi.version awr_db_version , REPLACE(wdi.parallel, 'YES', 'RAC') awr_db_rac FROM sys.wrm$_database_instance wdi WHERE wdi.instance_number = 1 AND wdi.startup_time = ( SELECT MAX(wdi2.startup_time) FROM sys.wrm$_database_instance wdi2 WHERE wdi.dbid = wdi2.dbid AND wdi.instance_number = wdi2.instance_number ) ORDER BY wdi.db_name / PROMPT ACCEPT usr_db_name PROMPT 'Database Name <&curr_db_name> : ' DEFAULT &curr_db_name -- Instance Selection COLUMN awr_inst_host_name FORMAT A30 HEADING 'Host Name' COLUMN awr_inst_id FORMAT A4 HEADING 'Inst|ID' NEW_VALUE awr_inst_id COLUMN awr_inst_name FORMAT A10 HEADING 'Instance|Name' NEW_VALUE awr_inst_name COLUMN awr_inst_start_time FORMAT A30 HEADING 'Start Time' PROMPT PROMPT *** Instances for &usr_db_name in AWR Repository *** PROMPT SELECT wdi.instance_number||CASE WHEN wdi.instance_name = '&curr_inst_name' THEN '*' END awr_inst_id , wdi.instance_name awr_inst_name , wdi.startup_time awr_inst_start_time , wdi.host_name awr_inst_host_name FROM sys.wrm$_database_instance wdi WHERE UPPER(wdi.db_name) = UPPER('&usr_db_name') AND wdi.startup_time = ( SELECT MAX(wdi2.startup_time) FROM sys.wrm$_database_instance wdi2 WHERE wdi.dbid = wdi2.dbid AND wdi.instance_number = wdi2.instance_number ) ORDER BY wdi.instance_number / PROMPT ACCEPT usr_inst_name PROMPT 'Instance ID <&curr_inst_name> : ' DEFAULT &curr_inst_name SET HEADING OFF COLUMN rpt_db_id FORMAT 999999999999 HEADING 'DBID' NEW_VALUE rpt_db_id NOPRINT COLUMN rpt_inst_id FORMAT A4 HEADING 'Inst|ID' NEW_VALUE rpt_inst_id NOPRINT SELECT DISTINCT wdi.dbid rpt_db_id , wdi.instance_number rpt_inst_id FROM sys.wrm$_database_instance wdi WHERE UPPER(wdi.db_name) = UPPER('&usr_db_name') AND UPPER(wdi.instance_name) = UPPER('&usr_inst_name') / SET HEADING ON SET HEADING OFF COLUMN snap_int_min FORMAT A100 COLUMN snap_retention FORMAT A100 FOLD_BEFORE SELECT 'Current Snapshot Interval for &usr_db_name ==> '||EXTRACT(minute FROM snap_interval)||' minutes' snap_int_min , 'Current Snapshot Retention for &usr_db_name ==> '||EXTRACT(day FROM retention)||' days' snap_retention FROM sys.wrm$_wr_control WHERE dbid = &rpt_db_id / SET HEADING ON @awr_snap_wkly_matrix &rpt_db_id &rpt_inst_id SET PAGESIZE 45 ACCEPT usr_begin_snap PROMPT 'Enter Beginning Snapshot ID : ' ACCEPT usr_end_snap PROMPT 'Enter Ending Snapshot ID : ' /* Set spool name as "cust_awr_dbid_instid_begintime_endtime.lst */ /* Get report times Elapsed DB Time */ COLUMN elapsed_time_minutes FORMAT 9999999999999999 HEADING 'Elapsed Time|Minutes' NEW_VALUE elapsed_time_minutes NOPRINT COLUMN elapsed_time_seconds FORMAT 9999999999999999 HEADING 'Elapsed Time|Seconds' NEW_VALUE elapsed_time_seconds NOPRINT SELECT ROUND(SUM(EXTRACT (DAY FROM (sn.end_interval_time - sn.begin_interval_time)) * 86400 + EXTRACT (HOUR FROM (sn.end_interval_time - sn.begin_interval_time)) * 3600 + EXTRACT (MINUTE FROM (sn.end_interval_time - sn.begin_interval_time)) * 60 + ROUND(EXTRACT (SECOND FROM (sn.end_interval_time - sn.begin_interval_time))) )/60) elapsed_time_minutes , SUM(EXTRACT (DAY FROM (sn.end_interval_time - sn.begin_interval_time)) * 86400 + EXTRACT (HOUR FROM (sn.end_interval_time - sn.begin_interval_time)) * 3600 + EXTRACT (MINUTE FROM (sn.end_interval_time - sn.begin_interval_time)) * 60 + ROUND(EXTRACT (SECOND FROM (sn.end_interval_time - sn.begin_interval_time))) ) elapsed_time_seconds FROM sys.wrm$_snapshot sn WHERE sn.snap_id between (&usr_begin_snap + 1) and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id / SET HEADING off COLUMN db_time_stat_id FORMAT 9999999999999999 NEW_VALUE db_time_stat_id NOPRINT SELECT sname.stat_id db_time_stat_id FROM wrh$_stat_name sname WHERE sname.stat_name = 'DB time' AND sname.dbid = &rpt_db_id / COLUMN total_db_time_micro FORMAT 9999999999999999999 NEW_VALUE total_db_time_micro NOPRINT COLUMN total_db_time_minutes FORMAT 9999999999999999 HEADING 'DB Time|Minutes' NEW_VALUE total_db_time_minutes NOPRINT COLUMN total_db_time_seconds FORMAT 9999999999999999 HEADING 'DB Time|Seconds' NEW_VALUE total_db_time_seconds NOPRINT WITH db_time_q AS ( SELECT wtm.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0 ELSE LAG(wtm.value) OVER (PARTITION BY sn.startup_time ORDER BY sn.snap_id) END),wtm.value) snap_db_time FROM sys.wrh$_sys_time_model wtm , sys.wrm$_snapshot sn WHERE wtm.stat_id = &db_time_stat_id AND wtm.snap_id between &usr_begin_snap and &usr_end_snap AND wtm.dbid = &rpt_db_id AND wtm.instance_number = &rpt_inst_id AND sn.snap_id = wtm.snap_id AND sn.dbid = wtm.dbid AND sn.instance_number = wtm.instance_number ) SELECT SUM(snap_db_time) total_db_time_micro , ROUND(SUM(snap_db_time)/60000000) total_db_time_minutes , ROUND(SUM(snap_db_time)/1000000) total_db_time_seconds FROM db_time_q / SET HEADING ON PAGESIZE 45 COLUMN time_desc FORMAT A20 HEADING "" PROMPT PROMPT *** Report Times *** PROMPT SELECT 'Elapsed Time' time_desc , &elapsed_time_minutes rpt_val_minutes , &elapsed_time_seconds rpt_val_seconds FROM dual UNION ALL SELECT 'Database Time' time_desc , &total_db_time_minutes rpt_val_minutes , &total_db_time_seconds rpt_val_seconds FROM dual ORDER BY time_desc DESC / SET HEADING off COLUMN rpt_transactions FORMAT 9999999999999999 HEADING 'Transactions' NEW_VALUE rpt_transactions NOPRINT WITH sysstat_value_q AS ( SELECT wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0 ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name ORDER BY sn.snap_id) END),wsv.value) snap_metric_value FROM dba_hist_sysstat wsv , sys.wrm$_snapshot sn WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND wsv.snap_id = sn.snap_id AND wsv.dbid = sn.dbid AND wsv.instance_number = sn.instance_number AND wsv.stat_name IN ('user commits', 'user rollbacks') ) SELECT SUM(svq.snap_metric_value) rpt_transactions FROM sysstat_value_q svq / SET HEADING on PROMPT PROMPT PROMPT PROMPT *** Load Profile *** PROMPT WITH sysstat_value_q AS ( SELECT wsv.stat_name , wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0 ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name ORDER BY sn.snap_id) END),wsv.value) snap_metric_value FROM dba_hist_sysstat wsv , sys.wrm$_snapshot sn WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND wsv.snap_id = sn.snap_id AND wsv.dbid = sn.dbid AND wsv.instance_number = sn.instance_number AND wsv.stat_name IN ('user calls', 'execute count', 'parse count (total)', 'parse count (hard)', 'session logical reads', 'db block changes', 'redo size', 'physical reads', 'physical writes', 'recursive calls', 'sorts (memory)', 'sorts (disk)') ), sysstat_value_summary AS ( SELECT CASE WHEN svq.stat_name LIKE 'sorts%' THEN 'sorts' ELSE svq.stat_name END stat_name , SUM(svq.snap_metric_value) rpt_value , ROUND((SUM(svq.snap_metric_value) / &elapsed_time_seconds),2) rpt_val_per_second , ROUND((SUM(svq.snap_metric_value) / &rpt_transactions),2) rpt_val_per_tx FROM sysstat_value_q svq GROUP BY CASE WHEN svq.stat_name LIKE 'sorts%' THEN 'sorts' ELSE svq.stat_name END UNION ALL SELECT 'transactions' stat_name , &rpt_transactions rpt_value , ROUND((&rpt_transactions / &elapsed_time_seconds),2) rpt_val_per_second , NULL FROM dual ) SELECT DECODE(svs.stat_name, 'transactions', 001, 'user calls', 002, 'recursive calls', 003, 'execute count', 004, 'parse count (total)', 005, 'parse count (hard)', 006, 'session logical reads', 007, 'physical reads', 008, 'physical writes',009, 'db block changes',010, 'redo size',011, 'sorts',012, 9999) sort_order , svs.stat_name rpt_stat_name , svs.rpt_value rpt_stat_value , svs.rpt_val_per_second rpt_val_per_second , svs.rpt_val_per_tx rpt_val_per_tx FROM sysstat_value_summary svs ORDER BY sort_order / PROMPT PROMPT PROMPT PROMPT *** Operating System Statistics *** PROMPT PROMPT *** O/S Configuration (end) *** PROMPT SELECT wsv.stat_name rpt_stat_name , wsv.value rpt_stat_value FROM dba_hist_osstat wsv , sys.wrm$_snapshot sn WHERE sn.snap_id = &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND wsv.snap_id = sn.snap_id AND wsv.dbid = sn.dbid AND wsv.instance_number = sn.instance_number AND wsv.stat_name IN ('NUM_CPUS', 'PHYSICAL_MEMORY_BYTES') / PROMPT PROMPT PROMPT PROMPT *** O/S Time *** PROMPT COLUMN qry_val_per_second FORMAT 999,999,999.99 HEADING 'Seconds|Per Second' WITH sysstat_value_q AS ( SELECT wsv.stat_name , wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0 ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name ORDER BY sn.snap_id) END),wsv.value) snap_metric_value FROM dba_hist_osstat wsv , sys.wrm$_snapshot sn WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND wsv.snap_id = sn.snap_id AND wsv.dbid = sn.dbid AND wsv.instance_number = sn.instance_number AND wsv.stat_name IN ('BUSY_TIME', 'IDLE_TIME', 'SYS_TIME', 'USER_TIME') ) SELECT DECODE(svq.stat_name, 'BUSY_TIME', 001, 'SYS_TIME', 002, 'USER_TIME', 003, 'IDLE_TIME', 004, 9999) sort_order , svq.stat_name rpt_stat_name , SUM(svq.snap_metric_value) rpt_val_centi , CASE WHEN svq.stat_name LIKE '%TIME' THEN SUM(svq.snap_metric_value)/100 END rpt_val_seconds , ROUND(((CASE WHEN svq.stat_name LIKE '%TIME' THEN SUM(svq.snap_metric_value)/100 END) / &elapsed_time_seconds),4) qry_val_per_second FROM sysstat_value_q svq GROUP BY svq.stat_name ORDER BY sort_order / PROMPT PROMPT PROMPT PROMPT *** Time Model Statistics *** PROMPT WITH sysstat_value_q AS ( SELECT wsv.stat_name , wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0 ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name ORDER BY sn.snap_id) END),wsv.value) snap_metric_value FROM dba_hist_sys_time_model wsv , sys.wrm$_snapshot sn WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND wsv.snap_id = sn.snap_id AND wsv.dbid = sn.dbid AND wsv.instance_number = sn.instance_number AND wsv.stat_name IN ('DB CPU', 'DB time', 'sql execute elapsed time', 'parse time elapsed', 'hard parse elapsed time', 'PL/SQL execution elapsed time', 'background elapsed time', 'background cpu time') ) SELECT DECODE(svq.stat_name, 'DB time', 001, 'DB CPU', 002, 'sql execute elapsed time', 003, 'parse time elapsed', 004, 'hard parse elapsed time', 005, 'PL/SQL execution elapsed time', 006, 'background elapsed time', 007, 'background cpu time', 008, 9999) sort_order , svq.stat_name rpt_stat_name , SUM(svq.snap_metric_value) rpt_val_micro , SUM(svq.snap_metric_value)/1000000 rpt_val_seconds , CASE WHEN svq.stat_name IN ('DB CPU','sql execute elapsed time','parse time elapsed', 'hard parse elapsed time', 'PL/SQL execution elapsed time') THEN LPAD(TO_CHAR(ROUND((SUM(svq.snap_metric_value)/&total_db_time_micro),4)*100)||'%',7) END rpt_db_time_pct FROM sysstat_value_q svq GROUP BY svq.stat_name ORDER BY sort_order / PROMPT PROMPT PROMPT PROMPT *** Top 10 Timed Events *** PROMPT COLUMN rpt_avg_micro FORMAT 999,999.99 HEADING 'Avg Time|(micro)' COLUMN rpt_event_count FORMAT 999,999,999,999 HEADING 'Event Count' WITH sysstat_value_q AS ( SELECT wsv.event_name , wsv.time_waited_micro - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0 ELSE LAG(wsv.time_waited_micro) OVER (PARTITION BY sn.startup_time, wsv.event_name ORDER BY sn.snap_id) END),wsv.time_waited_micro) snap_wait_time , wsv.total_waits - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0 ELSE LAG(wsv.total_waits) OVER (PARTITION BY sn.startup_time, wsv.event_name ORDER BY sn.snap_id) END),wsv.total_waits) snap_wait_count FROM dba_hist_system_event wsv , sys.wrm$_snapshot sn WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND wsv.snap_id = sn.snap_id AND wsv.dbid = sn.dbid AND wsv.instance_number = sn.instance_number AND wsv.wait_class != 'Idle' UNION ALL SELECT 'CPU time' stat_name , wsv.value - NVL((CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id != &usr_begin_snap THEN 0 ELSE LAG(wsv.value) OVER (PARTITION BY sn.startup_time, wsv.stat_name ORDER BY sn.snap_id) END),wsv.value) snap_wait_time , NULL snap_wait_count FROM dba_hist_sys_time_model wsv , sys.wrm$_snapshot sn WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND wsv.snap_id = sn.snap_id AND wsv.dbid = sn.dbid AND wsv.instance_number = sn.instance_number AND wsv.stat_name = 'DB CPU' ), sysstat_wait_summary AS ( SELECT svq.event_name , SUM(svq.snap_wait_time) event_value , DENSE_RANK() OVER (ORDER BY SUM(svq.snap_wait_time) DESC) event_rank , SUM(svq.snap_wait_count) event_count FROM sysstat_value_q svq GROUP BY svq.event_name ), sysstat_wait_pcts AS ( SELECT sws.event_name , RATIO_TO_REPORT(sws.event_value) OVER () event_pct FROM sysstat_wait_summary sws ) SELECT sws.event_rank sort_order , sws.event_name rpt_event_name , ROUND((sws.event_value/1000000),2) rpt_val_seconds , ROUND((swp.event_pct*100),2) rpt_val_pct , ROUND(sws.event_value/sws.event_count)/1000 rpt_avg_micro , sws.event_count rpt_event_count FROM sysstat_wait_summary sws , sysstat_wait_pcts swp WHERE sws.event_rank <= 10 AND sws.event_name = swp.event_name ORDER BY sws.event_rank ASC / PROMPT PROMPT PROMPT PROMPT *** SQL Ranking Overview *** PROMPT WITH sql_stats_summary AS ( SELECT sqs.sql_id , CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.buffer_gets_delta END lios , CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.disk_reads_delta END pios , CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.elapsed_time_delta END ela_time , CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.cpu_time_delta END cpu_time , CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.executions_delta END execs , CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.rows_processed_delta END q_rows FROM dba_hist_sqlstat sqs , sys.wrm$_snapshot sn WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND sqs.snap_id = sn.snap_id AND sqs.dbid = sn.dbid AND sqs.instance_number = sn.instance_number ), sql_ranking AS ( SELECT sqs.sql_id , SUM(lios) lios , DENSE_RANK() OVER (ORDER BY SUM(lios) DESC) lios_rank , SUM(pios) pios , DENSE_RANK() OVER (ORDER BY SUM(pios) DESC) pios_rank , SUM(ela_time) ela_time , DENSE_RANK() OVER (ORDER BY SUM(ela_time) DESC) ela_rank , SUM(cpu_time) cpu_time , DENSE_RANK() OVER (ORDER BY SUM(cpu_time) DESC) cpu_rank , SUM(execs) execs , SUM(q_rows) q_rows FROM sql_stats_summary sqs GROUP BY sqs.sql_id ), sql_top_5 AS ( SELECT sqlr.sql_id , sqlr.lios , sqlr.lios_rank , sqlr.pios , sqlr.pios_rank , sqlr.ela_time , sqlr.ela_rank , sqlr.cpu_time , sqlr.cpu_rank , sqlr.execs , sqlr.q_rows FROM sql_ranking sqlr WHERE ( sqlr.lios_rank <= 5 OR sqlr.pios_rank <= 5 OR sqlr.ela_rank <= 5 OR sqlr.cpu_rank <= 5 ) ), sql_plan_count AS ( SELECT sqs.sql_id , COUNT(DISTINCT sqs.plan_hash_value) plan_count FROM dba_hist_sqlstat sqs , sys.wrm$_snapshot sn , sql_top_5 st5 WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND sqs.snap_id = sn.snap_id AND sqs.dbid = sn.dbid AND sqs.instance_number = sn.instance_number AND sqs.sql_id = st5.sql_id AND sqs.executions_delta > 0 GROUP BY sqs.sql_id ) SELECT sql5.sql_id||(CASE WHEN spc.plan_count > 1 THEN '*' ELSE NULL END) rpt_sql_id , TO_CHAR(sql5.ela_time, '99,999,999,999,999')||LPAD('('||TO_CHAR(ela_rank)||')', 6) rpt_sql_elapsed , TO_CHAR(sql5.cpu_time, '99,999,999,999,999')||LPAD('('||TO_CHAR(cpu_rank)||')', 6) rpt_sql_cpu , TO_CHAR(sql5.lios, '99,999,999,999,999')||LPAD('('||TO_CHAR(lios_rank)||')', 6) rpt_sql_lios , TO_CHAR(sql5.pios, '99,999,999,999,999')||LPAD('('||TO_CHAR(pios_rank)||')', 6) rpt_sql_pios , sql5.execs rpt_sql_execs_num , sql5.q_rows rpt_sql_rows_num FROM sql_top_5 sql5 , sql_plan_count spc WHERE sql5.sql_id = spc.sql_id (+) ORDER BY (sql5.ela_rank + sql5.lios_rank + sql5.pios_rank + sql5.cpu_rank + sql5.wait_rank) ASC / SET PAGESIZE 0 BREAK ON qry_sql_id NODUP SKIP 2 ON qry_sql_text NODUP SKIP 2 COLUMN qry_sql_id FORMAT A20 COLUMN qry_sql_text FORMAT A100 WORD_WRAP FOLD_AFTER COLUMN qry_exec_plan_hash FORMAT A36 FOLD_BEFORE COLUMN qry_execs FORMAT A36 COLUMN qry_rows FORMAT A36 COLUMN qry_rows_per_exec FORMAT A36 COLUMN qry_spacer FORMAT A36 FOLD_BEFORE COLUMN qry_ela_time FORMAT A36 COLUMN qry_cpu_time FORMAT A36 COLUMN qry_wait_time FORMAT A36 COLUMN qry_ela_per_exec FORMAT A36 COLUMN qry_cpu_per_exec FORMAT A36 COLUMN qry_wait_per_exec FORMAT A36 COLUMN qry_lios FORMAT A36 COLUMN qry_lios_per_exec FORMAT A36 COLUMN qry_lios_per_row FORMAT A36 COLUMN qry_pios FORMAT A36 COLUMN qry_pios_per_exec FORMAT A36 COLUMN qry_pios_per_row FORMAT A36 PROMPT PROMPT PROMPT PROMPT *** SQL Executions *** PROMPT WITH sql_stats_summary AS ( SELECT sqs.sql_id , DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.buffer_gets_delta END) DESC) lios_rank , DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.disk_reads_delta END) DESC) pios_rank , DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.elapsed_time_delta END) DESC) ela_rank , DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.cpu_time_delta END) DESC) cpu_rank , DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE (sqs.elapsed_time_delta - sqs.cpu_time_delta) END) DESC) wait_rank FROM dba_hist_sqlstat sqs , sys.wrm$_snapshot sn WHERE sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND sqs.snap_id = sn.snap_id AND sqs.dbid = sn.dbid AND sqs.instance_number = sn.instance_number GROUP BY sqs.sql_id ), sql_top_5 AS ( SELECT sqlr.sql_id FROM sql_stats_summary sqlr WHERE ( sqlr.lios_rank <= 5 OR sqlr.pios_rank <= 5 OR sqlr.ela_rank <= 5 OR sqlr.cpu_rank <= 5 OR sqlr.wait_rank <= 5 ) ), sql_plan_stats AS ( SELECT sqs.sql_id , sqs.plan_hash_value , SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.buffer_gets_delta END) lios , SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.disk_reads_delta END) pios , SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.elapsed_time_delta END) ela_time , SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.cpu_time_delta END) cpu_time , SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE (sqs.elapsed_time_delta - sqs.cpu_time_delta) END) wait_time , SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.executions_delta END) execs , SUM(CASE WHEN sn.startup_time = sn.begin_interval_time AND sn.snap_id = &usr_begin_snap THEN 0 ELSE sqs.rows_processed_delta END) q_rows FROM dba_hist_sqlstat sqs , sys.wrm$_snapshot sn , sql_top_5 sql5 WHERE sqs.sql_id = sql5.sql_id AND sn.snap_id between &usr_begin_snap and &usr_end_snap AND sn.dbid = &rpt_db_id AND sn.instance_number = &rpt_inst_id AND sqs.snap_id = sn.snap_id AND sqs.dbid = sn.dbid AND sqs.instance_number = sn.instance_number GROUP BY sqs.sql_id, sqs.plan_hash_value ) SELECT sps.sql_id qry_sql_id , SUBSTR(sqt.sql_text, 1, 500) qry_sql_text , 'Plan Hash ==> '||sps.plan_hash_value qry_exec_plan_hash , 'Executions ==> '||TO_CHAR(execs,'999,999,999,999') qry_execs , 'Rows ==> '||TO_CHAR(q_rows,'999,999,999,999') qry_rows , 'Rows per Exec ==> '||TO_CHAR(ROUND(q_rows/DECODE(execs,0,1,execs)),'999,999,999,999') qry_rows_per_exec , NULL qry_spacer , 'Ela Time(s) ==> '||LPAD(TO_CHAR(ela_time/1000000,'999,990.900000'),16) qry_ela_time , 'CPU Time(us) ==> '||LPAD(TO_CHAR(cpu_time/1000000,'999,990.900000'),16) qry_cpu_time , 'Wait Time(us) ==> '||LPAD(TO_CHAR(wait_time/1000000,'999,990.900000'),16) qry_wait_time , NULL qry_spacer , 'Ela per Exec ==> '||LPAD(TO_CHAR(ROUND((ela_time/1000000)/DECODE(execs,0,1,execs),6),'999,990.900000'),16) qry_ela_per_exec , 'CPU per Exec ==> '||LPAD(TO_CHAR(ROUND((cpu_time/1000000)/DECODE(execs,0,1,execs),6),'999,990.900000'),16) qry_cpu_per_exec , 'Wait per Exec ==> '||LPAD(TO_CHAR(ROUND((wait_time/1000000)/DECODE(execs,0,1,execs),6),'999,990.900000'),16) qry_wait_per_exec , NULL qry_spacer , 'Logical I/O ==> '||TO_CHAR(lios,'999,999,999,999') qry_lios , 'LIO per Exec ==> '||TO_CHAR(ROUND(lios/DECODE(execs,0,1,execs)),'999,999,999,999') qry_lios_per_exec , 'LIO per Row ==> '||TO_CHAR(ROUND(lios/DECODE(q_rows,0,1,q_rows)),'999,999,999,999') qry_lios_per_row , NULL qry_spacer , 'Physical I/O ==> '||TO_CHAR(pios,'999,999,999,999') qry_pios , 'PIO per Exec ==> '||TO_CHAR(ROUND(pios/DECODE(execs,0,1,execs)),'999,999,999,999') qry_pios_per_exec , 'PIO per Row ==> '||TO_CHAR(ROUND(pios/DECODE(q_rows,0,1,q_rows)),'999,999,999,999') qry_pios_per_row FROM sql_plan_stats sps , dba_hist_sqltext sqt WHERE sps.execs > 0 AND sps.sql_id = sqt.sql_id ORDER BY sps.sql_id , sps.ela_time DESC /