Oracle DBMS_SHARED_POOL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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
dbmsdbms_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);
CREATE OR REPLACE TRIGGER pin_markhot_objects
AFTER STARTUP ON DATABASE
BEGIN
  dbms_shared_pool.markhot(hash=>'01630e17906c4f222031266c21b49303',namespace=>0);
  dbms_shared_pool.markhot(hash=>'119df082543f104e29cad00ee793c8aa',namespace=>0);
  dbms_shared_pool.markhot(hash=>'251d24517d18ee7b2154e091b80e64d2',namespace=>0);
  dbms_shared_pool.markhot(hash=>'28104e170c4020b7d6991509b4886443',namespace=>0);
  dbms_shared_pool.markhot(hash=>'3362900d064bc7d9a1812303ea49391e',namespace=>0);
END;
/
 
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
Overload 1
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);
exec dbms_shared_pool.unmarkhot(hash=>'7eb6e0f357f73998ba9116f63f50f54e',namespace=>0);
 
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
Built-in Functions
Built-in Packages
Sequences
Tables
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved