如何將oracle 物件pin在共享池中

dbms_shared_pool.keep 可以將物件pin入shared_pool,而不進入LRU 機制被keep的物件可以是資料庫物件，也可以是sql
dbms_shared_pool.unkeep為反操作。
實驗如下：（實驗環境 11.2.0.1）
執行一個sql，並檢視其在shared_pool 中的address和hash_value值。
count(*) from yang_a;
  COUNT(*)
----------
        29
address,hash_value,sql_text from v$sqlarea where sql_text like 'select count(*) from yang_a%';
ADDRESS          HASH_VALUE  SQL_TEXT
---------------- ----------- ------------
00000001736C9E48 1083615814  select count(*) from yang_a

address,hash_value,executions,parse_calls from v$sql where sql_text like 'select count(*) from yang_a%';
ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
00000001736C9E48 1083615814          1           1
keep該sql語句的執行計劃到shared_pool。
sys.dbms_shared_pool.keep('00000001736C9E48,1083615814','C');
PL/SQL procedure successfully completed.
在v$db_object_cache 中查詢kept欄位為yes ，說明該物件已經被儲存！
owner,name,kept from v$db_object_cache where hash_value='1083615814';
OWNER      NAME                                     KEP
---------- ---------------------------------------- ---
           select count(*) from yang_a              YES
           select count(*) from yang_a              YES

COUNT(*) FROM v$sql WHERE hash_value='1083615814';
  COUNT(*)
----------
         1
因為已經被儲存了，所以執行刪除hash_value值為1083615814 時候報錯，
SYS.dbms_shared_pool.purge('00000001736C9E48,1083615814','C');
BEGIN SYS.dbms_shared_pool.purge('00000001736C9E48,1083615814','C'); END;

*
ERROR at line 1:
ORA-06596: object cannot be  purged, object is permanently kept in shared pool
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 31
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 77
ORA-06512: at line 1

對hash_value值為1083615814 執行計劃進行unkeep
sys.dbms_shared_pool.unkeep('00000001736C9E48,1083615814','C');
PL/SQL procedure successfully completed.
再次刪除
SYS.dbms_shared_pool.purge('00000001736C9E48,1083615814','C');
PL/SQL procedure successfully completed.
COUNT(*) FROM v$sql WHERE hash_value='1083615814';
  COUNT(*)
----------
         0
address,hash_value,executions,parse_calls from v$sql where sql_text like 'select count(*) from yang_a%';
no rows selected
address,hash_value,sql_id FROM v$sql_plan WHERE hash_value='1083615814';
no rows selected

演示 keep對於佇列的作用。
建立佇列，預設為佇列在記憶體中的cache 為20.
sequence seq_yang;
Sequence created.
seq_yang.nextval from dual;

   NEXTVAL
----------
         1
清除cache在記憶體中的佇列的值。
system flush shared_pool;
System altered.

seq_yang.nextval from dual;
   NEXTVAL
----------
        21
nextval為21 表示，隨著flush 共享池，sequence的cache被清空了。
將佇列keep在快取中，
sys.dbms_shared_pool.keep('seq_yang','q');
PL/SQL procedure successfully completed.

seq_yang.nextval from dual;
   NEXTVAL
----------
        22
然後再次flush 共享池。
system flush shared_pool;
System altered.
檢視nextval的值，結果：
seq_yang.nextval from dual;
   NEXTVAL
----------
        23
說明keep起作用了！被keep在共享池中的物件不會被flush 操作清除。
sys.dbms_shared_pool.unkeep('seq_yang','q');
PL/SQL procedure successfully completed.
seq_yang.nextval from dual;
   NEXTVAL
----------
        24
system flush shared_pool;
System altered.
seq_yang.nextval from dual;
   NEXTVAL
----------
        41
相信到這裡，我們可以對dbms_shared_pool.keep /unkeep 的作用有了初步的瞭解。^ _ ^