About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Thursday, December 15, 2016

How to purge/flush a single SQL PLAN from shared pool in Oracle

Purging a SQL PLAN from shared pool is not a frequent activity , we generally do it when a query is constantly picking up the bad plan and we want the sql to go for a hard parse next time it runs in database.

Obviously we can pass a hint in the query to force it for a Hard Parse but that will require a change in query , indirectly change in the application code , which is generally not possible in a business critical application.

We can flush the entire shared pool but that will invalidate all the sql plans available in the database and all sql queries will go for a hard parse. Flushing shared pool can have adverse affect on your database performance.

Flush the entire shared pool :-

Alter system flush shared_pool;

Flushing a single SQL plan from database will require certain details for that sql statement like address of the handle and hash value of the cursor holding the SQL plan.

Steps to Flush/purge a particular sql plan from Shared pool :-

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

ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

Now we have the address of the handle and hash value of the cursor holding the sql. Flush this from shared pool.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

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

no rows selected

SQL plan flushed for above particlar sql, Now next time above sql/query will go for a hard parse in database.

I hope this article helped you.

Thanks
Amit Rath

8 comments:

  1. Its better not to flush all SQL_ID out of shared pool queries. Great article.

    ReplyDelete
  2. Definitely did. Thanks so much

    ReplyDelete
  3. I have read your article. It is very good. Can you please share the document to force the sql hash to the sqlid.

    ReplyDelete
  4. I tried these in 19c database, it's not working. Is there any change in 19c ?

    ReplyDelete
    Replies
    1. It worked for me in 19c.
      I copied the plan from prod in 11204 into test in 19c

      Delete