Hello All,
I have a situation to provide the list of execution plan's for a particular SQL_ID. The particular SQL flips between 2 access paths (FTS and Index Scans) but at that point of time, I can see only one (current) plan_hash_value for the statement. i.e - the previous cursor gets invalidated ( due to some application package that runs statistics which in turn invalidates the previous cursor - This is my guess).
I can get it from dba_hist_sqlstat for the particular SQL, but curious to know is that possible to get it from dynamic views like V$SQL and V$SQLAREA to list all plan_hash_values for a particular SQL, where the child_number is 0.
Any idea please?
Note: I am interested in fetching child cursors due to different execution plan's and not child cursors created due to different settings for the same plan(e.g-optimizer mode etc..)