How to Flush Bad SQL Plan from Shared Pool

At times required to flush the BAD SQL_PLAN from shared_pool so that the new better execution plan can be picked by SQL_ID



STEP 1: FIND ADDRESS AND HASH_VALUE OF SQL_ID


SQL> select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '97s9z1zzpsp99%';


ADDRESS          HASH_VALUE

---------------- ----------

000000085FD77AF7  108321884

 


STEP 2 : PURGE THE PLAN FROM SHARED POOL


SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77AF7, 108321884', 'C');


PL/SQL procedure successfully completed.

 


STEP 3: VERIFY


SQL> select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '97s9z1zzpsp99%';


no rows selected


‘C’ (for cursor) or ‘S’ (for SQL)


BELOW SCRIPT CAN BE USED FOR THE SAME:


SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

BEGIN

 FOR i IN (SELECT address, hash_value

 FROM gv$sqlarea WHERE sql_id = '&&sql_id.')

 LOOP

 SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');

 END LOOP;

END;

/



SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

UNDEF sql_id;


Comments

Popular posts from this blog

How to drop index and before dropping it how to get the DDL.

How to set up the Oracle Wallets in Oracle Database 19C

PRVG-11250 : The check "RPM Package Manager database" was not performed because

ORA-00257:archiver error, connect internal only until freed

Linux OL7/RHEL7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

Verifying Daemon “Avahi-Daemon” Not Configured And Running …FAILED (PRVG-1360)

SKIP DNS RESLOV.CONF CHECK DURING RAC CONFIGURATION

How to write to a CSV file using Oracle SQL*Plus

Change Password in an Oracle Database