I came up with a simple query that shows a running SQL executing a different plan than what it had in the past. Here is the query:
-- show currently executing sqls that have history -- but who have never run with the current plan -- joins v$session to v$sql to get plan_hash_value of -- executing sql. -- queries dba_hist_sqlstat for previously used values -- of plan_hash_value. -- only reports queries that have an older plan that is -- different from the new one. select vs.sid, vs.sql_id, vs.last_call_et, sq.plan_hash_value from v$session vs, v$sql sq where vs.sql_id=sq.sql_id and vs.SQL_CHILD_NUMBER=sq.child_number and sq.plan_hash_value not in (select ss.plan_hash_value from dba_hist_sqlstat ss where ss.sql_id=sq.sql_id) and 0 < (select count(ss.plan_hash_value) from dba_hist_sqlstat ss where ss.sql_id=sq.sql_id);
SID SQL_ID LAST_CALL_ET PLAN_HASH_VALUE ---------- ------------- ------------ --------------- 229 cq8bhsxbbf9k7 0 3467505462
This was a test query. I ran it a bunch of times with an index and then dropped the index after creating an AWR snapshot. The query executed with a different plan when I ran it without the index. The same type of plan change could happen in production if an index were accidentally dropped.
I’m hoping to use this query to show production queries that have run in the past but whose current plan differs from any that they have used before. Of course, a new plan doesn’t necessarily mean you have a problem but it might be helpful to recognize those plans that are new and that differ from the plans used in the past.