Wikipedia

Search results

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.
PurposePin and unpin objects in memory
AUTHIDDEFINER
Dependencies
DBMS_OUTPUTDBMS_UTILITYV$SQLAREA
DBMS_SQLTCB_INTERNALV$DB_OBJECT_CACHEX$KGLOB
DBMS_STANDARD
DocumentedYes
First Available10gR1
Security ModelOwned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source{ORACLE_HOME}/rdbms/admin/dbmspool.sql
Subprograms
ABORTED_REQUEST_THRESHOLD
Sets the aborted request threshold for the shared pooldbms_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 ValuesDescription
Ccursor
JCjava class
JDjava shared data
JRjava resource
JSjava source
Pprocedure
Qsequence
Rtrigger
Ttype
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 Memoryconn 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 Memoryconn 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 2dbms_shared_pool.keep(
schema    IN VARCHAR2,
objname   IN VARCHAR2,
namespace IN NUMBER,
heaps     IN NUMBER);
TBD
Overload 3dbms_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 2dbms_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 bytesdbms_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 3dbms_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 2dbms_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 cursorsSELECT address, hash_value
FROM gv$sqlarea
WHERE sql_text LIKE '%<name_from_v$db_object_cache%';

Troubleshooting Assistant: Resolve Issues with Oracle Undo Management (ORA-01555, ORA-30036, ORA-01628, ORA-01552, etc.)

Troubleshooting Assistant: Resolve Issues with Oracle Undo Management (ORA-01555, ORA-30036, ORA-01628, ORA-01552, etc.)

It save your time in searching related doc on ORA- issue fix.

Just try the Oracle Support doc assistance -

(Doc ID 1575667.2)  Troubleshooting Assistant