dbms_shared_pool keep物件到share pool中

dbms_shared_pool

--------------

預設情況下dbms_shared_pool包是不在系統中的
需要執行$ORACLE_HOME/rdbms/admin/dbmspool.sql進行建立
整個包有4個過程，如下

SQL> desc dbms_shared_pool
PROCEDURE ABORTED_REQUEST_THRESHOLD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THRESHOLD_SIZE                 NUMBER                  IN
PROCEDURE KEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT
PROCEDURE SIZES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MINSIZE                        NUMBER                  IN
PROCEDURE UNKEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT

---------------

dbms_shared_pool.aborted_request_threshold

設定報錯閥值，該值在5000 - 2147483647之間，
只要load的物件大小大於該值而shared pool中沒有滿足要求的空閒空間時，
不會從lru中flush物件出去，直接以一個ORA-4031錯誤結束。

---------------

dbms_shared_pool.sizes

需要設定set serveroutput on
列出當前share_pool中大於給定引數k的物件
如我的測試系統中只有一個大小為772k的java物件大於500k

SQL> exec dbms_shared_pool.sizes(500);
SIZE(K) KEPT   NAME
------- ------ ----------------------------------------------------
772 YES    SYS.oracle/gss/util/NLSLocale (JAVA CLASS)

PL/SQL procedure successfully completed.

-----------------

dbms_shared_pool.keep
dbms_shared_pool.unkeep

將物件pin入shared_pool,而不進入LRU 機制
被keep的物件可以是資料庫物件，也可以是sql
unkeep為反操作

型別代號關係如下
C cursor
JC java class
JD java shared data
JR java resource
JS java source
P procedure
Q sequence
R trigger
T type

如將procedure AN.p_a keep到share_pool中可以使用如下方法
exec dbms_shared_pool.keep('AN.P_A','p');

keep sql需要知道sql的addr和hash_value

SQL> select * from dual;

D
-
X

SQL> select address,hash_value,sql_text from v$sqlarea where sql_text='select * from dual';

ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ---------------------
689667FC  942515969 select * from dual

keep sql命令：
exec dbms_shared_pool.keep('689667FC,942515969','W');

已經被keep早share_pool中的物件和sql可以在v$db_object_cache中查詢
欄位kept為yes就是已經被keep的物件