| 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 |
GV$SQLAREA |
X$KGLOB |
|
| Object Privileges |
GRANT EXECUTE ON dbms_shared_pool TO <schema_name>; |
| 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 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'); |
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) |
dbms_shared_pool.keep(<procedure_name>, 'P'); |
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
SELECT owner, name, type
FROM gv$db_object_cache
WHERE kept = 'YES'
AND owner = 'UWCLASS'; |
| Pin A Sequence In Memory |
dbms_shared_pool.keep(<sequence_name>, 'Q'); |
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 |
dbms_shared_pool.keep(<trigger_name>, 'R'); |
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';
|
| |
| SIZES |
|
Shows What Is In The Shared Pool Larger Than A Specified Number Of 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); |
|
exec dbms_shared_pool.unkeep('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%'; |