Description
Provides a list of SQL statements that have experienced an execution plan change within the last hour or whatever the execution interval is defined as.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: SQLs with Execution Plan Changes
Execution Interval: 1 Hour
Notification Text: The following list of SQL statements had plan changes
SQL Statement:
SELECT sql_hash_or_name, COUNT(1) FROM (
SELECT DISTINCT NVL(n.name, sw.izho) sql_hash_or_name, orph plan_hash_value
FROM consw_#DBID# sw, con_sql_name n, (
SELECT sqlhash, timesecs FROM (
SELECT sqlhash, SUM(timesecs) timesecs
FROM con_sql_sum_#DBID# ss
WHERE datehour > CURRENT_TIMESTAMP - 14
GROUP BY sqlhash
ORDER BY 2 DESC)
WHERE ROWNUM <= 50) topn
WHERE sw.izho = n.hash 
AND sw.izho = topn.sqlhash
AND sw.d >= SYSDATE - (#FREQUENCY#/1440)
AND sw.izho <> 0 AND sw.orph <> 0)
GROUP BY sql_hash_or_name
HAVING COUNT(1) > 1
Execute Against: Repository
Units: Number of plan changes
High Threshold: Min 2, Max empty
