@@header /* * * Author : Vishal Gupta * Purpose : Display top SQLs from AWR (By Weight) * Version : 10.1 and above * Parameters : 1 - Instance Number (Default is '%' ie ALL) * 2 - From Timestamp (YYYY-MM-DD HH24:MI:SS) * 3 - To Timestamp (YYYY-MM-DD HH24:MI:SS) * 4 - Top record Count * 5 - Where Clause * * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 20-JAN-14 Vishal Gupta Intial version * */ set lines 20000 UNDEFINE INST_ID UNDEFINE FROM_TIMESTAMP UNDEFINE TO_TIMESTAMP UNDEFINE TOP_RECORDCOUNT UNDEFINE WHERECLAUSE DEFINE INST_ID="&&1" DEFINE FROM_TIMESTAMP="&&2" DEFINE TO_TIMESTAMP="&3" DEFINE TOP_RECORDCOUNT="&4" DEFINE WHERECLAUSE="&&5" DEFINE COUNT_SMALL_FORMAT=999,999,999,999 DEFINE COUNT_SMALL_DIVIDER="1" DEFINE COUNT_SMALL_HEADING="#" --DEFINE COUNT_SMALL_DIVIDER="1000" --DEFINE COUNT_SMALL_HEADING="#1000" --DEFINE COUNT_SMALL_DIVIDER="1000000" --DEFINE COUNT_SMALL_HEADING="#million" 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" DEFINE WEIGHT_FORMAT=999,999 --DEFINE WEIGHT_DIVIDER="1000" --DEFINE WEIGHT_HEADING="#1000" DEFINE WEIGHT_DIVIDER="1000000" DEFINE WEIGHT_HEADING="Million" --DEFINE WEIGHT_DIVIDER="1000000000" --DEFINE WEIGHT_HEADING="Billion" DEFINE elapsed_time_weight=10 DEFINE executions_weight=0.1 DEFINE buffer_gets_weight=0.00000001 DEFINE physical_read_bytes_weight=0.00000015 DEFINE physical_write_bytes_weight=0.000001 COLUMN min_time HEADING "Snap Time (Min)" FORMAT a15 COLUMN max_time HEADING "Snap Time (Max)" FORMAT a15 COLUMN instance_number HEADING "I#" FORMAT 99 COLUMN username HEADING "UserName" FORMAT a20 COLUMN SQL_ID HEADING "SQLId" FORMAT a13 COLUMN force_matching_signature HEADING "Force matching signature" FORMAT a30 COLUMN plan_hash_value HEADING "Plan|Hash|Value" COLUMN weight HEADING "Weight|(&WEIGHT_HEADING)" FORMAT &&WEIGHT_FORMAT NOPRINT COLUMN reason HEADING "Top|Reason" FORMAT a11 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_HEADING)" FORMAT &COUNT_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 direct_write_bytes_total HEADING "Direct|Write|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN physical_change_bytes_total HEADING "Phy|Change|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT COLUMN optimized_physical_reads_total HEADING "Optimized|Phy|ReadReq|(&COUNT_HEADING)" 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 * Input Parameters Prompt * - Instance Number - "&&INST_ID" Prompt * - From Timestamp - "&&from_timestamp" (YYYY-MM-DD HH24:MI:SS) Prompt * - To Timestamp - "&&to_timestamp" (YYYY-MM-DD HH24:MI:SS) Prompt * - Top RecordCount - "&&TOP_RECORDCOUNT" Prompt * - Where Clause - "&&WHERECLAUSE" Prompt *************************************************************************************************** set timing on WITH topsql as ( SELECT /*+ NO_MERGE NO_UNNEST */ * FROM ( SELECT /*+ LEADING(d s) USE_NL (d s ss) PARALLEL(s,8) PARALLEL(ss,8)*/ CASE WHEN REGEXP_LIKE(DECODE(ss.force_matching_signature,0,ss.sql_id,ss.force_matching_signature), '[a-z]') THEN '0' ELSE DECODE(ss.force_matching_signature,0,ss.sql_id,ss.force_matching_signature) END force_matching_signature , max(ss.sql_id) sql_id , DECODE( GREATEST(SUM(ss.elapsed_time_delta)/1000000 * &&elapsed_time_weight , SUM(ss.executions_delta) * &&executions_weight --, SUM(ss.buffer_gets_delta) * &&buffer_gets_weight , SUM(ss.physical_read_bytes_delta) * &&physical_read_bytes_weight , SUM(ss.physical_write_bytes_delta) * &&physical_write_bytes_weight ) , SUM(ss.elapsed_time_delta)/1000000 * &&elapsed_time_weight ,'CPU/Elapsed' , SUM(ss.executions_delta) * &&executions_weight ,'Execs' --, SUM(ss.buffer_gets_delta) * &&buffer_gets_weight ,'BufferGets' , SUM(ss.physical_read_bytes_delta) * &&physical_read_bytes_weight ,'PhyRead' , SUM(ss.physical_write_bytes_delta) * &&physical_write_bytes_weight ,'PhyWrite' ) Reason , ROUND(( SUM(ss.elapsed_time_delta)/1000000 * &&elapsed_time_weight + SUM(ss.executions_delta) * &&executions_weight --+ SUM(ss.buffer_gets_delta) * &&buffer_gets_weight + SUM(ss.physical_read_bytes_delta) * &&physical_read_bytes_weight + SUM(ss.physical_write_bytes_delta) * &&physical_write_bytes_weight )/&WEIGHT_DIVIDER) weight /* , ROUND(SUM(ss.elapsed_time_delta)/1000000 * &&elapsed_time_weight) elapsed_time_weight , ROUND(SUM(ss.executions_delta) * &&executions_weight) executions_weight --, ROUND(SUM(ss.buffer_gets_delta) * &&buffer_gets_weight) buffer_gets_weight , ROUND(SUM(ss.physical_read_bytes_delta) * &&physical_read_bytes_weight) physical_read_bytes_weight , ROUND(SUM(ss.physical_write_bytes_delta) * &&physical_write_bytes_weight) physical_write_bytes_weight */ , ROUND(SUM(ss.iowait_delta)/1000000 /&TIME_DIVIDER) iowait_total , ROUND(SUM(ss.apwait_delta)/1000000 /&TIME_DIVIDER) apwait_total , ROUND(SUM(ss.ccwait_delta)/1000000 /&TIME_DIVIDER) ccwait_total , ROUND(SUM(ss.clwait_delta)/1000000 /&TIME_DIVIDER) clwait_total , ROUND(SUM(ss.plsexec_time_delta)/1000000 /&TIME_DIVIDER) plsexec_time_total , ROUND(SUM(ss.cpu_time_delta)/1000000 /&TIME_DIVIDER) cpu_time_total , ROUND(SUM(ss.elapsed_time_delta)/1000000 /&TIME_DIVIDER) elapsed_time_total , ROUND(SUM(ss.executions_delta) /&COUNT_SMALL_DIVIDER) executions_total , ROUND(SUM(ss.rows_processed_delta) /&COUNT_DIVIDER) rows_processed_total , ROUND(SUM(ss.sorts_delta) /&COUNT_SMALL_DIVIDER) sorts_total , ROUND(SUM(ss.buffer_gets_delta) /&COUNT_DIVIDER) buffer_gets_total &&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.io_interconnect_bytes_delta) /&BYTES_DIVIDER) io_interconnect_bytes_total , ROUND(SUM(ss.physical_read_bytes_delta) /&BYTES_DIVIDER) physical_read_bytes_total , ROUND(SUM(ss.physical_write_bytes_delta) /&BYTES_DIVIDER) physical_write_bytes_total , ROUND(SUM(ss.direct_writes_delta * p.value) /&BYTES_DIVIDER) direct_write_bytes_total , ROUND(SUM(ss.physical_write_bytes_delta - (ss.direct_writes_delta * p.value)) /&BYTES_DIVIDER) physical_change_bytes_total &&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.optimized_physical_reads_delta)/&BYTES_DIVIDER) optimized_physical_reads_total &&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.io_offload_elig_bytes_delta) /&BYTES_DIVIDER) io_offload_elig_bytes_total &&_IF_ORA_11gR2_OR_HIGHER , ROUND(SUM(ss.io_offload_return_bytes_delta) /&BYTES_DIVIDER) io_offload_return_bytes_total from v$database d , dba_hist_snapshot s , dba_hist_sqlstat ss , (select value from v$system_parameter where name = 'db_block_size') p where d.dbid = s.dbid AND ss.dbid = s.dbid AND ss.instance_number = s.instance_number AND ss.snap_id = s.snap_id AND ss.instance_number LIKE '&&INST_ID' 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') &&WHERECLAUSE GROUP BY ss.dbid , DECODE(ss.force_matching_signature,0,ss.sql_id,ss.force_matching_signature) order by weight DESC NULLS LAST ) WHERE ROWNUM <= &&TOP_RECORDCOUNT ) SELECT /*+ */ --/* ( SELECT /*+ first_rows(ash) */ u.username FROM dba_hist_active_sess_history ash , dba_users u , v$database d WHERE d.dbid = ash.dbid AND ash.user_id = u.user_id AND ash.sql_id = ss.sql_id and ash.sample_time between TO_TIMESTAMP('&&from_timestamp','YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP('&&to_timestamp','YYYY-MM-DD HH24:MI:SS') and rownum = 1 ) username , --*/ ss.* from topsql ss ; set timing off @@footer