Flush specific id and all execution plan from Shared pool in Oracle

Flush specific id and all execution plan from Shared pool in Oracle

Flushing the shared pool lead to hard parse of the SQL queries running from the Application which will degrade the performance also.
You can flush the specific query also instead of flushing the whole shared pool area.

Check wheather SQLAREA plan value present in Shared pool

select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '';

select ADDRESS, HASH_VALUE from GV$SQLAREA where PLAN_HASH_VALUE like '';

Script for flush SQL ID from the database shared pool area.

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

Script for flush all SQL id plans present in SQLAREA

BEGIN
FOR i IN (SELECT address, hash_value FROM gv$sqlarea)
LOOP
SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
END LOOP;
END;
/

You can flush entire shared pool

Alter system flush shared_pool;

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.