Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

How to flush one Cursor out of the Shared Pool

with 9 comments

During my 2-days SQL Tuning session at the NoCOUG last week, someone asked me how to flush just one Cursor out of the Shared Pool. We were playing with Adaptive Cursor Sharing (ACS) and between tests we were simply flushing the entire Shared Pool. On 11g there is an API that allows us to flush just one Cursor. I wrote the small script flush_cursor.sql below, and I also placed it inside SQLTXPLAIN (SQLT) under directory sqlt/utl. Feel free to use it and share as needed:

REM Flushes one cursor out of the shared pool. Works on 11g+
REM To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.
REM The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs.
REM These scripts are not run by as part of standard database creation.
SPO flush_cursor_&&sql_id..txt;
PRO *** before flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
BEGIN
 FOR i IN (SELECT address, hash_value
 FROM gv$sqlarea WHERE sql_id = '&&sql_id.')
 LOOP
 SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
 END LOOP;
END;
/
PRO *** after flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
UNDEF sql_id;
SPO OFF;

Written by Carlos Sierra

February 28, 2013 at 2:21 pm

9 Responses

Subscribe to comments with RSS.

  1. for pre 10.2.0.4 you can also use flush_sql10.sql see http://kerryosborne.oracle-guy.com/2008/12/flush-a-single-sql-statement-take-2/ from Kerry Osborne

    bdrouvot

    March 1, 2013 at 6:49 am

  2. For pre 10.2.0.4 you can use flush_sql10.sql from Kerry Osborne see: http://kerryosborne.oracle-guy.com/2008/12/flush-a-single-sql-statement-take-2/

    bdrouvot

    March 1, 2013 at 6:52 am

  3. Hello, just downloaded latest version on MOS and script isn’t included yet. I suppose it will be on next release?
    Thanks

    Bruno Lavoie

    March 1, 2013 at 8:53 am

    • Bruno,
      SQLT 11.4.5.5 was uploaded March 1 around 10pm. Please look for it again. It should be there now.
      Cheers — Carlos

      Carlos Sierra

      March 2, 2013 at 7:01 am

  4. Carlos,

    The code will also work if you supply a child_address from v$sql. Unfortunately this doesn’t flush just that child cursor, which is what I had hoped when I first tried it.

    It’s also worth noting that you can flush individual subheaps – and for a child cursor subheap 6 is the execution plan, so you can leave the cursor in place but get rid of the execution plan – which may save you a little time on library cache load. On the other hand it might be a little confusing because columns like executions, buffer_gets etc. will still be set on v$sql even though the plan has gone and could be replaced by a new one.

    There was a possible bug in the code when it first came out – if you tried flushing a cursor when another session had it active your session would go into a loop waiting on “cursor: pin X”. I’ve just done a quick test on 11.2.0.4 and not seen that happen – so perhaps it has been fixed.

    Jonathan Lewis

    March 12, 2014 at 11:28 am

    • Thanks Jonathan for the feedback!

      Carlos Sierra

      March 12, 2014 at 1:15 pm

      • Someone linked to this from the OTN database forum – and it wasn’t until after I’d posted that I realised that the previous was 2013, not 10 days ago ;)

        Jonathan Lewis

        March 12, 2014 at 1:28 pm

      • no worries :-)

        Carlos Sierra

        March 12, 2014 at 10:00 pm


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 1,429 other followers

%d bloggers like this: