Flush the SQL id execution plan from the Shared pool Memory

Shared pool is the SGA area where SQL and PLSQL objects are stored in memory area. DBMS_SHARED_POOL.PURGE Procedure is used to flush a SQL plan from Shared pool memory by passing the address and hash_value as parameter found in V$SQLAREA.

Steps for Purging for specific SQL Query from shared pool:

1. Find the SQL id from v$sqlarea view.

select sql_id from v$Sqlarea where sql_Text like 'select * from emp';

select sql_id from v$sqlarea where Plan_hash_value= '453351686';


2. Find the address and hash value from v$sqlarea after finding sqlid:

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '8ycabihre66ab';

ADDRESS           HASH_VALUE
----------------  ----------
000000025AJ44GH0  453351686

3. Purge the SQL Id with purge procedure:

exec DBMS_SHARED_POOL.PURGE ('000000025AJ44GH0, 453351686', 'C');


4. Again verified.

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '8ycabihre66ab%';

no row found


Mote: If multiple plan exists for same sql id or plan hash value then you can use this query:

select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '|| HASH_VALUE||''',
''C'');' from V$SQLAREA where PLAN_HASH_VALUE = '1235279176';

select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '|| HASH_VALUE||''',
''C'');' from V$SQLAREA where SQL_ID like '8ycabihre66ab%';


For Create the DBMS_SHARED_POOL need to execute the script present in RDBMS folder as the DBMSPOOL.SQL script.

Syntax:

DBMS_SHARED_POOL.PURGE (name VARCHAR2, flag CHAR DEFAULT 'P', heaps NUMBER DEFAULT 1);

DBMS_SHARED_POOL.PURGE (hash VARCHAR2, namespace NUMBER,heaps NUMBER);

Name: is the name of object to purge from shared pool memory area.
flag: P is default value identify package/procedure/function , T is set for type, R is for trigger and S for sequence
heaps: 0 means whole object to be purged.
schema : username of the object.
objname: Name of the object.
namespace: library cache namespace in which object present.
hash: 16-byte hash value.