http://psoug.org/reference/dbms_shared_pool.html
DBMS_SHARED_POOL source code:
/rdbms/admin/dbmspool.sql
DBMS_SHARED_POOL Related Views:
V$DB_OBJECT_CACHE
V$SQLAREA
X$KGLOB
DBMS_SHARED_POOL.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);
DBMS_SHARED_POOL.KEEP
- Pin A Cursor In Memory
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 / as sysdba GRANT select ON gv_$open_cursor TO testuser; conn testuser/testuser -- 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 / 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 = 'testuser';
CREATE OR REPLACE PROCEDURE testproc IS
BEGIN
NULL;
END testproc;
/
exec sys.dbms_shared_pool.keep('testproc', 'P');
conn / as sysdba
col owner format a30
SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'testuser';
- Pin A Sequence In Memory
conn / as sysdba
SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'testuser';
conn testuser/testuser
CREATE SEQUENCE seq_test;
exec sys.dbms_shared_pool.keep('seq_test', 'Q');
conn / as sysdba
SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'testuser';
- Pin A Trigger In Memory
conn testuser/testuser
SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'testuser';
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 / as sysdba
SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'testuser';
DBMS_SHARED_POOL.PURGE
Purge the named object or particular heap(s) of the object
dbms_shared_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.
conn testuser/testuser
CREATE OR REPLACE PROCEDURE testproc IS
BEGIN
NULL;
END testproc;
/
conn / as sysdba
exec dbms_shared_pool.purge('testuser.TESTPROC');
DBMS_SHARED_POOL.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);
DBMS_SHARED_POOL.UNKEEP
Unpin an object from memory
dbms_shared_pool.unkeep(name IN VARCHAR2, flag IN CHAR DEFAULT 'P');
exec dbms_shared_pool.unkeep('testuser.TESTPROC', 'P');
Query pinned objects
To find information on pinned cursors
SELECT address, hash_value
FROM gv$sqlarea
WHERE sql_text LIKE '%<name_from_v$db_object_cache%';