How to keep cursors in v$sql_plan alive longer

Question

I'm trying to analyse a query execution plan in my Oracle database. I have set

alter system set statistics_level = all;

Such that I can compare estimated cardinalities and times with actual cardinalities and times. Now, I'm running this statement in order to display that 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)

The CHILD_NUMBER was correct when the query was being executed. Also, when I run dbms_xplan.display_cursor at the same time as the query, I get the actual plan. But my JDBC connection closes the PreparedStatement immediately after execution, so maybe that's why the execution plan disappears from v$sql_plan.

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

score 5 · Answer 1 · answered Apr 15 '11 at 15:14

5

You could always pin the cursor, which is new in 11g -

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

answered Apr 15 '11 at 15:14

Adam Musch

13,286
2
28
32

Very nice, I didn't know that. And I guess you'll have to manually throw it out again using `dbms_shared_pool.purge` ? – Lukas Eder Apr 15 '11 at 15:18
Yup. Or shut down the instance. It won't repin it automatically. – Adam Musch Apr 15 '11 at 15:20
Ah, I guess I'll prefer the `purge` rather than shutting it down :) – Lukas Eder Apr 15 '11 at 15:26

score 4 · Accepted Answer · answered Apr 13 '11 at 07:36

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

How to keep cursors in v$sql_plan alive longer

2 Answers2