使用dbms_shared_pool包將物件pin到記憶體中

sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 27 17:18:20 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.執行dbmspool指令碼建立dbms_shared_pool包，預設不存在。
SQL> @?/rdbms/admin/dbmspool

Package created.

Grant succeeded.

View created.

Package body created.

2.執行許可權授予test使用者。
SQL> grant execute on dbms_shared_pool to test;

Grant succeeded.

SQL> connect test/test
Connected.

3.建立一個測試儲存過程。
SQL> create or replace procedure test.t111
as
t date;
begin
select sysdate into t from dual;
dbms_output.put_line(t);
end t111;
/  2    3    4    5    6    7    8

Procedure created.

4.將t111儲存過程pin到記憶體中。
SQL> exec sys.dbms_shared_pool.keep('t111');

PL/SQL procedure successfully completed.

5.檢視是否pin成功。
SQL> COL OWNER FORMAT A10;
SQL> COL NAME FORMAT A30;
SQL> select OWNER,NAME,TYPE,KEPT from V$db_Object_Cache where name='T111';

OWNER      NAME                           TYPE                         KEP
---------- ------------------------------ ---------------------------- ---
TEST       T111                           PROCEDURE                    YES

6.檢視sys.dbms_shared_pool定義。
SQL> desc sys.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 PURGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 FLAG                           CHAR                    IN     DEFAULT
 HEAPS                          NUMBER                  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

FLAG的值如下：
Value     Kind of Object to keep
-----        ----------------------
P          package/procedure/function(預設)
Q          sequence
R          trigger
T          type
JS         java source
JC         java class
JR         java resource
JD         java shared data
C          cursor

        dbms_shared_pool.purge的使用參考文章：http://space.itpub.net/471666/viewspace-616339