Flush A Single SQL Statement
In 11g, the DBMS_SHARED_POOL package has a couple of new procedures. One of them is the PURGE procedure which allows individual objects to be flushed from the shared pool. The create statement for this package can be found in the $ORACLE_HOME/rdbms/admin/dbmspool.sql script. I first ran across this about a year ago on Fairlie Rego’s Blog. The modification history shows that the PURGE procedure was written back in Aug, 2006 – so it’s been around for a while. The PURGE procedure can be used to dump objects from the shared pool including code (procedure/package/function), sequences, type definitions, triggers, and cursors (i.e. SQL statments). The ability to flush a single SQL statement is what caught my eye. Unfortunately, the option to purge cursors is not in the 11.1 documentation but it is documented (lightly) in the header of the package definition as you can see here.
rem $Header: dbmspool.sql 14-nov-2006.02:58:03 desinha Exp $
rem
Rem Copyright (c) 1991, 2006, Oracle. All rights reserved.
Rem NAME
Rem dbmspool.sql - dbms_shared_pool utility package.
Rem DESCRIPTION
Rem This package allows you to display the sizes of objects in the
Rem shared pool, and mark them for keeping or unkeeping in order to
Rem reduce memory fragmentation.
Rem RETURNS
Rem
Rem NOTES
Rem MODIFIED (MM/DD/YY)
Rem desinha 08/29/06 - Add purge
Rem rburns 05/07/06 - split for parallel
Rem mjungerm 06/15/99 - add java shared data object type
Rem ansriniv 04/13/98 - keep functionality for types
Rem gdoherty 06/05/97 - invoke prvtpool
Rem ansriniv 03/26/97 - provide keep/unkeep for sequences
Rem asurpur 04/09/96 - Dictionary Protection Implementation
Rem bhirano 12/23/94 - merge changes from branch 1.3.720.1
Rem adowning 02/23/94 - split into public/private files
Rem ajasuja 01/06/94 - merge changes from branch 1.1.312.1
Rem rkooi 04/20/93 - change psdkeep to psdkep
Rem ajasuja 11/05/93 - handle UNIX addresses
Rem rkooi 12/08/92 - Creation
create or replace package dbms_shared_pool is
------------
-- OVERVIEW
--
-- This package provides access to the shared pool. This is the
-- shared memory area where cursors and PL/SQL objects are stored.
----------------------------
-- PROCEDURES AND FUNCTIONS
--
procedure sizes(minsize number);
-- Show objects in the shared_pool that are larger than the specified
-- size. The name of the object is also given which can be used as
-- an argument to either the 'keep' or 'unkeep' calls below. You should
-- issue the SQLDBA or SQLPLUS 'set serveroutput on size xxxxx'
-- command prior to using this procedure so that the results will
-- be displayed.
-- Input arguments:
-- minsize
-- Size, in kilobytes, over which an object must be occupying in the
-- shared pool, in order for it to be displayed.
procedure keep(name varchar2, flag char DEFAULT 'P');
-- Keep an object in the shared pool. Once an object has been keeped in
-- the shared pool, it is not subject to aging out of the pool. This
-- may be useful for certain semi-frequently used large objects since
-- when large objects are brought into the shared pool, a larger
-- number of other objects (much more than the size of the object
-- being brought in, may need to be aged out in order to create a
-- contiguous area large enough.
-- WARNING: This procedure may not be supported in the future when
-- and if automatic mechanisms are implemented to make this
-- unnecessary.
-- Input arguments:
-- name
-- The name of the object to keep. There are two kinds of objects:
-- PL/SQL objects, triggers, sequences, types and Java objects,
-- which are specified by name, and
-- SQL cursor objects which are specified by a two-part number
-- (indicating a location in the shared pool). For example:
-- dbms_shared_pool.keep('scott.hispackage')
-- will keep package HISPACKAGE, owned by SCOTT. The names for
-- PL/SQL objects follows SQL rules for naming objects (i.e.,
-- delimited identifiers, multi-byte names, etc. are allowed).
-- A cursor can be keeped by
-- dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
-- The complete hexadecimal address must be in the first 8 characters.
-- The value for this identifier is the concatenation of the
-- 'address' and 'hash_value' columns from the v$sqlarea view. This
-- is displayed by the 'sizes' call above.
-- Currently 'TABLE' and 'VIEW' objects may not be keeped.
-- flag
-- This is an optional parameter. If the parameter is not specified,
-- the package assumes that the first parameter is the name of a
-- package/procedure/function and will resolve the name. Otherwise,
-- the parameter is a character string indicating what kind of object
-- to keep the name identifies. The string is case insensitive.
-- The possible values and the kinds of objects they indicate are
-- given in the following table:
-- Value Kind of Object to keep
-- ----- ----------------------
-- P package/procedure/function
-- Q sequence
-- R trigger
-- T type
-- JS java source
-- JC java class
-- JR java resource
-- JD java shared data
-- C cursor
-- If and only if the first argument is a cursor address and hash-value,
-- the flag parameter should be set to 'C' (or 'c').
-- Exceptions:
-- An exception will raised if the named object cannot be found.
procedure unkeep(name varchar2, flag char DEFAULT 'P');
-- Unkeep the named object.
-- WARNING: This procedure may not be supported in the future when
-- and if automatic mechanisms are implemented to make this
-- unnecessary.
-- Input arguments:
-- name
-- The name of the object to unkeep. See description of the name
-- object for the 'keep' procedure.
-- flag
-- See description of the flag parameter for the 'keep' procedure.
-- Exceptions:
-- An exception will raised if the named object cannot be found.
procedure purge(name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);
-- Purge the named object or particular heap(s) of the object.
-- Input arguments:
-- name
-- The name of the object to purge. See description of the name
-- object for the 'keep' procedure.
-- flag
-- See description of the flag parameter for the 'keep' procedure.
-- heaps
-- heaps to purge. e.g if heap 0 and heap 6 are to be purged.
-- 1=0 | 1=6 = hex 0x41 = decimal 65. so specify heaps=65.
-- Default is 1 i.e heap 0 which means the whole object will be purged.
-- Exceptions:
-- An exception will raised if the named object cannot be found.
procedure aborted_request_threshold(threshold_size number);
-- Set aborted request threshold for the shared pool.
-- Input arguments:
-- threshold_size
-- The size in bytes of a request which will not try to free unpinned
-- (not "unkeep-ed") memory within the shared pool. The range of
-- threshold_size is 5000 to ~2 GB inclusive.
-- Description:
-- Usually, if a request cannot be satisfied on the free list,
-- the RDBMS will try to reclaim memory by freeing objects from the
-- LRU list and checking periodically to see if the request can be
-- fulfilled. After finishing this step, the RDBMS has performed a near
-- equivalent of an 'alter system flush shared_pool'. As this impacts
-- all users on the system, this procedure "localizes" the impact to the
-- process failing to find a piece of shared pool memory of size
-- greater than thresh_hold size. This user will get the out of
-- memory error without attempting to search the LRU list.
-- Exceptions:
-- An exception will be raised if threshold is not in the valid range.
--
The interface to the PURGE procedure is a little screwy. You have to provide the SQL statement’s ADDRESS and HASH_VALUE from the v$sqlarea view, separated by a comma like so: ‘C18555B4,3891882158’. This seemed like a lot of bother so I wrote a little script that prompts for a sql_id and gets the address and hash_value for me and then calls the PURGE procedure. I called it flush_sql. Also the whole bit about flushing specific heaps didn’t make much sense to me, so the script just passes a value of 1 which appears to flush the whole statement including all the child cursors. Here’s an example:
> sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Mon Sep 29 14:02:03 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @multi_plans *** This script searchs the shared pool for SQL stataments with How_Many (or more) distinct plans. Enter value for how_many: 5 SQL_ID DISTINCT_PLANS SQL_TEXT ------------- -------------- -------------------------------------------------------------------------------- 8n73wdrfnzzs2 5 select tab.row_id, tab.msgid, tab.corrid, tab.priority, tab.delay, tab.expira tion, tab.retry_count, tab.exception_qschema, tab.exception_queue, tab.chain_n o, tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.enq _tid, tab.step_no, tab.sender_name, tab.sender_address, tab.sender_protocol, tab.dequeue_msgid, tab.user_prop, tab.user_data from "SYSMAN"."AQ$_MGMT_TASK_ QTABLE_F" tab where q_name = :1 and (state = :2 ) and ( tab.user_data.schedule d_time <= CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) AND (tab.user_data.messa ge_code = 0 OR tab.user_data.message_code = 1)) 9qst8n375p2gs 5 select tab.row_id, tab.msgid, tab.corrid, tab.priority, tab.delay, tab.expira tion, tab.retry_count, tab.exception_qschema, tab.exception_queue, tab.chain_n o, tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.enq _tid, tab.step_no, tab.sender_name, tab.sender_address, tab.sender_protocol, tab.dequeue_msgid, tab.user_prop, tab.user_data from "SYSMAN"."AQ$_MGMT_TASK_ QTABLE_F" tab where q_name = :1 and (state = :2 ) and (tab.user_data.message_c ode = 1 AND tab.user_data.task_id = 0) SQL> @find_sql Enter value for sql_text: Enter value for address: Enter value for sql_id: 9qst8n375p2gs SQL_ID CHILD PLAN_HASH EXECS ETIME AVG_ETIME USERNAME SQL_TEXT ------------- ------ ---------- ---------- ------------- ------------- ------------- ----------------------------------------- 9qst8n375p2gs 0 4252966799 982 .62 .00 SYSMAN select tab.row_id, tab.msgid, tab.corrid , tab.priority, tab.delay, tab.expirati on, tab.retry_count, tab.exception_qschem a, tab.exception_queue, tab.chain_no, t ab.local_order_no, tab.enq_time, tab.ti me_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sende r_address, tab.sender_protocol, tab.deq ueue_msgid, tab.user_prop, tab.user_data from "SYSMAN"."AQ$_MGMT_TASK_QTABLE_F" tab where q_name = :1 and (state = :2 ) and (tab.user_data.message_code = 1 AND tab.user_data.task_id = 0) 9qst8n375p2gs 1 4252966799 2060 1.25 .00 SYSMAN select tab.row_id, tab.msgid, tab.corrid , tab.priority, tab.delay, tab.expirati on, tab.retry_count, tab.exception_qschem a, tab.exception_queue, tab.chain_no, t ab.local_order_no, tab.enq_time, tab.ti me_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sende r_address, tab.sender_protocol, tab.deq ueue_msgid, tab.user_prop, tab.user_data from "SYSMAN"."AQ$_MGMT_TASK_QTABLE_F" tab where q_name = :1 and (state = :2 ) and (tab.user_data.message_code = 1 AND tab.user_data.task_id = 0) 9qst8n375p2gs 2 3220469022 2 .04 .02 SYSMAN select tab.row_id, tab.msgid, tab.corrid , tab.priority, tab.delay, tab.expirati on, tab.retry_count, tab.exception_qschem a, tab.exception_queue, tab.chain_no, t ab.local_order_no, tab.enq_time, tab.ti me_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sende r_address, tab.sender_protocol, tab.deq ueue_msgid, tab.user_prop, tab.user_data from "SYSMAN"."AQ$_MGMT_TASK_QTABLE_F" tab where q_name = :1 and (state = :2 ) and (tab.user_data.message_code = 1 AND tab.user_data.task_id = 0) 9qst8n375p2gs 3 3186456616 12 .05 .00 SYSMAN select tab.row_id, tab.msgid, tab.corrid , tab.priority, tab.delay, tab.expirati on, tab.retry_count, tab.exception_qschem a, tab.exception_queue, tab.chain_no, t ab.local_order_no, tab.enq_time, tab.ti me_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sende r_address, tab.sender_protocol, tab.deq ueue_msgid, tab.user_prop, tab.user_data from "SYSMAN"."AQ$_MGMT_TASK_QTABLE_F" tab where q_name = :1 and (state = :2 ) and (tab.user_data.message_code = 1 AND tab.user_data.task_id = 0) 9qst8n375p2gs 4 3894132994 5372 6.75 .00 SYSMAN select tab.row_id, tab.msgid, tab.corrid , tab.priority, tab.delay, tab.expirati on, tab.retry_count, tab.exception_qschem a, tab.exception_queue, tab.chain_no, t ab.local_order_no, tab.enq_time, tab.ti me_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sende r_address, tab.sender_protocol, tab.deq ueue_msgid, tab.user_prop, tab.user_data from "SYSMAN"."AQ$_MGMT_TASK_QTABLE_F" tab where q_name = :1 and (state = :2 ) and (tab.user_data.message_code = 1 AND tab.user_data.task_id = 0) 9qst8n375p2gs 5 3039444570 2 .04 .02 SYSMAN select tab.row_id, tab.msgid, tab.corrid , tab.priority, tab.delay, tab.expirati on, tab.retry_count, tab.exception_qschem a, tab.exception_queue, tab.chain_no, t ab.local_order_no, tab.enq_time, tab.ti me_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sende r_address, tab.sender_protocol, tab.deq ueue_msgid, tab.user_prop, tab.user_data from "SYSMAN"."AQ$_MGMT_TASK_QTABLE_F" tab where q_name = :1 and (state = :2 ) and (tab.user_data.message_code = 1 AND tab.user_data.task_id = 0) 6 rows selected. SQL> @flush_sql Enter value for sql_id: 9qst8n375p2gs PL/SQL procedure successfully completed. SQL> @find_sql Enter value for sql_text: Enter value for address: Enter value for sql_id: 9qst8n375p2gs no rows selected
This feature was actually back ported to 10g (10.2.0.4) however there is a bug (5614566) that keeps it from working unless you set an event like this:
grandpa(osborne:TSTDB1)> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 2 12:58:20 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @find_sql
Enter value for sql_text: %KSO T%
Enter value for address:
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS ETIME AVG_ETIME USERNAME SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------- ------------- -----------------------------------------
d2x6vyb3fb5dj 0 3543395131 1 .01 .01 SYS select /* KSO Test 1 */ * from dual
SQL @flush_sql
Enter value for sql_id: d2x6vyb3fb5dj
PL/SQL procedure successfully completed.
SQL> @find_sql
Enter value for sql_text:
Enter value for address:
Enter value for sql_id: d2x6vyb3fb5dj
SQL_ID CHILD PLAN_HASH EXECS ETIME AVG_ETIME USERNAME SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------- ------------- -----------------------------------------
d2x6vyb3fb5dj 0 3543395131 1 .01 .01 SYS select /* KSO Test 1 */ * from dual
SQL> -- Rats!
SQL> -- Bug 5614566
SQL> --
SQL> alter session set events '5614566 trace name context forever';
Session altered.
SQL> @flush_sql
Enter value for sql_id: d2x6vyb3fb5dj
PL/SQL procedure successfully completed.
SQL> @find_sql
Enter value for sql_text:
Enter value for address:
Enter value for sql_id: d2x6vyb3fb5dj
no rows selected
SQL> -- Cool!
Could you publish the sql script you used in your example?
I assume you mean the flush_sql.sql script. It is actually linked in the text in the post. I’m still experimenting with wrapping examples with that source code widget thing. Here’s the source in case you can’t get it the link to work.
set serveroutput on
set pagesize 9999
set linesize 155
var name varchar2(50)
accept sql_id –
prompt ‘Enter value for sql_id: ‘
BEGIN
select address||’,’||hash_value into :name
from v$sqlarea
where sql_id like ‘&&sql_id’;
dbms_shared_pool.purge(:name,’C’,1);
END;
/
undef sql_id
undef name
[…] posted earlier about the ability to flush a single SQL statement out of the shared pool in 11g (also back ported to 10.2.0.4 with a bit …. If you are on an earlier release of Oracle though, you can accomplish the same thing by creating […]
[…] In 11g this is very easy as there is a built function (psuedo documented) to flush a single SQL statement from the shared pool (see my previous post – Flush A Single SQL Statement – for details). […]
I just realized that in some instances, the upgrade from 10.2.0.3 to 10.2.0.4 does not automatically create the DBMS_SHARED_POOL package. The file $ORACLE_HOME/rdbms/admin/dbmspool.sql should be there. But you may need to execute it manually to create the package. Like so:
> cd $ORACLE_HOME/rdbms/admin
[homer:osborne:LAB1024] /u01/app/oracle/product/lab1024/10.2.0/rdbms/admin
> !sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 18 08:32:54 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbmspool
Package created.
Grant succeeded.
View created.
Package body created.
Kerry
[…] http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/ Comments (3) […]
Kerry – has anyone ever indicated that the pbod for dbms_shared_pool becomes invalidated with the 10.2.0.4.0 patch or 11g dbmspool.sql is loaded? I came upon your post during a production issue we are having with bad plans on peeked binds. This seems like the perfect solution if I could get it to work. I have recreated this both on HPUX and Linux.
sys@BKUP> @dbmspool11g.sql
Package created.
Grant succeeded.
sys@BKUP> exec dbms_shared_pool.purge(‘0000000181E6C1F0,3606220645’, ‘C’, 1);
BEGIN dbms_shared_pool.purge(‘0000000181E6C1F0,3606220645’, ‘C’, 1); END;
*
ERROR at line 1:
ORA-04063: package body “SYS.DBMS_SHARED_POOL” has errors
ORA-06508: PL/SQL: could not find program unit being called: “SYS.DBMS_SHARED_POOL”
ORA-06512: at line 1
No, I haven’t run into that problem. Sorry. The file that I executed was called called dbmspool.sql (the back ported version) not dbmspool11g.sql. Might try checking with support or the other “trick” way using the create outline, drop outline method I talked about here:
http://kerryosborne.oracle-guy.com/2008/12/flush-a-single-sql-statement-take-2/.
(although that method is a bit flaky due to using Outlines)
Kerry
The script dbmspool11g.sql only creates the package specification, not the body, and it does invalidate the current package body because it doesn’t match the current spec. Look inside the script and you’ll find only a CREATE OR REPLACE PACKAGE … command; there will also be a .plb file in the same location which will create the package body to match the specification. The ?/rdbms/admin/dbmspool.sql script which Kerry used creates the package spec and calls the wrapped script to create the corresponding package body. All I see in your posted output is ‘Package created.’; there should also be the following messge:
“Package body created.”
for this to function correctly.
If you can’t find a corresponding .plb file for the dbmspool11g.sql script I suggest you run the listed dbmspool.sql script to get the spec and body created correctly.
Thanks David.
You’re exactly right. There is another script that gets run by dbmspool.sql (prvtpool.plb).
I see the “Package body created.” message in a previous comment, but at any rate I just re-ran it. Here’s the output I got:
SQL> @dbmspool
Package created.
Grant succeeded.
View created.
Package body created.
Thank you for your blog.
hi,
this is really nice to know. I have another question, suppose I have sql which I want to keep in shared pool permanently like we pin the procedures.
is it possible ?
Rich
Rich,
Yes – dbms_shared_pool.keep is supposed to be able to pin individual SQL statements as well. Here’s a couple of scripts. The first one takes a sql_id and pins the statement. The second one shows all the pinned statements.
SYS@LAB112> !cat pin_sql.sql
set serveroutput on
set pagesize 9999
set linesize 155
accept sql_id –
prompt ‘Enter value for sql_id: ‘
DECLARE
name1 varchar2(30);
sql_string varchar2(300);
BEGIN
select address||’, ‘||hash_value into name1
from v$sqlarea
where sql_id like ‘&&sql_id’;
dbms_output.put_line(name1);
sys.dbms_shared_pool.keep(name1, ‘C’);
dbms_output.put_line(‘SQL_ID ‘||’&&sql_id’||’ pinned.’);
END;
/
undef sql_id
======================
SYS@LAB112> !cat fs_pinned.sql
col name for a60 wrap
SELECT distinct sql_id, oc.name, oc.type
FROM v$DB_OBJECT_CACHE oc, v$sql sa
WHERE oc.hash_value = sa.hash_value
and kept = ‘YES’
AND TYPE = ‘CURSOR’
/
Kerry – few words: YOU’re a GENIUS !!
Thanks for sharing your knowledge on this blog.
Have you encountered any issues with this in 11.2.0.1? Oracle seems to flush only the last child cursor, not the original one that parsed the query. The package body is there an valid and I’m not getting any errors out of the procedure.
Lisa,
No I have not run into that issue. But the dbms_shared_pool.purge procedure is very lightly documented. The last parameter is called HEAPS. I have used a value of 1 which seems to dump everything. It may be that there is something unusual about your particular cursor and that a different HEAPS value would cure. So you might try messing with that. Or it may be that the child cursor is still in use and thus preventing it from being flushed. This is probably more likely.
Kerry
Just what I was looking for. Thanks, Kerry!
Thanks !! .
Hi Kerry,
will dbms_shared_pool.purge remove the sql even if its ‘active’ ? What happens when another session is running the same query while the purge is executed ? Does it still purge the sql from the cache ? or wait for the other query to finish ? or does NOT purge it at all ?
Hi Percy,
No it won’t flush active statements although it appears to mark them to be flushed when they become inactive (not sure where the bit is set for this as it doesn’t appear to be exposed in v$sql_shared_cursor in 11.2.0.3 even though there is PURGED_CURSOR flag in that view).
Kerry
[…] OLTP_GRP (visible from v$session). I flushed the statement from the shared pool (as described here for example), when that didn’t help I flushed the flushed the whole shared pool (in my […]
[…] Kerry Osborne : Flush A Single SQL Statement […]
[…] Kerry Osborne : Flush A Single SQL Statement […]
[…] the sql from the shared pool (See Kerry Osborne’s post) so that the next execution will generate a hard […]
Hey, I am using 10.2.0.3, i would like to purge for a single SQL statement from shared pool. What is your suggestion?
Hi Subbu,
I have blogged about some techniques for doing that here: http://kerryosborne.oracle-guy.com/2008/12/flush-a-single-sql-statement-take-2/
Kerry
Hi Kerry
We have been using the script to flush sql_id in our shop. I thought if there is a extended version of this script that would allow to flush sql_id from local node for a different node (for example from node 1 flush the sql_id on node X). The reason is simple, we want to automate this and when someone slaps a sql profile on a sql_id we want to flush the sql_id on all RAC nodes to ensure the new plan is picked up across the board. Logging into different nodes to achieve this seems to be time inefficient. I am thinking of writing a shell script to achieve this, but wondering if there is one out there already.
Hi Dina,
I have not written such a script. Might be an interesting little project. You shouldn’t need to do this for SQL Profiles (or Patches or Baselines) though. When you create (or drop such an object) the cursors in other nodes should be invalidated, this may be version dependent but I haven’t had any issues with it in a long time. In fact, the last time I had such an issue was using Outlines in early 10g I think (or maybe it was 9i). Not that flushing/invalidating cursors across a large number of RAC nodes might not be usual for other purposes. One idea might be to create a SQL Patch (with a nonsensical hint text) and then drop it. This could be run from a single node and should invalidate cursors across the cluster, although it wouldn’t necessarily flush all children.
Kerry
Thanks for sharing this and the scripts. Just a simple question, each time you call the script “find_sql” it asked for input “address” but I don’t see the parameter anywhere in the script. Would you explain a little bit?
Eugene