How to Flush a Single SQL Statement from the Shared Pool

July 15, 2018
Sometimes it is required to flush the single sql plan from the shared pool and it a good idea rather than flushing all the sql plan from the memory. After baselining or fixing the bad plan, DBA’s wanted to kick out the sql plan from the memory to confirm, if optimizer is picking the correct plan.

flush_sql_id_shared_pool

I have seen that many guys simply use to flush the shared pool using alter system flush shared_pool statement which is not a good idea. Rather if you are struggling for fixing the bad plan of any single query, it is advisable to flush the single sql_id out of the memory.

STEP 1: Find Address and  hash_value of particular sql_id.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='251fjyn5fj84q';

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

00000005DGEC9DE0 257655674

STEP 2: Purge sql plan from the shared pool by passing the above values.

SQL> exec DBMS_SHARED_POOL.PURGE ('00000005DGEC9DE0, 257655674', 'C');

PL/SQL procedure successfully completed.

Here, ‘C’ (for cursor)

STEP 3: Check if the plan still exist in the memory. If no rows selected then plan has been flushed out from the memory for that sql_id.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='251fjyn5fj84q';

no rows selected

In the above example, we have used the V$ view to find cursor information. If you are using RAC environment then change your query to use gv$sqlarea just in case the SQL statement you are looking for was executed on an instance other than the one you are currently logged into.

So this is all about flushing the sql statement from the shared pool. If you are facing such perforamce issues then you can use the above method to flush plan for the particular sql_id from the memory.

Must Read: