Skip to content

Flush Bad SQL Plan from Shared Pool

Sometimes it is required to flush the BAD SQL_PLAN from shared_pool so that new (or old) better execution plan can be picked by SQL_ID

 

1) FIND ADDRESS AND HASH_VALUE OF SQL_ID

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

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

 

2) PURGE THE PLAN FROM SHARED POOL

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

PL/SQL procedure successfully completed.

 

3) VERIFY

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

NOTE:

++++++++++++++++++++++++++++++++++++++++++++

  • ‘C’ (for cursor) or ‘S’ (for SQL)
  • In this example, we used the V$ view to find cursor information. If you are in a RAC environment, change your query to use gv$sqlarea just in case the SQL statement you are looking for was executed on an instance other than the one you are currently logged into.

+++++++++++++++++++++++++++++++++++++++++++++

 

FLUSHING BAD PLAN USING BELOW SCRIPT:

This script will flush one cursor out of the shared pool. Works on 11g and above. It will need SQL_ID as input parameter

SPOOL ON flush_cursor_&&sql_id..txt;
PRO *** before flush ***
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;
/

PRO *** after flush ***
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;
SPOOL OFF;

 

 

 

 

 

[Post Views: 1165]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

2 Comments

  1. Smita S. Smita S.

    Thanks for Sharing Brijesh,
    One question, when do we use ‘S’ (for SQL) and C- Cursor.
    I mean what will be the difference here .

    Thanks in advance.

  2. Pawel Adamski Pawel Adamski

    It works good since 11gR2 (as I tested in practice) but surprisingly did not work in 10.2.0.4 – DBMS_SHARED_POOL.PURGE executed without errors but the execution plan was still alive in SGA. I had to flush whole SHARED POOL in the end.

Leave a Reply

Required fields are marked *