Want to  get alerted whenever there is  change  in execution plan?  Try the below query.

COLUMN SQL_TEXT FORMAT A100
SET LINESIZE 150 PAGESIZE 2000
SET LONG 300000

SELECT DISTINCT A.SQL_ID ,A.PLAN_HASH_VALUE , B.SQL_TEXT FROM V$SQL_PLAN A, V$SQL B
WHERE A.SQL_ID IN (
SELECT SQL_ID  FROM V$SQL_PLAN
WHERE OBJECT_OWNER=’SCOTT‘
AND TIMESTAMP>SYSDATE-1
GROUP BY SQL_ID
HAVING COUNT (DISTINCT PLAN_HASH_VALUE) >1)
AND A.SQL_ID=B.SQL_ID

You can choose to run the query every hour or  once every day depending on the sensitivity of your applications to plan changes. It is good to change the TIMESTAMP”  accordingly  in the query. You can also ORDER BY SQL_ID if required.

Tagged: applications, change in execution plan, detect execution plan changes, execution plan, find changes in execution plan, sql, V$SQL_PLAN