Purpose: Purge specific sql_id from Shared Pool.

Purpose: Purge specific sql_id from Shared Pool.

It could be useful if a sql have too many child cursors, which can cause "library cache: mutex X" wait events.

CREATE OR REPLACE PROCEDURE purge_sp_by_sql AS
  v_sql_str     VARCHAR2(1000);
  v_sql_id      VARCHAR2(13);
  v_address     RAW(8);
  v_hash_value  NUMBER;
  v_param       VARCHAR2(1000);
  v_child_records_before  INTEGER;
  v_child_records_after  INTEGER;
  v_module_name SGA_W_LOG.procedure_name%TYPE; 
  v_msg_str     SGA_W_LOG.data%TYPE;
BEGIN
  v_module_name := 'purge_sp_by_sql'; 
  
  SELECT sql_id, address, hash_value 
    INTO v_sql_id, v_address, v_hash_value
    FROM SYS.V_$SQLAREA
   WHERE sql_text LIKE 'MERGE into AAA_B_CCC_DDDDDD o using%' 
     AND ROWNUM < 2;
 
  SELECT loaded_versions INTO v_child_records_before 

    FROM SYS.V_$SQLAREA WHERE sql_id = v_sql_id;

   --Format is: 'ADDRESS,HASH_VALUE',
  v_param := v_address||','||v_hash_value;
  SYS.DBMS_SHARED_POOL.purge (v_param,'C');
  
  SELECT loaded_versions INTO v_child_records_after 

    FROM SYS.V_$SQLAREA WHERE sql_id = v_sql_id;
   
  v_msg_str :=  'Child Records Stats for sql_id : '||v_sql_id||' Before: '||v_child_records_before||' After: '||v_child_records_after;
  INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified) 
  VALUES (v_module_name, v_msg_str, SYSDATE);
  commit;
END purge_sp_by_sql;