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;

 

 

 

 

 

Brijesh Gogia

5 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.

  3. E Noel E Noel

    So helpful. Thank you.

  4. Anonymous Anonymous

    Hi Brijesh

    I am new to DB performance tuning
    Does pinning a plan mean creating a sql profile?

    Actually we are migrating from 11g to 19c and would like to know if pinned plans in 11g be migrated to 19c.

  5. wtolentino wtolentino

    the sql_id exists on gv$sqlarea when used the DBMS_SHARED_POOL.PURGE

    select ADDRESS, HASH_VALUE from gv$sqlarea where SQL_Id=’2pfky1pq64ca2′;
    exec sys.DBMS_SHARED_POOL.PURGE (‘00000003248F6E58,1818374466′,’C’);

    it throws an error:

    Error starting at line : 7 in command –
    BEGIN sys.DBMS_SHARED_POOL.PURGE (‘00000003248F6E58,1818374466′,’C’); END;
    Error report –
    ORA-06570: shared pool object does not exist, cannot be pinned/purged
    ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 51
    ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 120
    ORA-06512: at line 1
    06570. 00000 – “shared pool object does not exist, cannot be pinned/purged”
    *Cause: The specified shared pool shared cursor could not be found,
    therefore it cannot be pinned/purged.
    *Action: Make sure that a correct shared cursor name is given. Names
    are a string of the form ‘HHHHHHHH,SDDDDDDDDDD’ where the H’s
    are an 8 digit hex number from the ‘address’ column of v$sqlarea,
    and the D’s are a 1 to 10 digit decimal number with an optional
    leading sign (from the ‘hash_value’ column)
    *Action: Remove the procedure from the calling stored procedure.

    it does not exists on v$sqlarea but exists on gv$sqlarea. how do i remove the one that exist on the gv$sqlarea.

    thank you.

Leave a Reply