@@header /* * * Author : Vishal Gupta * Purpose : Display SQLStats per execution from AWR * Version : 10.1 and above * Parameters : 1 - SQL_ID * 2 - PLAN_HASH_VALUE (Use % as wildcard) * 3 - Number of Days (Default 7 days) * * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 08-Mar-12 Vishal Gupta Intial version * 20-Mar-12 Vishal Gupta Added PLAN_HASH_VALUE in output * * */ DEFINE sqlid="&&1" DEFINE plan_hash_value="&&2" DEFINE days="&&3" DEFINE COUNT_FORMAT=9,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=9,999 --DEFINE TIME_DIVIDER="0.001" --DEFINE TIME_HEADING="msec" --DEFINE TIME_DIVIDER="1" --DEFINE TIME_HEADING="sec" DEFINE TIME_DIVIDER="60" DEFINE TIME_HEADING="min" COLUMN seperator HEADING "!|!|!|!" FORMAT a1 COLUMN end_interval_time HEADING "Snap Time" FORMAT a18 COLUMN instance_number HEADING "I#" FORMAT 99 COLUMN SQL_ID HEADING "SQLId" FORMAT a13 COLUMN plan_hash_value HEADING "Plan|Hash|Value" FORMAT 999999999999 COLUMN invalidations_delta HEADING "Invalid|(&COUNT_HEADING)|PerExec" FORMAT 9,999 COLUMN iowait_delta HEADING "IO|Wait|(&TIME_HEADING)|PerExec" FORMAT &TIME_FORMAT COLUMN apwait_delta HEADING "Appl|Wait|(&TIME_HEADING)|PerExec" FORMAT &TIME_FORMAT COLUMN ccwait_delta HEADING "Conc|Wait|(&TIME_HEADING)|PerExec" FORMAT &TIME_FORMAT COLUMN clwait_delta HEADING "Cluster|Wait|(&TIME_HEADING)|PerExec" FORMAT &TIME_FORMAT COLUMN plsexec_time_delta HEADING "PLsexec|Time|(&TIME_HEADING)|PerExec" FORMAT &TIME_FORMAT COLUMN cpu_time_delta HEADING "CPU|Time|(&TIME_HEADING)|PerExec" FORMAT &TIME_FORMAT COLUMN elapsed_time_delta HEADING "Elapsed|Time|(&TIME_HEADING)|PerExec" FORMAT &TIME_FORMAT COLUMN executions_delta HEADING "Exec|(&COUNT_HEADING)" FORMAT 9,999 COLUMN px_servers_execs_total HEADING "Par'l|Serv|Exec|(&COUNT_SMALL_HEADING)|PerExec" FORMAT 999 --COLUMN px_servers_execs_total HEADING "Par'l|Server|Exec|(&COUNT_SMALL_HEADING)|PerExec" FORMAT &COUNT_SMALL_FORMAT COLUMN rows_processed_delta HEADING "Rows|(&COUNT_HEADING)|PerExec" FORMAT &COUNT_FORMAT COLUMN sorts_delta HEADING "Sorts|(&COUNT_HEADING)|PerExec" FORMAT &COUNT_FORMAT COLUMN buffer_gets_delta HEADING "Buffer|Gets|(&COUNT_HEADING)|PerExec" FORMAT &COUNT_FORMAT COLUMN direct_writes_delta HEADING "Direct|Write|(&BYTES_HEADING)|PerExec" FORMAT &BYTES_FORMAT COLUMN io_interconnect_bytes_delta HEADING "IO|Inter|Connect|(&BYTES_HEADING)|PerExec" FORMAT &BYTES_FORMAT COLUMN physical_read_bytes_delta HEADING "Phy|Read|(&BYTES_HEADING)|PerExec" FORMAT &BYTES_FORMAT COLUMN physical_write_bytes_delta HEADING "Phy|Write|(&BYTES_HEADING)|PerExec" FORMAT &BYTES_FORMAT COLUMN io_offload_elig_bytes_delta HEADING "IO|Offload|Elig|(&BYTES_HEADING)|PerExec" FORMAT &BYTES_FORMAT COLUMN io_offload_return_bytes_delta HEADING "IO|Offload|Return|(&BYTES_HEADING)|PerExec" FORMAT &BYTES_FORMAT COLUMN sql_profile HEADING "sql_profile" FORMAT a30 Prompt Prompt *************************************************************************************************** Prompt * AWR SQL Executions Statistics (All Figures are per execution) Prompt * SQLID = &&sqlid , plan_hash_value = &plan_hash_value over last &days days Prompt *************************************************************************************************** WITH snaps AS ( SELECT ss.dbid , ss.instance_number , ss.snap_id , LAG(ss.snap_id) OVER (PARTITION BY s.dbid, s.instance_number, ss.sql_id, ss.plan_hash_value ORDER BY ss.dbid, ss.instance_number, ss.snap_id , ss.sql_id, ss.plan_hash_value) prev_snap_id , ss.sql_id , ss.plan_hash_value , s.end_interval_time from dba_hist_sqlstat ss , dba_hist_snapshot s , v$database d where 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 = '&&sqlid' AND ss.plan_hash_value LIKE '&&plan_hash_value' AND ss.executions_delta > 0 ) SELECT TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI:SS') end_interval_time , ss.instance_number , ss.sql_id , ss.plan_hash_value , '|' seperator , ROUND((ss.iowait_total-ss_prev.iowait_total)/1000000/ss.executions_delta/&TIME_DIVIDER) iowait_delta , ROUND((ss.apwait_total-ss_prev.apwait_total)/1000000/ss.executions_delta/&TIME_DIVIDER) apwait_delta , ROUND((ss.ccwait_total-ss_prev.ccwait_total)/1000000/ss.executions_delta/&TIME_DIVIDER) ccwait_delta , ROUND((ss.clwait_total-ss_prev.clwait_total)/1000000/ss.executions_delta/&TIME_DIVIDER) clwait_delta , ROUND((ss.plsexec_time_total-ss_prev.plsexec_time_total)/1000000/ss.executions_delta/&TIME_DIVIDER) plsexec_time_delta , ROUND((ss.cpu_time_total-ss_prev.cpu_time_total)/1000000/ss.executions_delta/&TIME_DIVIDER) cpu_time_delta , ROUND((ss.elapsed_time_total-ss_prev.elapsed_time_total)/1000000/ss.executions_delta/&TIME_DIVIDER) elapsed_time_delta , '|' seperator , ss.executions_delta/&COUNT_DIVIDER executions_delta &&_IF_ORA_10gR2_OR_HIGHER , (ss.px_servers_execs_total-ss_prev.px_servers_execs_total)/ss.executions_total/&COUNT_DIVIDER px_servers_execs_delta -- , (ss.invalidations_total-ss_prev.invalidations_total)/ss.executions_total/&COUNT_DIVIDER invalidations_delta , (ss.rows_processed_total-ss_prev.rows_processed_total)/ss.executions_delta/&COUNT_DIVIDER rows_processed_delta , (ss.buffer_gets_total-ss_prev.buffer_gets_total)/ss.executions_delta/&COUNT_DIVIDER buffer_gets_delta , ROUND((ss.direct_writes_total-ss_prev.direct_writes_total)/ss.executions_delta/&BYTES_DIVIDER) direct_writes_delta , '|' seperator &&_IF_ORA_11gR2_OR_HIGHER , (ss.io_interconnect_bytes_total-ss_prev.io_interconnect_bytes_total)/ss.executions_delta/&BYTES_DIVIDER io_interconnect_bytes_delta &&_IF_ORA_11gR2_OR_HIGHER , (ss.physical_read_bytes_total-ss_prev.physical_read_bytes_total)/ss.executions_delta/&BYTES_DIVIDER physical_read_bytes_delta &&_IF_ORA_11gR2_OR_HIGHER , (ss.physical_write_bytes_total-ss_prev.physical_write_bytes_total)/ss.executions_delta/&BYTES_DIVIDER physical_write_bytes_delta &&_IF_ORA_11gR2_OR_HIGHER , (ss.io_offload_elig_bytes_total-ss_prev.io_offload_elig_bytes_total)/ss.executions_delta/&BYTES_DIVIDER io_offload_elig_bytes_delta &&_IF_ORA_11gR2_OR_HIGHER , (ss.io_offload_return_bytes_total-ss_prev.io_offload_return_bytes_total)/ss.executions_delta/&BYTES_DIVIDER io_offload_return_bytes_delta , ss.sql_profile from dba_hist_sqlstat ss , dba_hist_sqlstat ss_prev , snaps s where ss.dbid = s.dbid AND ss.instance_number = s.instance_number AND ss.snap_id = s.snap_id AND ss.sql_id = s.sql_id AND ss.plan_hash_value = s.plan_hash_value 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 (+) = s.sql_id AND ss_prev.plan_hash_value (+) = s.plan_hash_value order by s.end_interval_time , s.instance_number ; UNDEFINE sqlid UNDEFINE days @@footer