Oracle DBMS_SHARED_POOL

Oracle DBMS_SHARED_POOL Version 12.2.0.1

General Information
Library Note	The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1.  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. Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture?  The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
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 PURGE UNMARKHOT KEEP SIZES Related Queries MARKHOT UNKEEP
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%';