How to Remove a Cached SQL Execution Plan in Oracle

A cursor cache is a stored execution plan of a SQL statement in memory. Oracle reuses it to improve performance. However, sometimes we need to clear the cache to apply a new execution plan.

Steps to Clear a Cursor Cache

To clear a specific SQL statement from the cache, follow these steps:

1. Get the Address and Hash Value

Run the following SQL query to find the ADDRESS and HASH_VALUE of the SQL statement:

SELECT inst_id, address, hash_value, users_executing 
FROM gv$sqlarea 
WHERE sql_id = '9x12bcd34efgh' 
ORDER BY 1;

Example output:

INST_ID   ADDRESS            HASH_VALUE    USERS_EXECUTING
1         08000200AA7CDE12   3456789012    0
2         08000200BB8F1234   3456789012    0

2. Generate the Purge Commands

Use the following SQL command to generate the DBMS_SHARED_POOL.PURGE commands:

SELECT inst_id, 
       'exec dbms_shared_pool.purge(''' || address || ',' || hash_value || ''',''C'');' stmt 
FROM gv$sqlarea 
WHERE sql_id = '9x12bcd34efgh' 
ORDER BY 1;

Example output:

INST_ID   STMT
1         exec dbms_shared_pool.purge('08000200AA7CDE12,3456789012','C');
2         exec dbms_shared_pool.purge('08000200BB8F1234,3456789012','C');

3. Execute the Purge Commands

Run the generated purge commands for each instance:

For Node 1:

EXEC dbms_shared_pool.purge('08000200AA7CDE12,3456789012','C');

For Node 2:

EXEC dbms_shared_pool.purge('08000200BB8F1234,3456789012','C');

Expected Result

If successful, the SQL execution plan will be removed from the shared pool. You can verify by checking the gv$sqlarea view again to ensure the SQL statement is no longer in cache.

What If Purging Fails?

If DBMS_SHARED_POOL.PURGE does not work as expected, try the following:

1. Flush the shared pool:

ALTER SYSTEM FLUSH SHARED_POOL;

2. Modify the execution plan** by using hints or restructuring the query.

By following these steps, you can ensure your SQL statements use the latest execution plan