dbms_shared_pool

dbms_shared_pool提供以下功能:将object 或者 "sql statement" pin到shared pool......

keep过程可以将对象pin入shared_pool,而不进入LRU 机制;

unkeep过程将pinned对象unpin,从而进入LRU机制;

SQL> alter system flush shared_pool;

System altered.

SQL> select name,kept
2 from v$db_object_cache
3 where type='PROCEDURE';

no rows selected

SQL> exec dbms_shared_pool.keep('P_TEST','P');

PL/SQL procedure successfully completed.

SQL> select name,kept
2 from v$db_object_cache
3 where type='PROCEDURE';

NAME KEP
---------- ---
P_TEST YES

SQL> alter system flush shared_pool;

System altered.

SQL> select name,kept
2 from v$db_object_cache
3 where type='PROCEDURE';

NAME KEP
---------- ---
P_TEST YES

SQL> exec dbms_shared_pool.unkeep('P_TEST','P');

PL/SQL procedure successfully completed.

SQL> select name,kept
2 from v$db_object_cache
3 where type='PROCEDURE';

NAME KEP
---------- ---
P_TEST NO

SQL> select '2007 test' from dual;

'2007TEST
---------
2007 test

SQL> select sql_text,address,hash_value
2 from v$sqlarea
3 where sql_text = 'select ''2007 test'' from dual';

SQL_TEXT ADDRESS HASH_VALUE
------------------------------ -------- ----------
select '2007 test' from dual 670B0FF0 1310245235

SQL> exec dbms_shared_pool.keep('670B0FF0,1310245235','W');

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_text,address,hash_value
2 from v$sqlarea
3 where sql_text = 'select ''2007 test'' from dual';

SQL_TEXT ADDRESS HASH_VALUE
------------------------------ -------- ----------
select '2007 test' from dual 670B0FF0 1310245235

SQL> exec dbms_shared_pool.unkeep('670B0FF0,1310245235','W');

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_text,address,hash_value
2 from v$sqlarea
3 where sql_text = 'select ''2007 test'' from dual';

no rows selected

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134922/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134922/