SGA Keep
SGA Keep is only possible for the below type of objects
- PACKAGE
- PACKAGE BODY
- FUNCTION
- TRIGGER
- TYPE
- Cursor
TABLE & LIBRARY
Not possible
- Procedure , PACKAGE ,PACKAGE BODY & FUNCTION
SQL> exec dbms_shared_pool.keep(‘xxxxxxxx’, ‘p’);
- TYPE
SQL> exec dbms_shared_pool.keep(‘xxxxxxxx’, ‘t’);
- TRIGGER
SQL> exec dbms_shared_pool.keep(‘xxxxxxxx’, ‘r’);
- CURSOR
1. Issue SQL statement to load it into the shared pool.
2. Determine the ADDRESS and HASH_VALUE of the SQL statement. This can
usually be obtained from calling DBMS_SHARED_POOL.SIZES. For example,
in the output from the SIZES procedure above you can determine that:
ADDRESS = 70378A10
HASH_VALUE = -614308548
for the SQL statement:
select ename from emp where empno = 7788
If the information cannot be determined from SIZES, then check the view
V$SQLAREA.
3. Issue the following statement to keep the cursor:
SQL> exec dbms_shared_pool.keep(’70378A10, -614308548′, ‘c’)
PL/SQL procedure successfully completed.
