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