Carlos Sierra's Tools and Tips

A few years ago I created a post about “how to flush a cursor out the shared pool“, using DBMS_SHARED_POOL.PURGE. For the most part, this method has helped me to get rid of an entire parent cursor and all child cursors for a given SQL, but more often than not I have found than on 12c this method may not work, leaving active a set of cursors I want to flush.

Script below is an enhanced version, where besides using DBMS_SHARED_POOL.PURGE, we also create a dummy SQL patch, then drop it. This method seems to completely flush parent and child cursors. Why using this method instead?: We are implementing SQL Plan Management (SPM), and we have found that in some cases, some child cursors are still shared several hours after a SQL Plan Baseline (SPB) is created. We could argue a possible bug and pursue as such, but in the meantime my quick and dirty workaround is: whenever I want to flush an individual parent cursor for one SQL, and all of its child cursors, I just execute script below passing SQL_ID.

Anyways, just wanted to share and document this purge_cursor.sql script for those in similar need. I have developed it on 12.1.0.2, and haven’t tested it on lower or higher versions.

-- purge_cursor.sql
DECLARE
 l_name VARCHAR2(64);
 l_sql_text CLOB;
BEGIN
 -- get address, hash_value and sql text
 SELECT address||','||hash_value, sql_fulltext 
 INTO l_name, l_sql_text 
 FROM v$sqlarea 
 WHERE sql_id = '&&sql_id.';
 -- not always does the job
 SYS.DBMS_SHARED_POOL.PURGE (
 name => l_name,
 flag => 'C',
 heaps => 1
 );
 -- create fake sql patch
 SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH (
 sql_text => l_sql_text,
 hint_text => 'NULL',
 name => 'purge_&&sql_id.',
 description => 'PURGE CURSOR',
 category => 'DEFAULT',
 validate => TRUE
 );
 -- drop fake sql patch
 SYS.DBMS_SQLDIAG.DROP_SQL_PATCH (
 name => 'purge_&&sql_id.', 
 ignore => TRUE
 );
END;
/