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.
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.
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');
how to flush a single cursor cache that was in the gv$sqlarea but not in the v$sqlarea?
i query the sql id
select ADDRESS, HASH_VALUE from gv$sqlarea where SQL_Id=’2pfky1pq64ca2′;
then
exec sys.DBMS_SHARED_POOL.PURGE (‘00000003248F6E58,1818374466′,’C’);
it throws an error:
Error starting at line : 7 in command –
BEGIN sys.DBMS_SHARED_POOL.PURGE (‘00000003248F6E58,1818374466′,’C’); END;
Error report –
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 51
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 120
ORA-06512: at line 1
06570. 00000 – “shared pool object does not exist, cannot be pinned/purged”
*Cause: The specified shared pool shared cursor could not be found,
therefore it cannot be pinned/purged.
*Action: Make sure that a correct shared cursor name is given. Names
are a string of the form ‘HHHHHHHH,SDDDDDDDDDD’ where the H’s
are an 8 digit hex number from the ‘address’ column of v$sqlarea,
and the D’s are a 1 to 10 digit decimal number with an optional
leading sign (from the ‘hash_value’ column)
*Action: Remove the procedure from the calling stored procedure.
thanks.