All,
I am working on a piece of code that is capable of identifying/alerting us on plan changes on certain critical SQL ID's.. So, I have come up with the SQL below which looks at the last N snapshots to see if there are more than one PHV for a SQL_ID ( more for sharable SQL statement that use BIND ). Now given that the AWR snapshots run once every hour or 30 mins, I get alerted only after an hour if the SQL has completed executing... Is that possible to join this query with V$SQLSTAT view to see if an SQL being executed has a different PHV to that of the data from DBA_HIST_SQLSTAT ? That way, we can trigger the alert while it is executing with an incorrect PHV ?
Thanks in advance.
SELECT sql_id,
CASE
WHEN PLAN_COUNT > 1 THEN 'PLAN CHANGED'
WHEN PLAN_COUNT = 1 THEN 'PLAN INTACT'
END "PLAN STATUS"
FROM ( SELECT sql_id, COUNT (DISTINCT plan_hash_value) PLAN_COUNT
FROM (SELECT sql_id, plan_hash_value
FROM ( SELECT sql_id,
plan_hash_value,
NVL (executions_delta, 0) execs
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
WHERE sql_id IN ('0ctk7jpux5chm',
'4v6g0qh2w6t3j',
'f9j5wwwq4pfwq',
'90514pb8dbkmk',
'afcswub17n34t',
'7fgbycbuvbxp5',
'aqu52j8048h94')
AND ss.snap_id = S.snap_id
AND TRUNC (begin_interval_time) >
TRUNC (SYSDATE - 8)
AND ss.instance_number = S.instance_number
AND executions_delta > 0
ORDER BY 1, 2, 3))
GROUP BY sql_id);
SQL_ID PLAN STATUS
------------- ------------
7fgbycbuvbxp5 PLAN CHANGED
aqu52j8048h94 PLAN CHANGED
4v6g0qh2w6t3j PLAN CHANGED
0ctk7jpux5chm PLAN CHANGED
90514pb8dbkmk PLAN INTACT
afcswub17n34t PLAN CHANGED
f9j5wwwq4pfwq PLAN CHANGED