156 lines
10 KiB
SQL
156 lines
10 KiB
SQL
@@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
|