Description

This custom Ignite alert provides a list of SQL statements that have experienced an execution plan change in the last hour.

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

Ignite alert definition