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