Keep Pin Object From Flush Out

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%';