Oracle DBMS_SHARED_POOL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Pin and unpin objects in memory
AUTHID DEFINER
Dependencies
DBMS_OUTPUT DBMS_UTILITY V$SQLAREA
DBMS_SQLTCB_INTERNAL V$DB_OBJECT_CACHE X$KGLOB
DBMS_STANDARD    
Documented Yes
First Available 10gR1
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmspool.sql
Subprograms
 
ABORTED_REQUEST_THRESHOLD
Sets the aborted request threshold for the shared pool dbms_shared_pool.aborted_request_threshold(threshold_size IN NUMBER);

-- the range of threshold_size is 5000 to ~2 GB inclusive
exec dbms_shared_pool.aborted_request_threshold(100000000);
 
KEEP
Pin A Cursor In Memory

Overload 1
dbms_shared_pool.keep(name IN VARCHAR2, flag IN CHAR DEFAULT 'P');

Flag Values Description
C cursor
JC java class
JD java shared data
JR java resource
JS java source
P procedure
Q sequence
R trigger
T type
conn sys@pdbdev as sysdba

GRANT select ON gv_$open_cursor TO uwclass;

conn uwclass/uwclass@pdbdev

-- SQL statement to load cursor into the shared pool
VARIABLE x REFCURSOR

BEGIN
   OPEN :x for
   SELECT *
   FROM all_tables;
END;
/

--Determine address and hash value of the SQL statement
SELECT address, hash_value
FROM gv$open_cursor
WHERE sql_text LIKE '%ALL_TABLES%';

-- substitute your query results for mine, below
exec sys.dbms_shared_pool.keep('1C5B28DC, 3958201300', 'C');

conn sys@pdbdev as sysdba

SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND TYPE = 'CURSOR';
Pin A Package, Procedure Or Function In Memory (this is the default) SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'UWCLASS';

CREATE OR REPLACE PROCEDURE testproc IS
BEGIN
  NULL;
END testproc;
/

exec sys.dbms_shared_pool.keep('testproc', 'P');

conn sys@pdbdev as sysdba

col owner format a30

SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'UWCLASS';
Pin A Sequence In Memory conn sys@pdbdev as sysdba
SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'UWCLASS';

conn uwclass/uwclass@pdbdev

CREATE SEQUENCE seq_test;

exec sys.dbms_shared_pool.keep('seq_test', 'Q');

conn sys@pdbdev as sysdba

SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'UWCLASS';
Pin A Trigger In Memory conn uwclass/uwclass@pdbdev

SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'UWCLASS';

CREATE TABLE t (
testcol VARCHAR2(20));

CREATE OR REPLACE TRIGGER testtrig
BEFORE UPDATE
ON t
BEGIN
   NULL;
END testtrig;
/

exec sys.dbms_shared_pool.keep('testtrig', 'R');

conn sys@pdbdev as sysdba

SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'UWCLASS';
Overload 2 dbms_shared_pool.keep(
schema    IN VARCHAR2,
objname   IN VARCHAR2,
namespace IN NUMBER,
heaps     IN NUMBER);
TBD
Overload 3 dbms_shared_pool.keep(
hash      IN VARCHAR2,
namespace IN NUMBER,
heaps     IN NUMBER);
TBD
 
MARKHOT
Mark a library cache object as a hot object

Overload 1
dbms_shared_pool.markhot(
schema    IN VARCHAR2,
objname   IN VARCHAR2,
namespace IN NUMBER  DEFAULT 1,     -- library cache namespace to search
global    IN BOOLEAN DEFAULT TRUE); -- If TRUE mark hot on all RAC instances
TBD
Overload 2 dbms_shared_pool.markhot(
hash      IN VARCHAR2,              -- 16-byte hash value for the object
namespace IN NUMBER  DEFAULT 1,
global    IN BOOLEAN DEFAULT TRUE);
TBD
 
PURGE
Purge the named object or particular heap(s) of the object

Overload 1
dbms_shared_pool.purge(
name  IN VARCHAR2,
flag  IN CHAR   DEFAULT 'P',
heaps IN NUMBER DEFAULT 1);

Note: 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.

In some versions this may not work unless you set event 5614566 so
I have shown that in this demo. It is not necessary in 11gR1 or above.
conn sys@pdbdev as sysdba

alter session set events '5614566 trace name context forever';

SELECT /* find me */ COUNT(*)
FROM dba_tables t, dba_indexes i
WHERE t.table_name = i.table_name;

SELECT address, hash_value, sql_text
FROM v$sqlarea
WHERE sql_text LIKE '%find me%';

exec dbms_shared_pool.purge('385C52F8,943808449', 5'c');

SELECT address, hash_value, sql_text
FROM v$sqlarea
WHERE sql_text LIKE '%find me%';
Purge the named object or particular heap(s) of the object

Overload 2
dbms_shared_pool.purge(
schema    IN VARCHAR2,
objname   IN VARCHAR2,
namespace IN NUMBER,
heaps     IN NUMBER);
TBD
Purge the object or particular heap(s) of the object

Overload 3
dbms_shared_pool.purge(
hash      IN VARCHAR2,
namespace IN NUMBER,
heaps     IN NUMBER);
TBD
 
SIZES
Shows what is in the Shared Pool larger than a specified size in bytes dbms_shared_pool.sizes(minsize IN NUMBER);
set serveroutput on

exec dbms_shared_pool.sizes(500);
 
UNKEEP
Unkeep the named object dbms_shared_pool.unkeep(name IN VARCHAR2, flag IN CHAR DEFAULT 'P');
exec dbms_shared_pool.unkeep('UWCLASS.TESTPROC', 'P');
Unkeep an object in the shared pool

Overload 2
dbms_shared_pool.unkeep(
schema    IN VARCHAR2,
objname   IN VARCHAR2,
namespace IN NUMBER);
TBD
Overload 3 dbms_shared_pool.unkeep(
hash      IN VARCHAR2,
namespace IN NUMBER);
TBD
 
UNMARKHOT
Unmark a library cache object as a hot object

Overload 1
dbms_shared_pool.unmarkhot(
schema    IN VARCHAR2,
objname   IN VARCHAR2,
namespace IN NUMBER  DEFAULT 1,
global    IN BOOLEAN DEFAULT TRUE);
TBD
Overload 2 dbms_shared_pool.unmarkhot(
hash      IN VARHAR2,
namespace IN NUMBER  DEFAULT 1,
global    IN BOOLEAN DEFAULT TRUE);
TBD
 
Related Queries
Find information on pinned cursors SELECT address, hash_value
FROM gv$sqlarea
WHERE sql_text LIKE '%<name_from_v$db_object_cache%';

Related Topics
Packages
Sequences
Tables

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved