--@@header /* * * Author : Vishal Gupta * Purpose : Display top SQLs from AWR * Version : 10.1 and above * Parameters : 1 - From Timestamp (YYYY-MM-DD HH24:MI:SS) * 2 - To Timestamp (YYYY-MM-DD HH24:MI:SS) * 3 - Top record Count * 4 - ORDER BY column number * * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 15-May-12 Vishal Gupta Intial version * */ DEFINE FROM_TIMESTAMP="&&1" DEFINE TO_TIMESTAMP="&2" DEFINE TOP_RECORDCOUNT="&3" DEFINE ORDER_COLUMN_NUMBER="&4" DEFINE COUNT_SMALL_FORMAT=99,999 --DEFINE COUNT_SMALL_DIVIDER="1" --DEFINE COUNT_SMALL_HEADING="#" DEFINE COUNT_SMALL_DIVIDER="1000" DEFINE COUNT_SMALL_HEADING="#1000" DEFINE COUNT_FORMAT=999,999,999 --DEFINE COUNT_DIVIDER="1" --DEFINE COUNT_HEADING="#" DEFINE COUNT_DIVIDER="1000" DEFINE COUNT_HEADING="#1000" DEFINE BYTES_FORMAT="999,999" --DEFINE BYTES_DIVIDER="1024" --DEFINE BYTES_HEADING="KB" --DEFINE BYTES_DIVIDER="1024/1024" --DEFINE BYTES_HEADING="MB" DEFINE BYTES_DIVIDER="1024/1024/1024" DEFINE BYTES_HEADING="GB" DEFINE TIME_FORMAT=99,999 DEFINE TIME_DIVIDER="60" DEFINE TIME_HEADING="min" COLUMN min_end_interval_time HEADING "Snap Time (Min)" FORMAT a15 COLUMN max_end_interval_time HEADING "Snap Time (Max)" FORMAT a15 COLUMN instance_number HEADING "I#" FORMAT 99 COLUMN SQL_ID HEADING "SQLId" FORMAT a13 COLUMN plan_hash_value HEADING "Plan|Hash|Value" COLUMN invalidations_total HEADING "Invalid|Total|(&COUNT_HEADING)" FORMAT 9,999 COLUMN iowait_total HEADING "IO|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT COLUMN apwait_total HEADING "Appl|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT COLUMN ccwait_total HEADING "Conc|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT COLUMN clwait_total HEADING "Cluster|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT COLUMN plsexec_time_total HEADING "PLsexec|Time|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT COLUMN cpu_time_total HEADING "CPU|Time|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT COLUMN elapsed_time_total HEADING "Elapsed|Time|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT COLUMN executions_total HEADING "Exec|Total|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT COLUMN rows_processed_total HEADING "Rows|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT COLUMN sorts_total HEADING "Sorts|Total|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT COLUMN buffer_gets_total HEADING "Buffer|Gets|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT COLUMN direct_writes_total HEADING "Direct|Write|Total|(&COUNT_HEADING)" FORMAT &TIME_FORMAT COLUMN io_interconnect_bytes_total HEADING "IO|Inter|Connect|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN physical_read_bytes_total HEADING "Phy|Read|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN physical_write_bytes_total HEADING "Phy|Write|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN optimized_physical_reads_total HEADING "Optimized|Phy|ReadReq|(&COUNT_HEADING)|PerExec" FORMAT &TIME_FORMAT COLUMN io_offload_elig_bytes_total HEADING "IO|Offload|Elig|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN io_offload_return_bytes_total HEADING "IO|Offload|Return|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT Prompt Prompt *************************************************************************************************** Prompt * A W R T O P &&TOP_RECORDCOUNT S Q L s Prompt * Prompt * - Ordered by Column &&ORDER_COLUMN_NUMBER Prompt * - Between &&from_timestamp and &&to_timestamp (YYYY-MM-DD HH24:MI:SS) Prompt * Prompt *************************************************************************************************** SELECT * FROM ( WITH snap as ( SELECT s.dbid , s.instance_number , s.snap_id , LAG (s.snap_id) OVER(PARTITION BY s.dbid, s.instance_number, ss.sql_id ORDER BY s.dbid, s.instance_number, s.snap_id) prev_snap_id , s.end_interval_time , s.startup_time FROM dba_hist_snapshot s , v$database d WHERE d.dbid = s.dbid AND s.end_interval_time BETWEEN TO_TIMESTAMP('&&from_timestamp','YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('&&to_timestamp','YYYY-MM-DD HH24:MI:SS') ) SELECT ss.sql_id , TO_CHAR(min(s.end_interval_time),'DD-MON-YY HH24:MI') min_end_interval_time , TO_CHAR(max(s.end_interval_time),'DD-MON-YY HH24:MI') max_end_interval_time -- , ss.plan_hash_value , ROUND(SUM((ss.iowait_total-LEAST(ss_prev.iowait_total,ss.iowait_total))/1000000/&TIME_DIVIDER)) iowait_total , ROUND(SUM((ss.apwait_total-LEAST(ss_prev.apwait_total,ss.apwait_total))/1000000/&TIME_DIVIDER)) apwait_total , ROUND(SUM((ss.ccwait_total-LEAST(ss_prev.ccwait_total,ss.ccwait_total))/1000000/&TIME_DIVIDER)) ccwait_total , ROUND(SUM((ss.clwait_total-LEAST(ss_prev.clwait_total,ss.clwait_total))/1000000/&TIME_DIVIDER) ) clwait_total , ROUND(SUM((ss.plsexec_time_total-LEAST(ss_prev.plsexec_time_total,ss.plsexec_time_total))/1000000/&TIME_DIVIDER)) plsexec_time_total , ROUND(SUM((ss.cpu_time_total-LEAST(ss_prev.cpu_time_total,ss.cpu_time_total))/1000000/&TIME_DIVIDER)) cpu_time_total , ROUND(SUM((ss.elapsed_time_total-LEAST(ss_prev.elapsed_time_total,ss.elapsed_time_total))/1000000/&TIME_DIVIDER)) elapsed_time_total --/* , ROUND(SUM(ss.executions_total-LEAST(ss_prev.executions_total,ss.executions_total))/&COUNT_SMALL_DIVIDER) executions_total , ROUND(SUM(ss.rows_processed_total-LEAST(ss_prev.rows_processed_total,ss.rows_processed_total))/&COUNT_DIVIDER) rows_processed_total , ROUND(SUM(ss.sorts_total-LEAST(ss_prev.sorts_total,ss.sorts_total))/&COUNT_SMALL_DIVIDER) sorts_total , ROUND(SUM(ss.buffer_gets_total-LEAST(ss_prev.buffer_gets_total,ss.buffer_gets_total))/&COUNT_DIVIDER) buffer_gets_total , ROUND(SUM(ss.direct_writes_total-LEAST(ss_prev.direct_writes_total,ss.direct_writes_total))/&COUNT_DIVIDER) direct_writes_total &&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.io_interconnect_bytes_total-LEAST(ss_prev.io_interconnect_bytes_total,ss.io_interconnect_bytes_total))/&BYTES_DIVIDER) io_interconnect_bytes_total , ROUND(SUM(ss.physical_read_bytes_total-LEAST(ss_prev.physical_read_bytes_total,ss.physical_read_bytes_total))/&BYTES_DIVIDER) physical_read_bytes_total , ROUND(SUM(ss.physical_write_bytes_total-LEAST(ss_prev.physical_write_bytes_total,ss.physical_write_bytes_total))/&BYTES_DIVIDER) physical_write_bytes_total &&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.optimized_physical_reads_total-LEAST(ss_prev.optimized_physical_reads_total,ss.optimized_physical_reads_total))/&BYTES_DIVIDER) optimized_physical_reads_total &&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.io_offload_elig_bytes_total-LEAST(ss_prev.io_offload_elig_bytes_total,ss.io_offload_elig_bytes_total))/&BYTES_DIVIDER) io_offload_elig_bytes_total &&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.io_offload_return_bytes_total-LEAST(ss_prev.io_offload_return_bytes_total,ss.io_offload_return_bytes_total))/&BYTES_DIVIDER) io_offload_return_bytes_total -- , MODULE -- , action --*/ from snap s , dba_hist_sqlstat ss , dba_hist_sqlstat ss_prev where ss.dbid = s.dbid AND ss.instance_number = s.instance_number AND ss.snap_id = s.snap_id AND ss_prev.dbid = s.dbid AND ss_prev.instance_number = s.instance_number AND ss_prev.snap_id = s.prev_snap_id AND ss_prev.sql_id = ss.sql_id AND ss_prev.plan_hash_value = ss.plan_hash_value AND (ss.executions_delta > 0 OR ss.snap_id = (select max(snap_id) from dba_hist_snapshot s2 where s2.dbid = s.dbid and s2.instance_number = s.instance_number ) ) GROUP BY ss.dbid , ss.sql_id order by &&ORDER_COLUMN_NUMBER DESC ) WHERE ROWNUM <= &&TOP_RECORDCOUNT ; UNDEFINE FROM_TIMESTAMP UNDEFINE TO_TIMESTAMP UNDEFINE TOP_RECORDCOUNT UNDEFINE ORDER_COLUMN_NUMBER --@@footer