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
GROUP BY SQL_ID
HAVING COUNT (DISTINCT PLAN_HASH_VALUE) >1)
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.