How to keep cursors in v $ sql_plan longer

Question

I am trying to analyze a query execution plan in my Oracle database. I have installed

alter system set statistics_level = all; 

Thus, I can compare the calculated powers and times with real powers and time. Now I run this statement to display this information.

 select * from table(dbms_xplan.display_cursor( sql_id => '6dt9vvx9gmd1x', cursor_child_no => 2, FORMAT => 'ALLSTATS LAST')); 

But I keep getting this message

 NOTE: cannot fetch plan for SQL_ID: 6dt9vvx9gmd1x, CHILD_NUMBER: 2 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) 

CHILD_NUMBER was right when the request was executed. Also, when I run dbms_xplan.display_cursor at the same time as the request, I get the actual plan. But my JDBC connection closes the PreparedStatement right after execution, so that is probably why the execution plan disappears with v$sql_plan .

Am I getting something wrong, or how can I analyze the estimated / actual values ​​after execution?

Answer 1

Increase shared_pool to create more caching for cursors. If in 11g, write the sql plan in the baselines using optimizer_capture_sql_plan_baselines. This stores plans in dba_sql_plan_baselines.

Answer 2

You can always set the cursor which is new in 11g -

 dbms_shared_pool.keep ('[address, hash_value from v$open_cursor]', 'C');