Flush SQL Statement from Shared Pool
1 min readIf you flush the shared pool, all the statements in cursor will be flushed. 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 So if you want a SQL query to do hard parsing, then you can flush the particular SQL statement from shared pool. If multiple nodes, consider doing this plan for each node.
1) FIND ADDRESS AND HASH_VALUE OF SQL_ID
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7bz%';
ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0 808321886
2) PURGE THE PLAN FROM SHARED POOL 'C' (for cursor) or 'S' (for SQL) We used the V$ view to find cursor information.
SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');
PL/SQL procedure successfully completed.
NOTE: the single quote positions above. It is purposely NOT a single quote around each item. Else, you will get the error below
SQL> exec DBMS_SHARED_POOL.PURGE ('00000004E121EE18','1748938644','C');
BEGIN DBMS_SHARED_POOL.PURGE ('00000004E121EE18','1748938644','C'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00307: too many declarations of 'PURGE' match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
3) VERIFY
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7bz%';
no rows selected
