Flushing a single SQL from the shared pool

Many DBA's want to flush independent sql's when there is a situation to reparse the sql for testing or even at times to put a quick workaround for plan deviations. Traditionally we opt for flushing the shared pool, which at times could break another sql.So what could we do to avoid it, cant we flush one sql at a time ? there was a partial answer to this by Kerry Osborne by creating and dropping outlines.Which was great but dint work semlessly always (no other option if you are still stuck in a old version).


But if you are in 10.2.0.4 or above there is another winner in DBMS_SHARED_POOL.PURGE which does purge a single sql.

The DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release, but might not work if the db was upgraded from 10.2.0.X to 10.2.0.4. For 10.2.0.2/3 patch 5614566 can be installed to get this going.


The syntax for the procedure is as below and I will show more examples for different scenarios through this article.



DBMS_SHARED_POOL.PURGE (
name VARCHAR2,
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1)


SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
1384ubj5yw6d4 select * from dba_objects where rownum < 5 SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='1384ubj5yw6d4';

ADDRESS HASH_VALUE
---------------- ----------
000000019BFCB8E8 1273895332


SQL> alter session set events '5614566 trace name context forever'; ---> This is event protected in 10.2.0.4, not required for 11g

Session altered.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000019BFCB8E8,1273895332','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='1384ubj5yw6d4';

no rows selected


Perfect that has flushed one sql now.


Before we close we will take a look at one other variant of this procedure to clear a sequnce from the shared pool which could find a use in the DBA's life.

SQL> create sequence test_seq start with 1 increment by 1 cache 5; ---> created a sequence with cache value 5

Sequence created.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
1

exec DBMS_SHARED_POOL.PURGE ('TEST_SEQ','Q')

PL/SQL procedure successfully completed.

SQL> select test_seq.nextval from dual; ---> since we flushed the sequence with cache value of 5, the sequence has jummped to six

NEXTVAL
----------
6


Hope you found this usefull...