Clear a single query from the shared pool

by Granville Bonyata on November 8, 2013

When the cost based optimizer (CBO) comes up with a less optimal plan than expected (probably because of an unfortunate case of bind variable peeking), the solution is to flush the query from the shared pool.

Instead of clearing the entire shared pool and impacting the entire application, that query can be cleared:

--Get the sql id:
select sql_id, address, hash_value, sql_text
from v$sql
where sql_text = 'select * from user_tables';


SQL_ID ADDRESS HASH_VALUE SQL_TEXT
------------- ---------------- ---------------------- --------------------------------
gyba5s06nr7rn 000007FF55B3E128 223059700 select * from user_tables

exec SYS.DBMS_SHARED_POOL.PURGE ('000007FF55B3E128, 223059700', 'a');

The ‘a’ is just a placeholder – as long as it isn’t any of the parameter values, it assumes you’re clearing a cursor: “In case the first argument is a cursor address and hash-value, the parameter should be set to any character except ‘P’ or ‘p’ or ‘Q’ or ‘q’ or ‘R’ or ‘r’ or ‘T’ or ‘t’.”

And now it is gone from the shared pool:

select sql_id, address, hash_value, sql_text
from v$sql
where sql_text = 'select * from user_tables';

SQL_ID ADDRESS HASH_VALUE SQL_TEXT
------------- ---------------- ---------------------- --------------------------------

Previous post:

Next post: