Carlos Sierra's Tools and Tips

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;

15 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

   Reply
   • Great! Thanks!

     

     Carlos Sierra

     March 1, 2013 at 7:31 am

     Reply
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

   Reply
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

   Reply
   • 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

     Reply
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

   Reply
   • Thanks Jonathan for the feedback!

     

     Carlos Sierra

     March 12, 2014 at 1:15 pm

     Reply
     • 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
5. Hi Carlos,

   Thanks for the details. One question – is there way can we clear only bad execution plan. currently its clearing whole object from shared pool via hash_value. this hash_value common for both good & bad plan. looking for option to clear only bad execution plan.

   Please suggest.

   Thanks
   Yousuf,

   

   Yousuf

   December 3, 2015 at 12:48 am

   Reply
   • Yousuf,

     Before clearing the SQL, you can first “pin” the desired execution plan using create_spb_from_cur.sql out of SQLT (215187.1).

     Carlos

     

     Carlos Sierra

     December 3, 2015 at 7:54 am

     Reply
6. […] few years ago I created a post about “how to flush a cursor out the shared pool“, using DBMS_SHARED_POOL.PURGE. For the most part, this method has helped me to get rid of […]

   

   Purging a cursor in Oracle – revisited | Carlos Sierra's Tools and Tips

   November 22, 2017 at 5:55 am

   Reply
7. […] Note – the “Purge a cursor from cache” is of type “use at your own risk”, but it has the capability to purge just one cursor, based on the SQL_ID, from the cache. I think I based that on Carlos Sierras example. […]

   Find bad performing queries or code with PL/Scope – – nice theory…

   November 30, 2018 at 8:25 am

   Reply
8. i just download your sqlt but it’s had addition line as below

   SYS.DBMS_SHARED_POOL.UNKEEP(name => i.address||’,’||i.hash_value, flag => ‘C’);

   is it ok ? for old version db..

   

   Parmanto

   May 10, 2020 at 10:48 pm

   Reply
   • not sure what is the question? which version?

     

     Carlos Sierra

     May 11, 2020 at 2:29 pm

     Reply