245 lines
14 KiB
SQL
245 lines
14 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display SQLStats 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)
|
|
* 4 - Whereclause
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 10-Feb-16 Vishal Gupta Add following columns to output
|
|
* - End of Fetch Count
|
|
* - Phy Read Requests
|
|
* - Phy Write Requests
|
|
* Added conditional use of follwoing based on version
|
|
* - Force matching signature
|
|
* 13-Feb-14 Vishal Gupta Add PX_SERVERS to output
|
|
* 09-Oct-12 Vishal Gupta Removed the instance_number from output and
|
|
* aggregated stats for all instances in output.
|
|
* 23-Apr-12 Vishal Gupta Added PLAN_HASH_VALUE as the input variable
|
|
* 20-Mar-12 Vishal Gupta Added PLAN_HASH_VALUE in output
|
|
* 08-Mar-12 Vishal Gupta Intial version
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE sql_id
|
|
UNDEFINE plan_hash_value
|
|
UNDEFINE days
|
|
UNDEFINE whereclause
|
|
UNDEFINE force_matching_signature
|
|
UNDEFINE use_force_matching_signature
|
|
|
|
DEFINE sql_id="&&1"
|
|
DEFINE plan_hash_value="&&2"
|
|
DEFINE days="&&3"
|
|
DEFINE whereclause="&&4"
|
|
DEFINE force_matching_signature=""
|
|
|
|
COLUMN _plan_hash_value NEW_VALUE plan_hash_value NOPRINT
|
|
COLUMN _days NEW_VALUE days NOPRINT
|
|
COLUMN _force_matching_signature NEW_VALUE force_matching_signature NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&plan_hash_value','','%','&&plan_hash_value') "_plan_hash_value"
|
|
, DECODE('&&days','','7','&&days') "_days"
|
|
, DECODE('&&sql_id','%','%','') "_force_matching_signature"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
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=99,999
|
|
DEFINE COUNT_DIVIDER="1"
|
|
DEFINE COUNT_HEADING="#"
|
|
--DEFINE COUNT_DIVIDER="1000"
|
|
--DEFINE COUNT_HEADING="#1000"
|
|
|
|
DEFINE COUNT_LARGE_FORMAT=99,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=99,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="60"
|
|
DEFINE TIME_HEADING="min"
|
|
|
|
/************************************/
|
|
|
|
COLUMN _force_matching_signature NEW_VALUE force_matching_signature NOPRINT
|
|
|
|
set term off
|
|
select NULL
|
|
&&_IF_ORA_10gR2_OR_HIGHER , to_char(ss2.force_matching_signature) "_force_matching_signature"
|
|
from v$database d
|
|
, dba_hist_sqlstat ss2
|
|
where d.dbid = ss2.dbid
|
|
AND ss2.sql_id = '&&sql_id'
|
|
and rownum = 1
|
|
;
|
|
|
|
select CASE WHEN '&&force_matching_signature' IS NULL THEN '0'
|
|
WHEN '&&force_matching_signature' = '' THEN '0'
|
|
ELSE '&&force_matching_signature'
|
|
END "_force_matching_signature"
|
|
from dual
|
|
;
|
|
set term on
|
|
|
|
/************************************/
|
|
|
|
Prompt
|
|
Prompt **************************************************************
|
|
Prompt * AWR SQL Executions Statistics (Cummulative)
|
|
Prompt * (Force Matching Signature = '&&force_matching_signature')
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - SQL Id = '&&sql_id'
|
|
PROMPT * - Plan Hash Value = '&&plan_hash_value'
|
|
PROMPT * - Days = '&&days'
|
|
PROMPT * - WhereClause = '&&whereclause'
|
|
Prompt **************************************************************
|
|
|
|
COLUMN seperator HEADING "!|!|!|!" FORMAT a1
|
|
COLUMN end_interval_time HEADING "Snap Time" FORMAT a15
|
|
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 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_delta HEADING "Exec|Delta|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT
|
|
COLUMN executions_total HEADING "Exec|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT
|
|
COLUMN end_of_fetch_count_total HEADING "End Of|Fetch|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT
|
|
COLUMN invalidations_total HEADING "Invalid|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT
|
|
COLUMN px_servers_execs_total HEADING "Par'l|Serv|Exec|Total|(&COUNT_SMALL_HEADING)" FORMAT 999
|
|
--COLUMN px_servers_execs_total HEADING "Par'l|Server|Exec|Total|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT
|
|
COLUMN rows_processed_total HEADING "Rows|Total|(&COUNT_LARGE_HEADING)" FORMAT &COUNT_LARGE_FORMAT
|
|
COLUMN sorts_total HEADING "Sorts|Total|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT
|
|
COLUMN buffer_gets_bytes_delta HEADING "Logical|Read|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN buffer_gets_total HEADING "Buffer|Gets|Total|(&COUNT_LARGE_HEADING)" FORMAT &COUNT_LARGE_FORMAT
|
|
COLUMN physical_read_requests_total HEADING "Phy|Read|Req|(&COUNT_LARGE_HEADING)" FORMAT &COUNT_LARGE_FORMAT
|
|
COLUMN physical_write_requests_total HEADING "Phy|Write|Req|(&COUNT_LARGE_HEADING)" FORMAT &COUNT_LARGE_FORMAT
|
|
COLUMN direct_writes_total HEADING "Direct|Path|Write|Total|(&COUNT_LARGE_HEADING)" FORMAT &COUNT_LARGE_FORMAT
|
|
COLUMN optimized_physical_reads_total HEADING "Optim'd|Phy|Read|Req|(&COUNT_LARGE_HEADING)" FORMAT &COUNT_LARGE_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 io_interconnect_bytes_total HEADING "IO|Inter|Connect|Total|(&BYTES_HEADING)" FORMAT &BYTES_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
|
|
COLUMN cell_uncompressed_bytes_total HEADING "Cell|UnComp|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN sql_profile HEADING "sql_profile" FORMAT a30
|
|
|
|
SELECT TO_CHAR(MAX(ss.end_interval_time),'DD-MON-YY HH24:MI') end_interval_time
|
|
--, ss.instance_number
|
|
, ss.sql_id
|
|
, ss.plan_hash_value
|
|
, ROUND(SUM(ss.iowait_total)/1000000/&TIME_DIVIDER) iowait_total
|
|
, ROUND(SUM(ss.apwait_total)/1000000/&TIME_DIVIDER) apwait_total
|
|
, ROUND(SUM(ss.ccwait_total)/1000000/&TIME_DIVIDER) ccwait_total
|
|
, ROUND(SUM(ss.clwait_total)/1000000/&TIME_DIVIDER) clwait_total
|
|
--, ROUND(SUM(ss.plsexec_time_total)/1000000/&TIME_DIVIDER) plsexec_time_total
|
|
, ROUND(SUM(ss.cpu_time_total)/1000000/&TIME_DIVIDER) cpu_time_total
|
|
, ROUND(SUM(ss.elapsed_time_total)/1000000/&TIME_DIVIDER) elapsed_time_total
|
|
, '|' seperator
|
|
, SUM(ss.executions_total)/&COUNT_DIVIDER executions_total
|
|
&&_IF_ORA_10gR2_OR_HIGHER , SUM(ss.px_servers_execs_total)/&COUNT_SMALL_DIVIDER px_servers_execs_total
|
|
, SUM(ss.sorts_total)/&COUNT_SMALL_DIVIDER sorts_total
|
|
, SUM(ss.end_of_fetch_count_total )/&COUNT_DIVIDER end_of_fetch_count_total
|
|
--, SUM(ss.invalidations_total)/&COUNT_DIVIDER invalidations_total
|
|
, SUM(ss.rows_processed_total)/&COUNT_LARGE_DIVIDER rows_processed_total
|
|
, SUM(ss.buffer_gets_total)/&COUNT_LARGE_DIVIDER buffer_gets_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , SUM(ROUND(ss.physical_read_requests_total)/&COUNT_LARGE_DIVIDER) physical_read_requests_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , SUM(ROUND(ss.physical_write_requests_total)/&COUNT_LARGE_DIVIDER) physical_write_requests_total
|
|
, SUM(ROUND(ss.direct_writes_total)/&COUNT_LARGE_DIVIDER) direct_writes_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , SUM(ROUND(ss.optimized_physical_reads_total)/&COUNT_LARGE_DIVIDER) optimized_physical_reads_total
|
|
, '|' seperator
|
|
-- , SUM(ss.buffer_gets_delta* ss.value)/&&BYTES_DIVIDER buffer_gets_bytes_delta
|
|
&&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.io_interconnect_bytes_total)/&BYTES_DIVIDER io_interconnect_bytes_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.physical_read_bytes_total)/&BYTES_DIVIDER physical_read_bytes_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.physical_write_bytes_total)/&BYTES_DIVIDER physical_write_bytes_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.io_offload_elig_bytes_total)/&BYTES_DIVIDER io_offload_elig_bytes_total
|
|
&&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.io_offload_return_bytes_total)/&BYTES_DIVIDER io_offload_return_bytes_total
|
|
--&&_IF_ORA_11gR2_OR_HIGHER , SUM(ss.cell_uncompressed_bytes_total)/&BYTES_DIVIDER cell_uncompressed_bytes_total
|
|
, '|' seperator
|
|
, ss.sql_profile
|
|
-- , ss.module
|
|
-- , ss.action
|
|
FROM
|
|
(
|
|
SELECT s.end_interval_time
|
|
, ss.*
|
|
, p.value
|
|
, NVL(LEAD (ss.snap_id) OVER (ORDER BY ss.dbid, ss.instance_number, ss.snap_id),0) next_snap_id
|
|
from dba_hist_sqlstat ss
|
|
, dba_hist_snapshot s
|
|
, v$database d
|
|
, v$system_parameter p
|
|
where p.name = 'db_block_size'
|
|
AND s.dbid = d.dbid
|
|
AND ss.instance_number = s.instance_number
|
|
AND ss.dbid = s.dbid
|
|
AND ss.snap_id = s.snap_id
|
|
AND s.end_interval_time > SYSDATE - &&days
|
|
&&_IF_ORA_10gR1_OR_LOWER AND ss.sql_id = '&&sql_id'
|
|
&&_IF_ORA_10gR2_OR_HIGHER AND ss.sql_id LIKE CASE ss.force_matching_signature
|
|
&&_IF_ORA_10gR2_OR_HIGHER WHEN 0 THEN '&&sql_id'
|
|
&&_IF_ORA_10gR2_OR_HIGHER ELSE '%'
|
|
&&_IF_ORA_10gR2_OR_HIGHER END
|
|
&&_IF_ORA_10gR2_OR_HIGHER AND ss.force_matching_signature = '&&force_matching_signature'
|
|
AND ss.plan_hash_value LIKE '&&plan_hash_value'
|
|
&&whereclause
|
|
) ss
|
|
WHERE (ss.executions_delta > 0 OR ss.snap_id + 1 <> ss.next_snap_id)
|
|
GROUP BY ss.dbid
|
|
, ss.snap_id
|
|
, ss.sql_id
|
|
, ss.plan_hash_value
|
|
, ss.sql_profile
|
|
ORDER BY TO_CHAR(MAX(ss.end_interval_time),'YYYY-MM-DD HH24:MI')
|
|
, ss.plan_hash_value
|
|
, ss.sql_profile
|
|
;
|
|
|
|
|
|
@@footer
|