@@header /* * * Author : Vishal Gupta * Purpose : Display SQLStats from AWR * Version : 10.1 and above * Parameters : 1 - SQL_ID * 2 - Number of Days (Default 30 days) * * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 08-Mar-12 Vishal Gupta Intial version * 20-Mar-12 Vishal Gupta Added PLAN_HASH_VALUE in output * 23-Apr-12 Vishal Gupta Added PLAN_HASH_VALUE as the input variable * 09-Oct-12 Vishal Gupta Removed the instance_number from output and * aggregated stats for all instances in output. * */ /************************************ * INPUT PARAMETERS ************************************/ UNDEFINE sql_id UNDEFINE days UNDEFINE whereclause UNDEFINE force_matching_signature DEFINE sql_id="&&1" DEFINE days="&&2" DEFINE whereclause="&&4" DEFINE force_matching_signature="" COLUMN _days NEW_VALUE days NOPRINT COLUMN _force_matching_signature NEW_VALUE force_matching_signature NOPRINT set term off SELECT DECODE('&&days','','30','&&days') "_days" , DECODE('&&sql_id','%','%','') "_force_matching_signature" FROM DUAL; set term on /************************************ * CONFIGURATION PARAMETERS ************************************/ DEFINE COUNT_SMALL_FORMAT=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="#mill" DEFINE COUNT_FORMAT=99,999,999 DEFINE COUNT_DIVIDER="1" DEFINE COUNT_HEADING="#" --DEFINE COUNT_DIVIDER="1000" --DEFINE COUNT_HEADING="#1000" --DEFINE COUNT_DIVIDER="1000000" --DEFINE COUNT_HEADING="million" DEFINE COUNT_LARGE_FORMAT=99,999,999 --DEFINE COUNT_LARGE_DIVIDER="1" --DEFINE COUNT_LARGE_HEADING="#" DEFINE COUNT_LARGE_DIVIDER="1000" DEFINE COUNT_LARGE_HEADING="#1000" --DEFINE COUNT_LARGE_DIVIDER="1000000" --DEFINE COUNT_LARGE_HEADING="million" 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=999,999 --DEFINE TIME_DIVIDER="1" --DEFINE TIME_HEADING="sec" DEFINE TIME_DIVIDER="60" DEFINE TIME_HEADING="min" /************************************/ COLUMN _force_matching_signature NEW_VALUE force_matching_signature NOPRINT set term off select to_char(ss2.force_matching_signature) "_force_matching_signature" from v$database d JOIN sys.WRH$_SQLSTAT ss2 on ss2.dbid = d.dbid where ss2.sql_id = '&&sql_id' and rownum = 1; set term on /************************************/ Prompt Prompt *************************************************************************************************** Prompt * AWR SQL Executions Statistics (Daily - Delta) Prompt * (Force Matching Signature = '&&force_matching_signature') PROMPT * PROMPT * Input Parameters PROMPT * - SQL Id = '&&sql_id' PROMPT * - Days = '&&days' PROMPT * - WhereClause = '&&whereclause' Prompt *************************************************************************************************** COLUMN seperator HEADING "!|!|!|!" FORMAT a1 COLUMN end_interval_time HEADING "Date" FORMAT a9 COLUMN instance_number HEADING "I#" FORMAT 99 COLUMN SQL_ID HEADING "SQLId" FORMAT a13 COLUMN force_matching_signature HEADING "Force Matching Signature" FORMAT 999999999999999999999 COLUMN plan_hash_value HEADING "Plan|Hash|Value" FORMAT 999999999999 COLUMN invalidations_delta HEADING "Invalid|Delta|(&&COUNT_HEADING)" FORMAT 9,999 COLUMN iowait_delta HEADING "IO|Wait|Delta|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN apwait_delta HEADING "Appl|Wait|Delta|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN ccwait_delta HEADING "Conc|Wait|Delta|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN clwait_delta HEADING "Cluster|Wait|Delta|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN plsexec_time_delta HEADING "PLsexec|Time|Delta|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN cpu_time_delta HEADING "CPU|Time|Delta|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN elapsed_time_delta HEADING "Elapsed|Time|Delta|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN executions_delta HEADING "Exec|Delta|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT COLUMN px_servers_execs_delta HEADING "Par'l|Serv|Exec|Delta|(&&COUNT_HEADING)" FORMAT 999 COLUMN rows_processed_delta HEADING "Rows|Delta|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT COLUMN sorts_delta HEADING "Sorts|Delta|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT COLUMN buffer_gets_bytes_delta HEADING "Logical|Read|Delta|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN buffer_gets_delta HEADING "Buffer|Gets|Delta|(&&COUNT_LARGE_HEADING)" FORMAT &&COUNT_LARGE_FORMAT COLUMN direct_writes_delta HEADING "Direct|Write|Delta|(&&BYTES_HEADING)" FORMAT &&COUNT_FORMAT COLUMN io_interconnect_bytes_delta HEADING "IO|Inter|Connect|Delta|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN physical_read_bytes_delta HEADING "Phy|Read|Delta|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN physical_write_bytes_delta HEADING "Phy|Write|Delta|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN optimized_physical_reads_delta HEADING "Optimized|Phy|ReadReq|Delta|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT COLUMN io_offload_elig_bytes_delta HEADING "IO|Offload|Elig|Delta|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN io_offload_return_bytes_delta HEADING "IO|Offload|Return|Delta|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN sql_profile HEADING "sql_profile" FORMAT a30 SELECT TO_CHAR(TRUNC(s.end_interval_time,'DD'),'DD-MON-YY') end_interval_time , ss.force_matching_signature , max(ss.sql_id) sql_id --, ss.plan_hash_value , ROUND(SUM(ss.iowait_delta)/1000000/&&TIME_DIVIDER) iowait_delta , ROUND(SUM(ss.apwait_delta)/1000000/&&TIME_DIVIDER) apwait_delta , ROUND(SUM(ss.ccwait_delta)/1000000/&&TIME_DIVIDER) ccwait_delta , ROUND(SUM(ss.clwait_delta)/1000000/&&TIME_DIVIDER) clwait_delta , ROUND(SUM(ss.plsexec_time_delta)/1000000/&&TIME_DIVIDER) plsexec_time_delta , ROUND(SUM(ss.cpu_time_delta)/1000000/&&TIME_DIVIDER) cpu_time_delta , ROUND(SUM(ss.elapsed_time_delta)/1000000/&&TIME_DIVIDER) elapsed_time_delta , '|' seperator , SUM(ss.executions_delta)/&&COUNT_DIVIDER executions_delta --&&_IF_ORA_10gR2_OR_HIGHER , SUM(ss.px_servers_execs_delta)/&&COUNT_DIVIDER px_servers_execs_delta -- , SUM(ss.invalidations_delta)/&&COUNT_DIVIDER invalidations_delta , SUM(ss.rows_processed_delta)/&&COUNT_DIVIDER rows_processed_delta , SUM(ss.sorts_delta)/&&COUNT_DIVIDER sorts_delta , SUM(ss.buffer_gets_delta)/&&COUNT_LARGE_DIVIDER buffer_gets_delta , SUM(ss.buffer_gets_delta* p.value)/&&BYTES_DIVIDER buffer_gets_bytes_delta , ROUND(SUM(ss.direct_writes_delta)/&&COUNT_DIVIDER) direct_writes_delta , '|' seperator &&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.io_interconnect_bytes_delta)/&&BYTES_DIVIDER io_interconnect_bytes_delta &&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.physical_read_bytes_delta)/&&BYTES_DIVIDER physical_read_bytes_delta &&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.physical_write_bytes_delta)/&&BYTES_DIVIDER physical_write_bytes_delta &&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.optimized_physical_reads_delta)/&&COUNT_DIVIDER optimized_physical_reads_delta &&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.io_offload_elig_bytes_delta)/&&BYTES_DIVIDER io_offload_elig_bytes_delta &&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.io_offload_return_bytes_delta)/&&BYTES_DIVIDER io_offload_return_bytes_delta , '|' seperator from dba_hist_sqlstat ss , dba_hist_snapshot s , v$database d , v$system_parameter p where p.name = 'db_block_size' AND ss.instance_number = s.instance_number AND ss.dbid = s.dbid AND ss.snap_id = s.snap_id AND ss.dbid = d.dbid AND s.end_interval_time > SYSDATE - &&days AND ss.sql_id LIKE CASE ss.force_matching_signature WHEN 0 THEN '&&sql_id' ELSE '%' END AND ss.force_matching_signature LIKE '&&force_matching_signature' --AND ss.executions_delta > 0 &&whereclause GROUP BY ss.dbid , TRUNC(s.end_interval_time,'DD') , ss.force_matching_signature --, ss.plan_hash_value ORDER BY TRUNC(s.end_interval_time,'DD') , ss.force_matching_signature --, ss.plan_hash_value ; UNDEFINE sql_id UNDEFINE plan_hash_value UNDEFINE days UNDEFINE force_matching_signature UNDEFINE whereclause @@footer