How to Flush SQL Cursor Cache from Oracle Library Cache

All most all of Oracle DBAs are familiar with command to flush shared pool: alter system flush shared_pool;

This command clears all data from shared pool in system global area (SGA).  The shared pool stores cached data dictionary information and shared SQL and PL/SQL areas of SQL Statements, stored procedures, packages, functions and triggers.

 

Library Cache

 

This command is handy for dev, test or performance environments.  But what if you want to only flush a single SQL Cursor cache?  What if the environment is Production and a program (like Informatica) is stuck using bad explain plan and you want only flush this one SQL Cursor cache so optimizer can hard parse again and pick up a different explain plain?

To flush the cursor cache from Library Cache, you’ll need SQL ID of the query/cursor you want to flush.

Use the SQL ID to get address of the handle to the parent for this cursor and hash value of the parent statement in the library cache from v$sqlarea view.
SQL> select address, hash_value 
from v$sqlarea 
where sql_id in ('0wdq0hfrj4qxk');

ADDRESS          HASH_VALUE
---------------- ----------

0700000C5480FE90 804411656

Use these 2 values to purge the cursor cache. 2nd parameter can be anything except ‘P’ or ‘p’ or ‘Q’ or ‘q’ or ‘R’ or ‘r’ or ‘T’ or ‘t’.

SQL> exec dbms_shared_pool.purge('0700000C5480FE90, 804411656','C');

PL/SQL procedure successfully completed.

Verify that cursor cache has been cleared.

SQL> select address, hash_value 
from v$sqlarea 
where sql_id in ('0wdq0hfrj4qxk');

no rows selected

You can also run the following to generate purge sql:

SELECT 'exec dbms_shared_pool.purge(''' || ADDRESS || ',' || HASH_VALUE || ''',''C'');' 
FROM V$SQLAREA 
WHERE SQL_TEXT LIKE 'SELECT col1 from tab1%';

SELECT 'exec dbms_shared_pool.purge(''' || ADDRESS || ',' || HASH_VALUE || ''',''C'');' 
FROM V$SQLAREA 
where sql_id in ('0wdq0hfrj4qxk');

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *