當系統在載入PL/SQL大物件時，有可能遭遇由於Shared Pool中存在大量碎片導致沒有足夠空間載入的問題。
我們可以將那些經常被使用的PL/SQL大物件預先儲存到Shared Pool中，防止載入失敗以及經常被換出，提高系統執行效率。

1.建立儲存過程初始化測試環境
這裡使用文章《【PL/SQL】向表中插入連續數字之PL/SQL方法》（http://space.itpub.net/519536/viewspace-696181）中提供的方法建立一個簡單的儲存過程用於測試。
注：實際生產環境中，該方法用於儲存PL/SQL大物件，而非本例中給出的樣例儲存過程。
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> create or replace procedure p_insert
2 is
3 begin
4 for i in 1..10 loop
5    insert into t values(i);
6   end loop;
7   commit;
8 end;
9 /

Procedure created.

2.建立DBMS_SHARED_POOL包
系統預設情況下不會建立dbms_shared_pool這個包。我們可以使用dbmspool.sql指令碼進行手工建立。
1）驗證預設情況下DBMS_SHARED_POOL包未建立
sys@ora10g> desc dbms_shared_pool
ERROR:
ORA-04043: object dbms_shared_pool does not exist

2）執行建立DBMS_SHARED_POOL包指令碼
sys@ora10g> @?/rdbms/admin/dbmspool.sql

Package created.

Grant succeeded.

View created.

Package body created.

3）檢視建立成功的DBMS_SHARED_POOL包
sys@ora10g> 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包中的KEEP和UNKEEP方法。

3.使用DBMS_SHARED_POOL包將儲存過程儲存到Shared Pool
1）確認儲存過程P_INSERT是否被儲存到Shared Pool
可以透過查詢v$db_object_cache檢視獲得這方面的資訊。
sys@ora10g> col owner for a10;
sys@ora10g> col name for a30;
sys@ora10g> col kept for a4
sys@ora10g> select owner,name,type,kept from v$db_object_cache where name='P_INSERT';

OWNER      NAME                           TYPE                 KEPT
---------- ------------------------------ -------------------- ----
SEC        P_INSERT                       PROCEDURE            NO

最後一列KEPT表明儲存過程P_INSERT此時沒有被儲存到Shared Pool。

2）儲存儲存過程P_INSERT到Shared Pool
由於儲存過程p_insert是建立在sec使用者下，我們可以使用如下方法將該儲存過程儲存到Shared Pool中。
sys@ora10g> exec dbms_shared_pool.keep('SEC.P_INSERT','P');

PL/SQL procedure successfully completed.

3）在此確認儲存過程P_INSERT是否被儲存到Shared Pool
sys@ora10g> select owner,name,type,kept from v$db_object_cache where name='P_INSERT';

OWNER      NAME                           TYPE                 KEPT
---------- ------------------------------ -------------------- ----
SEC        P_INSERT                       PROCEDURE            YES

最後一列KEPT表明儲存過程P_INSERT此時已被儲存到Shared Pool。

4.使用DBMS_SHARED_POOL包解除Shared Pool中儲存的儲存過程
我們這裡使用UNKEEP方法可以解除Shared Pool中儲存的儲存過程。
sys@ora10g> exec dbms_shared_pool.unkeep('SEC.P_INSERT','P');

PL/SQL procedure successfully completed.

sys@ora10g> select owner,name,type,kept from v$db_object_cache where name='P_INSERT';

OWNER      NAME                           TYPE                 KEPT
---------- ------------------------------ -------------------- ----
SEC        P_INSERT                       PROCEDURE            NO

解除成功。

5.10g官方文件中關於DBMS_SHARED_POOL包的描述
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_shpool.htm#ARPLS055

6.小結
本文給出了使用DBMS_SHARED_POOL包將PL/SQL大物件儲存到Shared Pool的方法。
DBMS_SHARED_POOL包除了可以實現儲存儲存過程到Shared Pool外，還可以儲存其他資料庫物件，例如SQL語句、Package、Function、Type、Trigger、Sequence等，具體用法和介紹參見官方文件。

Good luck.

secooler
11.05.24

-- The End --

【Shared Pool】使用DBMS_SHARED_POOL包將PL/SQL大物件儲存到Shared Pool

相關文章