This content has been marked as final. Show 8 replies
Chiwatel wrote:post both EXPLAIN PLAN for fast & slow executions
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 ?
three distinct scenarios are possible here: loads, invalidations or sharing. I would start by looking in V$SQL_SHARED_CURSOR, V$SQL and DBA_HIST_SQLSTAT to determine which of the three is taking place. "Loads" means that the plan gets aged out of the shared pool more or less "naturally", either because the shared pool is not sufficiently big, or because there is a lot of other stuff in it, or because its fragmentation is causing the space to be used less efficiently and thus internal housekeeping kick in more often. "Invalidations" means that the cursor is marked invalid because of DDL or statistics updated against an object referenced in the query. There are columns called "loads" and "invalidations" in V$SQL and DBA_HIST_SQLSTAT views that may help you understand if either of the two scenarios is taking place.
The third scenario, sharing, is a bit more subtle. It could be that for example the size of one of the bind variables is changing considerably once in a while, and as a result the cursor cannot be reused. In this case, a new child cursor would be parsed, and the reason why existing cursor couldn't be shared could me in most cases found in V$SQL_SHARED_CURSOR view.
Thanks Hemant and Nikolay.
The SGA is about 2 Gigs so I guess it is big enough and there was no DDL when I tested the query.
What is strange is that there was normally no activity and the database when I run my tests this is why it was very strange to me that the plan aged out.
Anyway, I will try your suggestions and let you know !
Can you run this query and post the output here
This will show size of various pools in SGA.
select pool, sum(bytes)/(1024 * 1024) size_mb from v$sgastat group by pool ;
Many times, there is some application (written in 3GL language) which generates SQLs with hardcoded literals. So, same SQL with different values hardcoded in the SQL are executing on database e.g. select name from customer where id='ABCD' and select name from customer where id='EFGH' and select name from customer where id='IJKL' are 3 different SQLs if we don't use bind variables.
Try this query
If this query show plenty of similar looking SQLs with different SQL_IDs, these SQLs are flooding the cache with SQLs and older plans are flushed out quickly.
select sql_id, first_load_time, sql_text from v$sqlarea;
Chiwatel wrote:What are you comparing this to?
Thanks Hemant and Nikolay.
The SGA is about 2 Gigs so I guess it is big enough
and there was no DDL when I tested the query.DDL is just one of the possibilities. There's also rolling invalidation due to updates stats, which may cause the cursor to get invalidated hours after the stats changed. There could be even more complex scenarios, e.g. invalidations caused by shared pool being resized by ASMM internal mechanisms.
But before guessing, it's best to simply check statistics on loads and invalidations, it could be that you will find your answer there.
What is strange is that there was normally no activity and the database when I run my tests this is why it was very strange to me that the plan aged out.It does sound strange, but let's not forget that a database is an extremely complex system where a lot is going on behind the scene.
Anyway, I will try your suggestions and let you know !OK, good luck with that!