111 lines
4.1 KiB
SQL
111 lines
4.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display unstable execution plan from AWR
|
|
* Version :
|
|
* Parameters : 1 - From Timestamp (YYYY-MM-DD HH24:MI:SS)
|
|
* 2 - To Timestamp (YYYY-MM-DD HH24:MI:SS)
|
|
* 3 - Top record Count
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 12-Jun-12 Vishal Gupta Intial version
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE FROM_TIMESTAMP
|
|
UNDEFINE TO_TIMESTAMP
|
|
UNDEFINE TOP_RECORDCOUNT
|
|
|
|
DEFINE FROM_TIMESTAMP="&&1"
|
|
DEFINE TO_TIMESTAMP="&2"
|
|
DEFINE TOP_RECORDCOUNT="&3"
|
|
|
|
|
|
COLUMN _FROM_TIMESTAMP NEW_VALUE FROM_TIMESTAMP NOPRINT
|
|
COLUMN _TO_TIMESTAMP NEW_VALUE TO_TIMESTAMP NOPRINT
|
|
COLUMN _TOP_RECORDCOUNT NEW_VALUE TOP_RECORDCOUNT NOPRINT
|
|
|
|
|
|
set term off
|
|
SELECT DECODE('&&FROM_TIMESTAMP','',to_char(sysdate - 7 - (1/1440),'YYYY-MM-DD HH24:MI:SS'),'&&FROM_TIMESTAMP') "_FROM_TIMESTAMP"
|
|
, DECODE('&&TO_TIMESTAMP','',to_char(sysdate + (1/1440),'YYYY-MM-DD HH24:MI:SS'),'&&TO_TIMESTAMP') "_TO_TIMESTAMP"
|
|
, DECODE('&&TOP_RECORDCOUNT','','20','&&TOP_RECORDCOUNT') "_TOP_RECORDCOUNT"
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * Top &&TOP_RECORDCOUNT SQLIds with unstable execution plan
|
|
PROMPT ***********************************************************************
|
|
|
|
|
|
COLUMN force_matching_signature HEADING "Force Matching Signature" FORMAT 999999999999999999999
|
|
COLUMN elapsed_time_perexec_max HEADING "Max|Elapsed|Time|(sec)" FORMAT 999,999,999.999
|
|
COLUMN elapsed_time_perexec_min HEADING "Min|Elapsed|Time|(sec)" FORMAT 999,999,999.999
|
|
COLUMN elapsed_time_perexec_avg HEADING "Avg|Elapsed|Time|(sec)" FORMAT 999,999,999.999
|
|
COLUMN elapsed_time_perexec_deviation HEADING "Deviation|Elapsed|Time|(sec)" FORMAT 999,999,999.999
|
|
|
|
SELECT *
|
|
FROM
|
|
(
|
|
SELECT ss.force_matching_signature
|
|
, max(ss.sql_id) sql_id
|
|
, max(ss.elapsed_time_total/1000000/ss.executions_total) elapsed_time_perexec_max
|
|
, min(ss.elapsed_time_total/1000000/ss.executions_total) elapsed_time_perexec_min
|
|
, avg(ss.elapsed_time_total/1000000/ss.executions_total) elapsed_time_perexec_avg
|
|
, (max(ss.elapsed_time_total/1000000/ss.executions_total)
|
|
- avg(ss.elapsed_time_total/1000000/ss.executions_total)
|
|
)
|
|
/ avg(ss.elapsed_time_total/1000000/ss.executions_total) elapsed_time_perexec_deviation
|
|
FROM v$database d
|
|
, dba_hist_snapshot s
|
|
, dba_hist_sqlstat ss
|
|
-- , dba_hist_sql_plan 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 p.dbid = ss.dbid (+)
|
|
-- AND p.sql_id = ss.sql_id (+)
|
|
-- AND p.plan_hash_value = ss.plan_hash_value (+)
|
|
-- AND p.id = 0 -- Top row which has cost as well
|
|
AND s.begin_interval_time BETWEEN TO_TIMESTAMP('&&from_timestamp','YYYY-MM-DD HH24:MI:SS')
|
|
AND TO_TIMESTAMP('&&to_timestamp','YYYY-MM-DD HH24:MI:SS')
|
|
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')
|
|
AND ss.force_matching_signature <> 0
|
|
AND ss.executions_delta > 0
|
|
AND ss.executions_total > 0
|
|
AND ss.elapsed_time_total > 0
|
|
GROUP BY ss.force_matching_signature
|
|
/*
|
|
HAVING (max(ss.elapsed_time_total/1000000/ss.executions_total)
|
|
- avg(ss.elapsed_time_total/1000000/ss.executions_total)
|
|
)
|
|
/ avg(ss.elapsed_time_total/1000000/ss.executions_total) > 0.1
|
|
*/
|
|
ORDER BY elapsed_time_perexec_deviation desc
|
|
)
|
|
WHERE ROWNUM <= &&TOP_RECORDCOUNT
|
|
/
|
|
|
|
UNDEFINE FROM_TIMESTAMP
|
|
UNDEFINE TO_TIMESTAMP
|
|
UNDEFINE TOP_RECORDCOUNT
|
|
|
|
@@footer
|