Flushing a cursor out of the Library Cache

Troubleshooting performance problems is an art by itself, especially when the problem is transient and only shows up once in a while. A classic example of such a problem is an unlucky peeked bind variable causing a sub-optimal execution plan based on the first execution of a SQL statement containing a bind variable on a skewed column. The problem is twofold: first to find such a statement and second to make the problem to go away. The latter is what this posting is all about. In order to demonstrate the problem and the fix we need to have something to play around with.

We now have an indexed table with skewed data in it with current object statistics in place including a histogram on the skewed column. Lets execute a query using a bind variable on the skewed column and see what the query optimizer expects and what execution plan it considers optimal.

The example above shows that the query optimizer predicted the cardinality correctly and choosed the optimal execution plan based upon this information. It could do so because there is a histogram available to describe the data skew in the table. Now see what happens if we bind the value 2 and execute the query again:

Because the statement is not hard parsed again the same execution plan is used based on the prediction of 9990 rows. Because the query only returns 10 rows this execution plan is no longer optimal for the given value for the bind variable. If this query gets executed many times with this value of the bind variable we do have a performance problem for as long as this execution plan remains in the library cache. If this is indeed the case it might be beneficial to flush this cursor out of the shared pool. Starting with 10.2.0.4.0 this can be done using the PURGE procedure in the DBMS_SHARED_POOL package as demonstrated below:

Because the DBMS_SHARED_POOL package is not installed at database creation time, it has to be installed manually as shown above. The PURGE procedure needs the ADDRESS and HASH_VALUE of the cursor being flushed and the flag ‘C’ to indicate that we are flushing a cursor. This knowledge comes out of the dbmspool.sql script. The ADDRESS and HASH_VALUE can be retrieved from V$SQLAREA as shown in the example. A successful execution of the PURGE procedure indicates that the parent cursor is gone among with its children. A next execution of the query will force a hard parse and the creation of a new execution plan as we can see below:

This time the query optimizer predicted the correct number of rows for the given value of the bind variable and selected the optimal execution plan for the given situation. The difficulty is of course to detect these situations before we can correct them. An indication could be a difference in the predicted number of rows and the actual number of rows in an execution plan, but therefore we need to set the STATISTICS_LEVEL parameter to ALL or add the GATHER_PLAN_STATISTICS hint to all possible affected statements which might be difficult to do. Once a possible affected statement has been found we can see the used bind value in the execution plan by using the PEEKED_BINDS options in the format specifier in the call to DBMS_XPLAN.

In this final example we bounded the value 1 again and executed the query which retrieved 9990 rows whilst the execution plan shows a prediction of only 10 rows. By using PEEKED_BINDS we receive extra information from DBMS_XPLAN telling us that this particular execution plan is based on the value 2 of the first bind variable in the statement which is named ‘:x’ and is a number data type.

Conclusion: By using the PURGE procedure in the DBMS_SHARED_POOL package we can flush a cursor out of the Library Cache when the execution plan causes performance problems due to an unlucky bind variable value. However this is only a temporary solution. The definitive solution is Adaptive Cursor Sharing which is introduced in Oracle11g.-Harald

Like this:

LikeLoading...

Related

This entry was posted on April 19, 2009 at 15:15 and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

Thanks Harald. I did not know that there was a way to do this in 10g. I used to run a ddl command like grant or revoke on the table to invalidate the sql so that it is parsed again. But that invalidates not just one sql but all the sqls related to the same table.

illiyazsaid

Good Article…one small question…is there any advantage of using this purge option instead of Alter system flush shared_pool apart from prevention of flushing of entire shared pool ? Do both the things do the same job ??

Harald van Breederodesaid

Thanks for your comment.
No, I do not think that there is a difference in both methods when you only look at one cursor. Both methods will flush the cursor out of the library cache. However the scope is quite different. Flushing the entire shared pool is a very drastic thing to do with many consequences. I would not advise this over purging just one cursor. Even when you are on an older release I would choose something else like a dummy DDL for example.
-Harald

Robertosaid

I would stress that, as reported in metalink note 751876.1:
“The fix has gone into 10.2.0.4. However, the fix is event protected. You need to set the event 5614566, to make use of purge. Unless the event is set, purge will have no effect.”

JMIZEsaid

Great post! I have been looking to use the .purge to remove the sql generating a bad plan due to bind peeking. I am working a production issue now but am going through the motions for on test. When I load dbmspool.sql from 11g, the dbms_shared_pool package creates but the pbod becomes invalid. Have you (or others?) experienced this? I was able to reproduce on HPUX and Linux (OEL5). I have also asked Kerry Osbourne on his blog. Thanks for any insight!