The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 242 other followers

Flushing a cursor out of the Library Cache

Posted by Harald van Breederode on April 19, 2009

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.

SQL> create table skew(c1 number(6), c2 char(20));

Table created.

SQL> insert into skew select 1,1 from dual connect by level <= 10000;

10000 rows created.

SQL> update skew set c1 = 2 where rownum <= 10;

10 rows updated.

SQL> create index skew_idx on skew(c1);

Index created.

SQL> exec dbms_stats.gather_table_stats(null,'skew', -
> method_opt => 'for all columns size 2')

PL/SQL procedure successfully completed.

SQL> select c1, count(*) from skew group by c1;

        C1   COUNT(*)
---------- ----------
         1       9990
         2         10

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.

SQL> var x number
SQL> exec :x := 1;

PL/SQL procedure successfully completed.

SQL> select count(c2) from skew where c1 = :x;

 COUNT(C2)
----------
      9990

SQL> select *
  2  from table(dbms_xplan.display_cursor(null,null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x

Plan hash value: 568322376

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|   2 |   TABLE ACCESS FULL| SKEW |  9990 |
-------------------------------------------

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:

SQL> exec :x := 2;

PL/SQL procedure successfully completed.

SQL> select count(c2) from skew where c1 = :x;

 COUNT(C2)
----------
        10

SQL> select *
  2  from table(dbms_xplan.display_cursor(null,null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x

Plan hash value: 568322376

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|   2 |   TABLE ACCESS FULL| SKEW |  9990 |
-------------------------------------------

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:

SQL> @?/rdbms/admin/dbmspool

Package created.

Grant succeeded.

View created.

Package body created.

SQL> select address, hash_value from v$sqlarea
  2  where sql_text = 'select count(c2) from skew where c1 = :x';

ADDRESS  HASH_VALUE
-------- ----------
27308318 2934790721

SQL> exec sys.dbms_shared_pool.purge('&address, &hash_value','c')

PL/SQL procedure successfully completed.

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:

SQL> select count(c2) from skew where c1 = :x;

 COUNT(C2)
----------
        10

SQL> select *
  2  from table(dbms_xplan.display_cursor(null,null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x

Plan hash value: 3493361220

---------------------------------------------------------
| Id  | Operation                    | Name     | Rows  |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |
|   1 |  SORT AGGREGATE              |          |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW     |    10 |
|   3 |    INDEX RANGE SCAN          | SKEW_IDX |    10 |
---------------------------------------------------------

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.

SQL> exec :x := 1;

PL/SQL procedure successfully completed.

SQL> select count(c2) from skew where c1 = :x;

 COUNT(C2)
----------
      9990

SQL> select *
  2  from table(dbms_xplan.display_cursor(null,null,'basic rows peeked_binds'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x

Plan hash value: 3493361220

---------------------------------------------------------
| Id  | Operation                    | Name     | Rows  |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |
|   1 |  SORT AGGREGATE              |          |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW     |    10 |
|   3 |    INDEX RANGE SCAN          | SKEW_IDX |    10 |
---------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 2

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

About these ads

22 Responses to “Flushing a cursor out of the Library Cache”

  1. Good Job.

    Excellent “dbms_shared_pool”

    But my system still use 10.2 ;) it’s seem no “dbms_shared_pool.purge” procedure to use

  2. thank you Harald!

    I got it and see it(purge procedure)on dbmspool.sql file(10.2.0.4).

    I just confused and didn’t see “purg” procedure on Oracle DOCs.

    ;)

  3. Eric Grancher said

    hello,

    thank you for the blog entry, very clear, documented and informative.

    For Surachart: you might want to read Metalink Note 751876.1 about DBMS_SHARED_POOL.PURGE on 10.2.0.4

    cheers,
    Eric

  4. Yas said

    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.

  5. coskan said

    Thank you very much for sharing this better approach (than DDL) Harald

  6. [...] http://prutser.wordpress.com/2009/04/19/flushing-a-cursor-out-of-the-library-cache/ [...]

  7. [...] On The Dutch Prutser’s Blog, Harald van Breederode demonstrates flushing a cursor out of the library cache. [...]

  8. illiyaz said

    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 Breederode said

      Hi illiyaz,

      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

  9. Roberto said

    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.”

  10. Harald van Breederode said

    Hi Roberto,

    You are absolutely correct!

    Somehow I forgot to add the following to the example:

    alter session set events '5614566 trace name context forever';
    

    Thanks for catching this.
    -Harald

  11. [...] For more comprehensive details check out Haralds original post. [...]

  12. radino said

    A saw a trick somewhere: “comment on table skew is ”;”, but it invalidates all cursors.
    but dbms_shared_pool is much appropriate, of course..

  13. JMIZE said

    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!

  14. maclean said

    but dbms_shared_pool.pure procedure can’t be utilized on 10g ,even 10.2.0.4

  15. [...] окт 20th, 2010 by yavor Цък: Днес се сблъсках с този проблем и искам да споделя решението, което открих в блога на Харалд. [...]

  16. [...] 23rd, 2011 by yavor Цък: I needed this today and I want to share the solution i found in Harald’s [...]

  17. [...] Цък: (tested on 10.2.0.4.1) I needed this today and I want to share the solution i found in Harald’s [...]

  18. VARUN said

    HI sir,

    In Oracle 11g we can remove particular sql statement from Shared Pool without flushing the entire Shared Pool.

  19. [...] נוסף (בערך מה שכתבתי, רק באנגלית) ניתן לקרוא כאן בבלוג הזה או בספרות של אורקל. Share [...]

  20. Manoj Sharma said

    Nice post .one query
    if got two value for a particluar hash_value .

    SQL> select plan_hash_value ,sql_id ,address from v$sql where hash_value=2466267467;

    PLAN_HASH_VALUE SQL_ID ADDRESS
    ————— ————- —————-
    3069096336 1ad74dk9h0hab 0000000536761350
    761792906 1ad74dk9h0hab 0000000536761350

    1st is a bad plan while 2nd is a good one. i want to remove 1st one

    exec sys.dbms_shared_pool.purge(‘&address, &hash_value’,’c’) will this remove both as both have same hash value

    • Harald van Breederode said

      Hi Manoj,

      Unfortunatly it will flush both cursors out of the library cache. I am not aware of any technique to prevent this from happening.
      -Harald

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 242 other followers

%d bloggers like this: