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
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;
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;
