You Asked
Hi Tom,
I need to figure out the SQL's with multiple execution plans and when did the plan got changed also.
I queried the V$SQL table, please find the query below.
select * from v$sql where sql_id in( select sql_id from v$sql group by sql_id having count(plan_hash_value)>1) order by sql_id;
But my query is not showing when did the plan got changed.
Please suggest is there any other better way to find the results.
Thanks,
Kods
and Tom said...
You can use DBA_HIST_SQLSTAT (sql_id and plan_hash_value, coupled with a snap_id that can be converted into a "time frame").
And then pick up the associated plan from DBA_HIST_SQL_PLAN.
v$ table have a "here and now" value, they show what is in existence as of this exact point in time. They specifically do not contain a 'history', that is what the DBA_HIST_* views contain.
Note that in order to utilize the AWR repository (the dba_hist tables for example) you do need the diagnostic pack.
http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC167