How to Purge SQL statements from the Shared Pool

  • Posted on
  • Posted in SQL

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;

DBMS_SHARED_POOL Docmentation

SHARE

ndmsoft

Leave a Reply

Your email address will not be published. Required fields are marked *.

*
*
You may use these <abbr title="HyperText Markup Language">HTML</abbr> tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

BACK TO TOP