How to Purge SQL statements from the Shared Pool
Context:
When Oracle database receives a SQL statement for execution, it checks if there is already a execution plan stored in the shared pool area.
- If it finds one, it will use it. This is called a library cache hit, or a soft parse.
- If it does not find one, it will create it. This is called a library cache miss, or a hard parse. (expensive operation)
The SQLs stored is the library cache are available in the following view:
select * from V$SQLAREA
The following script can be used to purge a SQL statement from the library cache, and it is usefull for testing purposes:
set serveroutput on
declare
v_nr number:= 0;
v_err number:= 0;
begin
for rec in ( select distinct ADDRESS||', '||HASH_VALUE as param_value
from V$SQLAREA
where sql_text like 'select name from customer_data where id%'
)
loop
begin
DBMS_SHARED_POOL.PURGE (rec.param_value, 'C');
v_nr := v_nr +1;
exception
when others then
v_err := v_err+1;
dbms_output.put_line (sqlerrm);
end;
end loop;
dbms_output.put_line (v_nr||' SQLs purged sucessfully from Shared Pool');
if v_err > 0 then
dbms_output.put_line (v_err||' SQLs not purged due to error');
end if;
end;
