Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why my SQL Plans age very quickly ?

ChiwatelMar 3 2013 — edited Mar 4 2013

Hi All,

I am working on 11g R2 (Windows) and I am doing some tuning of a particular query. When I run several times the query, it is fairly quick but what I noticed is that the plan ages out very quickly. So when I run the same query 5 minutes later, it seems that Oracle parse it again and it takes much more time.

Also, When I want to see the plan in dbms_xplan.display_cursor after these 5 minutes I have the following error:

NOTE: cannot fetch plan for SQL_ID: avcd12sdzr3, CHILD_NUMBER: 1
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)

Does someone know why my plan is aging so quickly and how I can fix this ?

Many thanks.

This post has been answered by Nikolay Savvinov on Mar 4 2013

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 1 2013

Added on Mar 3 2013

#general-database-discussions

8 comments

772 views