Hi all,
It's quite easy to find a reasons for a plan flip if cursor exists in v$sql.
We can use v$sql_shared_cursor to check flags or/and v$sql_optimizer_env to analyze optimizer params.
But challenge becomes much more complicated if cursor no longer exists in v$sql and can be found only in DBA_HIST_* views.
It's well known that AWR views do not contain a lot of information about child cursors.
Below query can expressively confirm this
SQL> select table_name, column_name
2 from dba_tab_columns
3 where table_name like 'DBA_HIST%' and column_name like '%CHILD%';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DBA_HIST_LATCH_CHILDREN CHILD#
DBA_HIST_ACTIVE_SESS_HISTORY SQL_CHILD_NUMBER
In my case, I clearly see that there are few various sql_plan_hash_value for some particular sql_id and different children in dba_hist_active_sess_history.
I suspect that it's absolutely impossible to figure out why each child was generated (since v$sql_shared_cursor data does not go to AWR views)
but it would be great at least to check optimizer environment for each plan/child from AWR.
There is a view DBA_HIST_OPTIMIZER_ENV which could be joined with DBA_HIST_SQLSTAT
but I have no idea how to decode dba_hist_optimizer_env.optimizer_env
or get values for optimizer params from AWR view in another way.
Any help with highlighted request is highly appreciated.
Thanks