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');