Flush the SQL id plan from the Shared pool Memory

Flush the SQL id plan from the Shared pool Memory

Shared pool is the SGA area where sql and plsql objects are stored in memory
DBMS_SHARED_POOL.PURGE Procedure is used to flush a SQL plan from Shared pool memory by using the address and hash_value as parameter found in V$SQLAREA.
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 (
schema VARCHAR2,
objname VARCHAR2,
namespace NUMBER,
heaps NUMBER);

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.

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';

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');

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.