How to Flush bad SQL Plan from shared pool in Oracle RAC

In some of the performance issues, we may need to flush bad SQL plan from shared pool in Oracle RAC 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 in Oracle RAC:

Step 1. Check how many plans the SQL has

col SQL_PLAN_BASELINE for a23
col SQL_PROFILE for a32
set lines 222 pages 222
select inst_id,sql_id,plan_hash_value,LAST_ACTIVE_TIME,child_number, executions, buffer_gets, buffer_gets/executions "LIOs/Exec",DISK_READS/executions "PIOs/Exec",SQL_PLAN_BASELINE, SQL_PROFILE from gv$sql where
sql_id = '&sql_id'
and executions!=0
order by 2,3,4;

Output Will be like :

How to Flush bad SQL Plan from Shared Pool in Oracle RAC :

Step 2. Identify which plan to purge

Here we are assuming 6754334012 PHV to purge.

Issue the below command in instances 2 and 3 as this PHV is present in both instances :

 select 'exec DBMS_SHARED_POOL.PURGE ('||''''||ADDRESS||','||HASH_VALUE||''''|| ','|| '''C'''||');' from v$sqlarea where SQL_ID in
(select distinct sql_id from v$sql where sql_id='&sqlid' and PLAN_HASH_VALUE=&plan_hash_val and child_number='&cno');

The above command will ask for SQL_ID, PHV, and child_number. Here we have given below input in instances 2 and 3 respectively :

Instance 2 :

Flush bad SQL Plan from shared pool in Oracle RAC

Instance 3 :

Step 3: Execute the above-generated command.

It will flush bad SQL Plan from Shared Pool in Oracle RAC.

Instance 2:
SQL> exec DBMS_SHARED_POOL.PURGE ('000000081B571FC8,6754334012','C');
PL/SQL procedure successfully completed.
Instance 3:
SQL>exec DBMS_SHARED_POOL.PURGE ('000000081B571GF9,6754334012','C');
PL/SQL procedure successfully completed.

Step 4: Issue the step 1 command again to validate if the plan has been purged.

There should not be the purged plan present . It would be as below :

So, here are the detailed steps that you can get to know how to flush bad SQL Plan from shared pool in Oracle RAC database. Hope this helps!!Also, you can get further reference from Oracle Doc ID 2993366.1

Related Articles :