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
 

Description

Provides a list of SQL statements that have experienced an execution plan change in the last hour.