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.
Table of Contents
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 :
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 :
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 :
Pingback: How to Flush bad SQL Plan from Shared Pool in Oracle