Oracle | DBMS_SHARED

Oracle DBMS_SHARED_POOL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Pin and unpin objects in memory

AUTHID DEFINER

Dependencies

DBMS_GSM_DBADMIN	DBMS_STANDARD	V$SQLAREA
DBMS_OUTPUT	DBMS_UTILITY	X$KGLOB
DBMS_SQLTCB_INTERNAL	V$DB_OBJECT_CACHE

Documented Yes

First Available 10.1

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE and GSMADMIN_INTERNAL roles

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