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!

Leave a Reply