CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SHARED_POOL
Version 11.1
 
General
Purpose Pin and unpin objects from memory
Source {ORACLE_HOME}/rdbms/admin/dbmspool.sql
First Available 10.1
Dependencies
DBMS_OUTPUT GV$DB_OBJECT_CACHE
DBMS_STANDARD GV$SQLAREA
DBMS_UTILITY X$KGLOB
Security Model EXECUTE is granted to the EXECUTE_CATALOG_ROLE role
GRANT EXECUTE ON dbms_shared_pool TO uwclass;
 
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
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 uwclass;


conn uwclass/uwclass

-- 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 = 'UWCLASS';

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 = 'UWCLASS';

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

conn uwclass/uwclass

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 = 'UWCLASS';

Pin A Trigger In Memory
conn uwclass/uwclass

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 / as sysdba

SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'UWCLASS';
 
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 uwclass/uwclass

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

conn / as sysdba

exec dbms_shared_pool.purge('UWCLASS.TESTPROC');
 
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
Unpin an object from memory dbms_shared_pool.unkeep(name IN VARCHAR2, flag IN CHAR DEFAULT 'P');
exec dbms_shared_pool.unkeep('UWCLASS.TESTPROC', 'P');
 
Query
To find information on pinned cursors SELECT address, hash_value
FROM gv$sqlarea
WHERE sql_text LIKE '%<name_from_v$db_object_cache%';
 
Related Topics
Sequences
Tables
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [256 users online]    © 2010 psoug.org