How to flush one Cursor out of the Shared Pool

 

How to flush one Cursor out of the Shared Pool

flush_cursor.sql

REM Flushes one cursor out of the shared pool. Works on 11g+

REM To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.

REM The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs.

REM These scripts are not run by as part of standard database creation.

SPO flush_cursor_&&sql_id..txt;

PRO *** before flush ***

SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

BEGIN

 FOR i IN (SELECT address, hash_value

 FROM gv$sqlarea WHERE sql_id = '&&sql_id.')

 LOOP

 SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');

 END LOOP;

END;

/

PRO *** after flush ***

SELECT inst_id, loaded_versions, invalidations, address, hash_value

FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;

SELECT inst_id, child_number, plan_hash_value, executions, is_shareable

FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;

UNDEF sql_id;

SPO OFF;




=========================================================




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;

/

Article by kerryosborne regarding Flush of a Single Statement

• flush the shared pool – not a very appealing option in a production environment (although I see almost the same affect frequently at sites that gathering stats every night).
• modify an object that the statement depends on – I usually would add a comment to one of the tables used by the statement. Unfortunately, all statements that use the table will be flushed, so this technique can also be a little hard on a production system, but it’s certainly better than flushing the whole shared pool. 

Small Script Witten flush_sql10.sql 

----------------------------------------------------------------------------------------

--

-- File name:   flush_sql10.sql

--

-- Purpose:     Flush a single SQL statement.

-

-- Author:      Kerry Osborne

--

-- Usage:       This scripts prompts for two values.

--

--              sql_id: the sql_id of a statement that is in the shared pool (v$sqlarea)

--

--              child_number: a valid child_number for the given statement (v$sql) 

--                            - child_number defaults to 0

--

-- Description: This scripts creates an outline on the specified statement and then 

--              attempts to drop the outline. This has the side effect of flushing the 

--              statement from the shared pool. See kerryosborne.oracle-guy.com for 

--              additional information.

--

---------------------------------------------------------------------------------------

-- this is here to attempt to avoid the "ORA-03113: end-of-file on communication channel" error

-- (per metalink) to workaround Bug 5454975 (supposedly fixed 10.2.0.4)

alter session set use_stored_outlines=true;

set serveroutput on for wrap

set pagesize 9999

set linesize 155

var hval number

accept sql_id -

       prompt 'Enter value for sql_id: ' 

accept child_number -

       prompt 'Enter value for child_number: ' - 

       default 0

DECLARE

   name1 varchar2(30);

   sql_string varchar2(300);

BEGIN

   select hash_value into :hval

   from v$sqlarea 

   where sql_id like '&&sql_id';

   DBMS_OUTLN.create_outline(

    hash_value    => :hval, 

    child_number  => &&child_number);

--

-- The next step is a little dangerous,

-- it drops the last outline created (as long as it was create in the last 5 seconds or so)

-- Also note that it appears the category must be default to flush the statement comepletely

--

   select 'drop outline '||name,name into sql_string, name1

   from dba_outlines 

   where timestamp = (select max(timestamp) from dba_outlines)

   and timestamp > sysdate-(5/86400);

   dbms_output.put_line(' ');

   execute immediate sql_string;

   dbms_output.put_line('SQL Statement '||'&&sql_id'||' flushed.');

END;

/

undef sql_id

undef child_number

Ref- https://carlos-sierra.net/2013/02/28/how-to-flush-one-cursor-out-of-the-shared-pool/