Skip to content

Flush Bad SQL Plan from Shared Pool

Published July 8, 2015 By Brijesh Gogia

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
Latest posts by Brijesh Gogia (see all)
Print 🖨
Post Views: 5,222

Related posts:

  1. Move SQL Plan Baseline to other Instance
  2. Performance Tuning Basics 3 : Parent and Child Cursors
  3. Create SQL Baseline for SQL_ID
  4. Oracle EXPDP/IMPDP (DATAPUMP) Monitoring Scripts
  5. Performance Tuning Basics 4 : Bind Variables
  6. Oracle Applications Database frequently used scripts
  7. Create Tuning Task Manually
  8. RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
  9. Find SQL_ID of a SQL Statement in a PL/SQL Block
  10. Move SQL PROFILE to other database

Categories:SCRIPTS SQL Tuning TUNING Tuning Scripts

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.

    September 2, 2018 Reply
  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.

    October 11, 2018 Reply
  3. E Noel E Noel

    So helpful. Thank you.

    September 11, 2019 Reply
  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.

    April 19, 2022 Reply
  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.

    July 2, 2022 Reply
Leave a Reply