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