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;