SQL> desc sys.dbms_shared_pool
PROCEDURE ABORTED_REQUEST_THRESHOLD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
THRESHOLD_SIZE NUMBER IN
PROCEDURE KEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
PROCEDURE SIZES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MINSIZE NUMBER IN
PROCEDURE UNKEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
SQL>
也就是说如果我们想要keep一个sql的话
我们可以查询 v$sqlarea
查到 'address' and 'hash_value'
然后执行 dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
这样就把该 cursor keep 在内存中
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 ABORTED_REQUEST_THRESHOLD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
THRESHOLD_SIZE NUMBER IN
PROCEDURE KEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
PROCEDURE SIZES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MINSIZE NUMBER IN
PROCEDURE UNKEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
SQL>
也就是说如果我们想要keep一个sql的话
我们可以查询 v$sqlarea
查到 'address' and 'hash_value'
然后执行 dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
这样就把该 cursor keep 在内存中
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.