unstable plans
set lines 200 pages 9999
col execs for 999,999,999
col min_etime for 999,999.99
col max_etime for 999,999.99
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col norm_stddev for 999,999.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select * from (
select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
from (
select sql_id, plan_hash_value, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
(sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
group by sql_id, plan_hash_value
)
)
group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('&min_stddev'),2)
and max_etime > nvl(to_number('&min_etime'),.1)
order by norm_stddev
/
-- whats_changed_c.sql
DEF days_of_history_accessed = '5';
DEF captured_at_least_x_times = '1';
DEF captured_at_least_x_days_apart = '5';
DEF med_elap_microsecs_threshold = '1e4';
DEF min_slope_threshold = '0.1';
DEF max_num_rows = '20';
SET lin 300 ver OFF;
set linesize 500
COL row_n format A2 HEA '#';
COL change format A13 justify left;
COL slope format 9999.99 HEA 'SLOPE' justify right;
COL pctdbtim format 99.99 HEA 'Total Elapsed|% DB Time' justify right;
COL med_secs_per_exec format a15 HEA 'Median Secs|Per Exec' justify right;
COL std_secs_per_exec format a15 HEA 'Std Dev Secs|Per Exec' justify right;
COL avg_secs_per_exec format a15 HEA 'Avg Secs|Per Exec' justify right;
COL min_secs_per_exec format a15 HEA 'Min Secs|Per Exec' justify right;
COL max_secs_per_exec format a15 HEA 'Max Secs|Per Exec' justify right;
COL plans format 9999 justify right;
COL sql_text_80 format A80 justify left;
PRO SQL Statements with "Elapsed Time per Execution" changing over time
WITH
per_time AS
(
SELECT
h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE) days_ago,
SUM(h.elapsed_time_delta) / SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) time_per_exec,
--SUM(h.elapsed_time_delta) time_per_exec,
SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) execs
FROM
dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
AND h.parsing_schema_name NOT IN ('SYS','SYSTEM','SYSTEM2','DBSNMP')
AND h.executions_delta > 0
AND CAST(s.end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
GROUP BY
h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE)
HAVING
SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) >= 1
)
,
db_time AS
(
SELECT
tdbtim
FROM
(
(
SELECT
e.stat_name ,
(e.value - NVL(b.value,0)) value
FROM
dba_hist_sys_time_model b ,
dba_hist_sys_time_model e
WHERE
e.dbid = b.dbid
AND e.instance_number = b.instance_number
AND e.snap_id =
(
SELECT
MAX(snap_id)
FROM
dba_hist_snapshot
WHERE
CAST(end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
)
AND b.snap_id =
(
SELECT
MIN(snap_id)
FROM
dba_hist_snapshot
WHERE
CAST(end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
)
AND b.stat_id = e.stat_id
AND e.stat_name IN ('DB time','DB CPU',
'background elapsed time','background cpu time')
)
pivot (SUM(value) FOR stat_name IN ('DB time' tdbtim)) )
)
,
sql_dbt AS
(
SELECT
h.sql_id,
ROUND(h.time_per_exec*100/d.tdbtim,2) pctdbtim
FROM
(
SELECT
sql_id,
SUM(time_per_exec*execs) time_per_exec
FROM
per_time
GROUP BY
sql_id
)
h,
db_time d
)
,
avg_time AS
(
SELECT
dbid,
sql_id,
MEDIAN(time_per_exec) med_time_per_exec,
STDDEV(time_per_exec) std_time_per_exec,
AVG(time_per_exec) avg_time_per_exec,
MIN(time_per_exec) min_time_per_exec,
MAX(time_per_exec) max_time_per_exec
FROM
per_time
GROUP BY
dbid,
sql_id
HAVING
COUNT(*) >= &&captured_at_least_x_times.
--AND MAX(days_ago) - MIN(days_ago) >=
-- &&captured_at_least_x_days_apart.
AND MEDIAN(time_per_exec) > &&med_elap_microsecs_threshold.
)
,
time_over_median AS
(
SELECT
h.dbid,
h.sql_id,
h.days_ago,
(h.time_per_exec / a.med_time_per_exec) time_per_exec_over_med,
a.med_time_per_exec,
a.std_time_per_exec,
a.avg_time_per_exec,
a.min_time_per_exec,
a.max_time_per_exec
FROM
per_time h,
avg_time a
WHERE
a.sql_id = h.sql_id
)
,
ranked AS
(
SELECT
RANK () OVER (ORDER BY ABS(REGR_SLOPE(t.time_per_exec_over_med,t.days_ago)) DESC) rank_num,
t.dbid,
t.sql_id,
CASE
WHEN REGR_SLOPE(t.time_per_exec_over_med, t.days_ago) > 0
THEN 'IMPROVING'
ELSE 'REGRESSING'
END change,
ROUND(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago), 3)
slope,
ROUND(AVG(t.med_time_per_exec)/1e6, 3) med_secs_per_exec,
ROUND(AVG(t.std_time_per_exec)/1e6, 3) std_secs_per_exec,
ROUND(AVG(t.avg_time_per_exec)/1e6, 3) avg_secs_per_exec,
ROUND(MIN(t.min_time_per_exec)/1e6, 3) min_secs_per_exec,
ROUND(MAX(t.max_time_per_exec)/1e6, 3) max_secs_per_exec
FROM
time_over_median t
GROUP BY
t.dbid,
t.sql_id
HAVING
ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) > &&min_slope_threshold.
)
SELECT
row_n,
r.sql_id,
change,
slope,
s.pctdbtim,
lpad(med_secs_per_exec,15,' ') med_secs_per_exec,
lpad(std_secs_per_exec,15,' ') std_secs_per_exec,
lpad(avg_secs_per_exec,15,' ') avg_secs_per_exec,
lpad(min_secs_per_exec,15,' ') min_secs_per_exec,
lpad(max_secs_per_exec,15,' ') max_secs_per_exec,
plans,
sql_text_80
FROM
(
SELECT
LPAD(ROWNUM, 2) row_n,
r.sql_id,
r.change,
--TO_CHAR(r.slope, '990.000MI') slope,
ROUND(r.slope,2) slope,
--TO_CHAR(s.pctdbtim,'99.9999') ela_pct_dbtime,
TO_CHAR(r.med_secs_per_exec, '999,990.000') med_secs_per_exec,
TO_CHAR(r.std_secs_per_exec, '999,990.000') std_secs_per_exec,
TO_CHAR(r.avg_secs_per_exec, '999,990.000') avg_secs_per_exec,
TO_CHAR(r.min_secs_per_exec, '999,990.000') min_secs_per_exec,
TO_CHAR(r.max_secs_per_exec, '999,990.000') max_secs_per_exec,
(
SELECT
COUNT(DISTINCT p.plan_hash_value)
FROM
dba_hist_sql_plan p
WHERE
p.dbid = r.dbid
AND p.sql_id = r.sql_id
)
plans,
REPLACE(
(
SELECT distinct
sys.DBMS_LOB.SUBSTR(s.sql_text, 80)
FROM
dba_hist_sqltext s
WHERE
s.dbid = r.dbid
AND s.sql_id = r.sql_id
)
, CHR(10)) sql_text_80
FROM
ranked r
WHERE
r.rank_num <= &&max_num_rows.
ORDER BY
r.rank_num
)
r,
sql_dbt s
WHERE
r.sql_id = s.sql_id
ORDER BY
row_n
/
Total Elapsed Median Secs Std Dev Secs Avg Secs Min Secs Max Secs
# SQL_ID CHANGE SLOPE % DB Time Per Exec Per Exec Per Exec Per Exec Per Exec PLANS SQL_TEXT_80
-- ------------- ------------- -------- ------------- --------------- --------------- --------------- --------------- --------------- ----- --------------------------------------------------------------------------------
1 00nxwgnnnhd9z REGRESSING -.62 .15 151.788 140.202 222.579 131.888 384.062 2 SELECT AUDITSIGNATURE,SEQUENCEGENERATORPOOLNAME,SEQUENCEGENERATORID,SEQUENCENUMB
2 02jrgb8ppzbpx IMPROVING .13 .32 262.992 168.498 285.982 53.514 523.788 0 call FTRESS_FT.purge_exp_sessions ( )
COL force_matching_signature format 999999999999999999999999 HEA 'FORCE_MATCHING' justify right;
WITH
per_time AS
(
SELECT
h.dbid,
--h.sql_id,
h.force_matching_signature,
SYSDATE - CAST(s.end_interval_time AS DATE) days_ago,
SUM(h.elapsed_time_delta) / SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) time_per_exec,
--SUM(h.elapsed_time_delta) time_per_exec,
SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) execs
FROM
dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE
h.executions_delta > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
AND h.parsing_schema_name NOT IN ('SYS','SYSTEM','SYSTEM2', 'DBSNMP')
AND CAST(s.end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
GROUP BY
h.dbid,
--h.sql_id,
h.force_matching_signature,
SYSDATE - CAST(s.end_interval_time AS DATE)
HAVING
SUM(decode(h.executions_delta,0,0.00000001,h.executions_delta)) >= 1
)
,
db_time AS
(
SELECT
tdbtim,
tdbcpu,
tbgtim,
tbgcpu
FROM
(
(
SELECT
e.stat_name ,
(e.value - NVL(b.value,0)) value
FROM
dba_hist_sys_time_model b ,
dba_hist_sys_time_model e
WHERE
e.dbid = b.dbid
AND e.instance_number = b.instance_number
AND e.snap_id =
(
SELECT
MAX(snap_id)
FROM
dba_hist_snapshot
WHERE
CAST(end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
)
AND b.snap_id =
(
SELECT
MIN(snap_id)
FROM
dba_hist_snapshot
WHERE
CAST(end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
)
AND b.stat_id = e.stat_id
AND e.stat_name IN ('DB time','DB CPU' ,'background elapsed time','background cpu time')
)
pivot (SUM(value) FOR stat_name IN ('DB time' tdbtim ,'DB CPU' tdbcpu ,'background elapsed time' tbgtim ,'background cpu time' tbgcpu)))
)
,
sql_dbt AS
(
SELECT
--h.sql_id,
h.force_matching_signature,
ROUND(h.time_per_exec*100/d.tdbtim,2) pctdbtim
FROM
(
SELECT
--sql_id,
force_matching_signature,
SUM(time_per_exec*execs) time_per_exec
FROM
per_time
GROUP BY
--sql_id
force_matching_signature
)
h,
db_time d
)
,
avg_time AS
(
SELECT
dbid,
--sql_id,
force_matching_signature,
MEDIAN(time_per_exec) med_time_per_exec,
STDDEV(time_per_exec) std_time_per_exec,
AVG(time_per_exec) avg_time_per_exec,
MIN(time_per_exec) min_time_per_exec,
MAX(time_per_exec) max_time_per_exec
FROM
per_time
GROUP BY
dbid,
--sql_id
force_matching_signature
HAVING
COUNT(*) >= &&captured_at_least_x_times.
--AND MAX(days_ago) - MIN(days_ago) >=
-- &&captured_at_least_x_days_apart.
AND MEDIAN(time_per_exec) > &&med_elap_microsecs_threshold.
)
,
time_over_median AS
(
SELECT
h.dbid,
--h.sql_id,
h.force_matching_signature,
h.days_ago,
(h.time_per_exec / a.med_time_per_exec) time_per_exec_over_med,
a.med_time_per_exec,
a.std_time_per_exec,
a.avg_time_per_exec,
a.min_time_per_exec,
a.max_time_per_exec
FROM
per_time h,
avg_time a
--WHERE a.sql_id = h.sql_id
WHERE
a.force_matching_signature = h.force_matching_signature
)
,
ranked AS
(
SELECT
RANK () OVER (ORDER BY ABS(REGR_SLOPE(t.time_per_exec_over_med,
t.days_ago)) DESC) rank_num,
t.dbid,
--t.sql_id,
t.force_matching_signature,
CASE
WHEN REGR_SLOPE(t.time_per_exec_over_med, t.days_ago) > 0
THEN 'IMPROVING'
ELSE 'REGRESSING'
END change,
ROUND(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago), 3)
slope,
ROUND(AVG(t.med_time_per_exec)/1e6, 3) med_secs_per_exec,
ROUND(AVG(t.std_time_per_exec)/1e6, 3) std_secs_per_exec,
ROUND(AVG(t.avg_time_per_exec)/1e6, 3) avg_secs_per_exec,
ROUND(MIN(t.min_time_per_exec)/1e6, 3) min_secs_per_exec,
ROUND(MAX(t.max_time_per_exec)/1e6, 3) max_secs_per_exec
FROM
time_over_median t
GROUP BY
t.dbid,
--t.sql_id
t.force_matching_signature
HAVING
ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) > &&min_slope_threshold.
)
SELECT
row_n,
--r.sql_id,
r.force_matching_signature,
change,
slope,
s.pctdbtim,
lpad(med_secs_per_exec,15,' ') med_secs_per_exec,
lpad(std_secs_per_exec,15,' ') std_secs_per_exec,
lpad(avg_secs_per_exec,15,' ') avg_secs_per_exec,
lpad(min_secs_per_exec,15,' ') min_secs_per_exec,
lpad(max_secs_per_exec,15,' ') max_secs_per_exec
-- plans,
--sql_text_80
FROM
(
SELECT
LPAD(ROWNUM, 2) row_n,
--r.sql_id,
r.force_matching_signature,
r.change,
--TO_CHAR(r.slope, '990.000MI') slope,
ROUND(r.slope,2) slope,
--TO_CHAR(s.pctdbtim,'99.9999') ela_pct_dbtime,
TO_CHAR(r.med_secs_per_exec, '999,990.000') med_secs_per_exec,
TO_CHAR(r.std_secs_per_exec, '999,990.000') std_secs_per_exec,
TO_CHAR(r.avg_secs_per_exec, '999,990.000') avg_secs_per_exec,
TO_CHAR(r.min_secs_per_exec, '999,990.000') min_secs_per_exec,
TO_CHAR(r.max_secs_per_exec, '999,990.000') max_secs_per_exec
--(SELECT COUNT(DISTINCT p.plan_hash_value) FROM
-- dba_hist_sql_plan p WHERE p.dbid = r.dbid AND p.sql_id =
-- r.sql_id) plans,
--REPLACE((SELECT sys.DBMS_LOB.SUBSTR(s.sql_text, 80) FROM
-- dba_hist_sqltext s WHERE s.dbid = r.dbid AND s.sql_id =
-- r.sql_id), CHR(10)) sql_text_80
FROM
ranked r
WHERE
r.rank_num <= &&max_num_rows.
ORDER BY
r.rank_num
)
r,
sql_dbt s
WHERE
r.force_matching_signature = s.force_matching_signature
ORDER BY
row_n
/
Total Elapsed Median Secs Std Dev Secs Avg Secs Min Secs Max Secs
# FORCE_MATCHING CHANGE SLOPE % DB Time Per Exec Per Exec Per Exec Per Exec Per Exec
-- ------------------------- ------------- -------- ------------- --------------- --------------- --------------- --------------- ---------------
1 15936051349132279638 REGRESSING -.62 .15 151.788 140.202 222.579 131.888 384.062
SQL>
min_elapsed_time
define min_elapsed_time=10
define min_repeat_executions_filter=10
col 1 FOR 99999
col 2 FOR 99999
col 3 FOR 9999
col 4 FOR 999
col 5 FOR 99
col av FOR 99999
col ct FOR 99999
col mn FOR 999
col av FOR 99999.9
col MAX_RUN_TIME FOR a40
col longest_sql_exec_id FOR A20
set linesize 500
set pages 9999
WITH pivot_data AS
(SELECT sql_id,
ct,
mxdelta mx,
mndelta mn,
ROUND(avdelta) av,
WIDTH_BUCKET(delta_in_seconds,mndelta,mxdelta+.1,5) AS bucket ,
SUBSTR(times,12) max_run_time,
SUBSTR(longest_sql_exec_id, 12) longest_sql_exec_id
FROM
(SELECT sql_id,
delta_in_seconds,
COUNT(*) OVER (PARTITION BY sql_id) ct,
MAX(delta_in_seconds) OVER (PARTITION BY sql_id) mxdelta,
MIN(delta_in_seconds) OVER (PARTITION BY sql_id) mndelta,
AVG(delta_in_seconds) OVER (PARTITION BY sql_id) avdelta,
MAX(times) OVER (PARTITION BY sql_id) times,
MAX(longest_sql_exec_id) OVER (PARTITION BY sql_id) longest_sql_exec_id
FROM
(SELECT sql_id,
sql_exec_id,
MAX(delta_in_seconds) delta_in_seconds ,
LPAD(ROUND(MAX(delta_in_seconds),0),10)
|| ' '
|| TO_CHAR(MIN(start_time),'YY-MM-DD HH24:MI:SS')
|| ' '
|| TO_CHAR(MAX(end_time),'YY-MM-DD HH24:MI:SS') times,
LPAD(ROUND(MAX(delta_in_seconds),0),10)
|| ' '
|| TO_CHAR(MAX(sql_exec_id)) longest_sql_exec_id
FROM
( SELECT sql_id, TO_CHAR(sql_exec_id)||'_'||to_char(sql_exec_start,'J') sql_exec_id,
CAST(sample_time AS DATE) end_time,
CAST(sql_exec_start AS DATE) start_time,
((CAST(sample_time AS DATE)) - (CAST(sql_exec_start AS DATE))) * (3600*24) delta_in_seconds
FROM dba_hist_active_sess_history
WHERE sql_exec_id IS NOT NULL
AND sql_id='00nxwgnnnhd9z'
)
GROUP BY sql_id,
sql_exec_id
)
)
WHERE ct >
&min_repeat_executions_filter
AND mxdelta >
&min_elapsed_time )
SELECT *
FROM pivot_data PIVOT ( COUNT(*) FOR bucket IN (1,2,3,4,5))
ORDER BY mx DESC,
av DESC ;
5
------------- ------ ---------- ---- -------- ---------------------------------------- -------------------- ------ ------ ----- ---- ---
00nxwgnnnhd9z 43 1123 411 995.0 24-07-30 16:32:52 24-07-30 16:51:35 16777217_2460522 2 1 0 3 37
SQL>
from https://blog.go-faster.co.uk/2019/10/purging-sql-statements-and-execution.html
set lines 300 pages 99
col costpctdiff heading 'Cost|%Diff' format 99999
col costdiff heading 'Cost|Diff' format 99999999
col plan_hash_value heading 'SQL Plan|Hash Value'
col child_number heading 'Child|No.' format 9999
col inst_id heading 'Inst|ID' format 999
col hcost heading 'AWR|Cost' format 99999999999
col ccost heading 'Cursor|Cost' format 9999999
col htimestamp heading 'AWR|Timestamp'
col ctimestamp heading 'Cursor|Timestamp'
col end_interval_time format a26
col snap_id heading 'Snap|ID' format 99999999
col awr_cost format 9999999999999
col optimizer_Cost heading 'Opt.|Cost' format 99999999
col optimizer_env_hash_value heading 'Opt. Env.|Hash Value'
col num_stats heading 'Num|Stats' format 9999
alter session set nls_date_format = 'hh24:mi:ss dd.mm.yy';
break on plan_hash_value skip 1 on sql_id on dbid
ttitle 'compare AWR/recent plan costs'
with h as ( /*captured plan outside retention limit*/
select p.dbid, p.sql_id, p.plan_hash_Value, max(cost) cost
, max(p.timestamp) timestamp
from dba_hist_sql_plan p
, dba_hist_wr_control c
where p.dbid = c.dbid
and p.cost>0
and (p.object_owner != 'SYS' OR p.object_owner IS NULL) --omit SYS owned objects
and p.timestamp < sysdate-c.retention
group by p.dbid, p.sql_id, p.plan_hash_value
), s as ( /*SQL statistics*/
select t.dbid, t.sql_id, t.plan_hash_value, t.optimizer_env_hash_value
, t.optimizer_cost
, MIN(t.snap_id) snap_id
, MIN(s.end_interval_time) end_interval_time
, COUNT(*) num_stats
from dba_hist_snapshot s
, dba_hist_sqlstat t
where s.dbid = t.dbid
and s.snap_id = t.snap_id
and t.optimizer_cost > 0
GROUP BY t.dbid, t.sql_id, t.plan_hash_value, t.optimizer_env_hash_value
, t.optimizer_cost
), x as (
Select NVL(h.dbid,s.dbid) dbid
, NVL(h.sql_id,s.sql_id) sql_id
, NVL(h.plan_hash_value,s.plan_hash_value) plan_hash_value
, h.cost hcost, h.timestamp htimestamp
, s.snap_id, s.end_interval_time
, s.optimizer_env_hash_value, s.optimizer_cost
, s.num_stats
, s.optimizer_cost-h.cost costdiff
, 100*s.optimizer_cost/NULLIF(h.cost,0) costpctdiff
From h join s
on h.plan_hash_value = s.plan_hash_value
and h.sql_id = s.sql_id
and h.dbid = s.dbid
), y as (
SELECT x.*
, MAX(ABS(costpctdiff)) OVER (PARTITION BY dbid, sql_id, plan_hash_value) maxcostpctdiff
, MAX(ABS(costdiff)) OVER (PARTITION BY dbid, sql_id, plan_hash_value) maxcostabsdiff
FROM x
)
SELECT dbid, sql_id, plan_hash_value, hcost, htimestamp
, snap_id, end_interval_time, optimizer_env_hash_value, optimizer_cost, num_stats, costdiff, costpctdiff
FROM y
WHERE maxcostpctdiff>=10
And maxcostabsdiff>=10
order by plan_hash_value,sql_id,end_interval_time
/
break on report
ttitle off
set linesize 500 pagesize 400
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
exec select max(snap_id) -100 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
col SQL_TEXT for a50
WITH
per_phv AS (
SELECT
h.dbid,
h.sql_id,
h.plan_hash_value,
MIN(s.begin_interval_time) min_time,
MAX(s.end_interval_time) max_time,
MEDIAN(h.elapsed_time_total / h.executions_total) med_time_per_exec,
STDDEV(h.elapsed_time_total / h.executions_total) std_time_per_exec,
AVG(h.elapsed_time_total / h.executions_total) avg_time_per_exec,
MIN(h.elapsed_time_total / h.executions_total) min_time_per_exec,
MAX(h.elapsed_time_total / h.executions_total) max_time_per_exec,
STDDEV(h.elapsed_time_total / h.executions_total) / AVG(h.elapsed_time_total / h.executions_total) std_dev,
MAX(h.executions_total) executions_total,
MEDIAN(h.elapsed_time_total / h.executions_total) * MAX(h.executions_total) total_elapsed_time
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.snap_id BETWEEN :BgnSnap AND :EndSnap
-- AND h.dbid = dbid
AND h.executions_total > 1
AND h.plan_hash_value > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
AND CAST(s.end_interval_time AS DATE) > SYSDATE - 7
and PARSING_SCHEMA_NAME='ANUJ'
GROUP BY
h.dbid,
h.sql_id,
h.plan_hash_value
),
ranked1 AS (
SELECT
RANK () OVER (ORDER BY STDDEV(med_time_per_exec)/AVG(med_time_per_exec) DESC) rank_num1,
dbid,
sql_id,
COUNT(*) plans,
SUM(total_elapsed_time) total_elapsed_time,
MIN(med_time_per_exec) min_med_time_per_exec,
MAX(med_time_per_exec) max_med_time_per_exec
FROM per_phv
GROUP BY
dbid,
sql_id
HAVING COUNT(*) > 1
),
ranked2 AS (
SELECT
RANK () OVER (ORDER BY r.total_elapsed_time DESC) rank_num2,
r.rank_num1,
r.sql_id,
r.plans,
p.plan_hash_value,
TO_CHAR(CAST(p.min_time AS DATE), 'YYYY-MM-DD/HH24') min_time,
TO_CHAR(CAST(p.max_time AS DATE), 'YYYY-MM-DD/HH24') max_time,
ROUND(p.med_time_per_exec / 1e6, 3) med_secs_per_exec,
p.executions_total executions,
ROUND(p.med_time_per_exec * p.executions_total / 1e6, 3) aprox_tot_secs,
ROUND(p.std_time_per_exec / 1e6, 3) std_secs_per_exec,
ROUND(p.avg_time_per_exec / 1e6, 3) avg_secs_per_exec,
ROUND(p.min_time_per_exec / 1e6, 3) min_secs_per_exec,
ROUND(p.max_time_per_exec / 1e6, 3) max_secs_per_exec
--, REPLACE(DBMS_LOB.SUBSTR(s.sql_text, 1000), CHR(10)) sql_text
FROM ranked1 r,
per_phv p,
dba_hist_sqltext s
WHERE r.rank_num1 <= 20 * 5
AND p.dbid = r.dbid
AND p.sql_id = r.sql_id
AND s.dbid(+) = r.dbid AND s.sql_id(+) = r.sql_id
)
SELECT
r.sql_id,
r.plans,
r.plan_hash_value,
r.min_time,
r.max_time,
r.med_secs_per_exec,
r.executions,
r.aprox_tot_secs,
r.std_secs_per_exec,
r.avg_secs_per_exec,
r.min_secs_per_exec,
r.max_secs_per_exec
-- ,r.sql_text
FROM ranked2 r
WHERE rank_num2 <= 20
ORDER BY
r.rank_num2,
r.sql_id,
r.min_time,
r.plan_hash_value;
set linesize 300
define sql_id='xxxxxxx' ;
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE 1=1
and sql_id = TRIM('&sql_id')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE 1=1
and sql_id = TRIM('&sql_id')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE 1=1
and sql_id = TRIM('&sql_id')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE 1=1
and sql_id = TRIM('&sql_id')
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT distinct p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs, d.SQL_ID, trunc(d.TIMESTAMP) as DATE_CREATED
FROM p, m, a, dba_hist_sql_plan d
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
AND p.plan_hash_value = d.plan_hash_value
ORDER BY
SQL_ID asc, DATE_CREATED NULLS LAST;
