Blog

In some of the performance issues, we may need to flush bad SQL plan from shared pool in Oracle Database. Today we will learn how to do that with detailed steps and an example. Let’s go through it.

Steps to Flush bad SQL Plan from shared pool :

1) Find the address and hash_value OF SQL_ID for whih you want to flush the plan :

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID ='8hciyhtgfrtty';

ADDRESS          HASH_VALUE
---------------- ----------
000000090GH889B0  906621898

2) Issue the below command to flush the plan from shared pool :

SQL> exec DBMS_SHARED_POOL.PURGE ('000000090GH889B0, 906621898', 'C');

PL/SQL procedure successfully completed.

3) Validate if the plan is present in the shared pool :

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '8hciyhtgfrtty';

no rows selected

So, here are the steps that you can get to flush bad SQL plan from shared pool in Oracle. To do the same in RAC, you can follow our article : How to Flush bad SQL Plan from Shared Pool in Oracle RAC.

Also, you can follow Oracle Oracle Doc ID 2993366.1 too for further reference.