使用DBMS_SHARED_POOL包將物件固定到共享池

-******************************************

-- 使用DBMS_SHARED_POOL包將物件固定到共享池

--******************************************

   

    DBMS_SHARED_POOL包提供儲存過程來將PL/SQL物件或SQL遊標固定到Oracle 共享池。一旦這些物件固定之後，將不再參與aged out,而

是常駐記憶體，即便是使用alter system flush shared_pool也不會將物件清除出共享池。

   

    對於一些大值物件裝載進共享池時容易引發兩種型別的問題：

        ORA-04031 errors 由於沒有足夠的記憶體引發該類似的錯誤

        為大值對像尋找可用的空間而引發系統效能下降

    將大值物件在例項啟動時裝載進共享池可以避免上述問題。

   

    對於已經固定在記憶體中的包，在關閉資料庫之前，該物件會被一直保留，不會清除或失效。

        需要訪問DBMS_SHARED_POOL這個包的任何使用者都必須由SYS授予執行許可權。

        

        如果在SYS模式中建立的包並在不同的模式中執行示例程式碼，則首先必須給執行示例(即TEST)的使用者授予EXECUTE_CATALOG_ROLE

    角色且在DBMS_SHARED_POOL上給TEST以EXECUTE許可權，然後需要在SYS.DBMS_SHARED_POOL.KEEP中完全地限定這個包，因為dbmspool.sql

    指令碼並不為這個包建立公有同義詞。   

   

一、安裝(DBMS_SHARED_POOL預設並沒有隨系統安裝)

        要使用這個過程，首先必須執行DBMSPOOL.SQL指令碼。在啟動DBMSPOOL.SQL指令碼後，PRVTPOOL.PLB指令碼將自動執行。這些指令碼不能

        使用CATPROC.SQL來執行。

       

    1.檢視版本資訊

        SQL> select * from v$version;

       

        BANNER

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

        Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

        PL/SQL Release 10.2.0.4.0 - Production

        CORE    10.2.0.4.0      Production

        TNS for Linux: Version 10.2.0.4.0 - Production

        NLSRTL Version 10.2.0.4.0 - Production

 

    2.以sys帳戶安裝DBMS_SHARED_POOL包

        SQL> show user;

        USER is "SYS"

        SQL> @?/rdbms/admin/dbmspool.sql

       

        Package created.

           

        Grant succeeded.

       

        View created.

           

        Package body created.

   

    3.檢視包包含的儲存過程

        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 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

 

二、DBMS_SHARED_POOL包的使用

    1.DBMS_SHARED_POOL.KEEP 儲存過程

        該過程用於將物件固定到共享池

       

        PROCEDURE DBMS_SHARED_POOL.KEEP (name IN VARCHAR2 ,flag IN CHAR DEFAULT 'P');

       

            Flag標誌                      Description

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

            C                              cursor                              

            JC                             java class                          

            JD                             java shared data                    

            JR                             java resource                       

            JS                             java source                         

            P                              Package, procedure, or function name

            Q                              sequence                            

            R                              trigger                             

            T                              type                                 

            Any other character             Cursor specified by address and hash value        

        e.g.

            exec sys.dbms_shared_pool.keep('SYS.STANDARD');

            exec sys.dbms_shared_pool.keep('scott.tri_test','T')   

                    

           

    2.DBMS_SHARED_POOL.UNKEEP 儲存過程

        從過程的描述即可以知道，該過程用於將物件從清出保留池

        e.g.

            exec sys.dbms_shared_pool.unkeep('SYS.STANDARD','P')

           

    3.DBMS_SHARED_POOL.SIZES 儲存過程

        該過程顯示在共享池中超過指定值大小的物件，包括遊標以及匿名的PL/SQL塊。(指定值的大小的單位為kbytes)

       

        PROCEDURE DBMS_SHARED_POOL.SIZES (minsize IN NUMBER);

        e.g.

            execute sys.dbms_shared_pool.sizes(70);

           

    4.ABORTED_REQUEST_THRESHOLD儲存過程

            該過程可以設定一個闕值尺寸，當該闕值被設定後，一個大於該設定值的物件被裝載到共享池時，在共享池沒有足夠的空間，

        且設定了Oracle動態清空未固定在記憶體的物件，可以避免該類事件的發生。但是將收到一個錯誤ORA-4031，而不會清空共享池為

        該物件騰出空間。

       

            該值在5000 - 2147483647之間，

       

            該闕值的設定可以避免由於共享池空間壓力而導致的系統效能下降，但同時導致了ORA-4031錯誤的機率。DBA也可以根據ORA-4031

        錯誤來將特定的大值物件固定了保留池。

               

        PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (threshold_size IN NUMBER); 

       

            execute SYS.DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD(50000);

 

三、將物件自動固定到保留池方案

        將物件固定到保留池的最佳時間是Oracle例項首次啟動之後，因此此時共享池空閒空間較多，且幾乎沒有記憶體碎片。

        下面建立一張表以及一個儲存過程用於來實現例項自動啟動後將大值物件固定到保留池

       

        1.首先建立一張表，用於儲存需要pin到保留池的物件

            CREATE TABLE keep_objects

            (obj_schema VARCHAR2(30) NOT NULL ,

             obj_name VARCHAR2(30) NOT NULL ,

             CONSTRAINT ko_PK PRIMARY KEY (obj_schema, obj_name)

            )

            TABLESPACE USERS STORAGE (INITIAL 2 NEXT 2 PCTINCREASE 0);

 

        2.建立儲存過程用於將物件pin到保留池

            CREATE OR REPLACE PROCEDURE object_keeper

                --Procedure to pin objects into the shared pool

                --using DBMS_SHARED_POOL.KEEP procedure. All

                --objects found in the keep_objects table will be KEEPed.

                --For best results, procedure should be created in the SYS schema.

                --Author: John Beresniewicz, Savant Corp

                --Created: 09/18/97

                -- Compilation Requirements:   --注意許可權問題

                --SELECT on SYS.DBA_OBJECTS || EXECUTE on SYS.DBMS_SHARED_POOL ||

                --Execution Requirements:

                --Some SYS objects may get ORA-1031 unless the procedure is run by SYS

             IS

                CURSOR keep_objects_cur IS

                    SELECT do.owner || '.' || do.object_name OBJECT

                          ,decode(do.object_type,

                              'PACKAGE' , 'P',

                              'PROCEDURE' ,'P',

                              'FUNCTION'  ,'P',

                              'TRIGGER'   ,'R',

                              NULL) TYPE

                    FROM   keep_objects ko, dba_objects do

                    WHERE  upper(ko.obj_schema) = do.owner

                           AND upper(ko.obj_name) = do.object_name

                           AND do.object_type IN

                           ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER');

            BEGIN

                FOR ko_rec IN keep_objects_cur

                LOOP

                    BEGIN

                        sys.dbms_shared_pool.keep(ko_rec.object, ko_rec.type);

                        dbms_output.put_line('KEPT: ' || ko_rec.object);

                    EXCEPTION

                        WHEN OTHERS THEN

                            dbms_output.put_line(SQLERRM);

                            dbms_output.put_line('KEEP FAIL: ' ||

                                         ko_rec.object || ' ' ||

                                         ko_rec.type);

                    END;

                END LOOP;

            END object_keeper;

            /

 

        3.建立觸發器用於例項啟動後將物件pin到保留池(提示，先應當尋找需要pin住的物件且將其插入到表keep_objects中)

            CREATE OR REPLACE TRIGGER tr_object_keeper

                AFTER startup ON DATABASE

            BEGIN

                sys.object_keeper;

            END;

            /

 

四、使頻繁的大值物件常駐共享池

    1.首先尋找需要常駐共享池的物件

        SELECT *

        FROM v$db_object_cache

        WHERE sharable_mem > 10000      /*此引數為佔住記憶體的大小，可自行設定大小*/

        AND (TYPE='PACKAGE' OR TYPE='PACKAGE BODY' OR TYPE='FUNCTION' OR TYPE='PROCEDURE')

        AND kept='NO';   

       

    2.將物件常駐記憶體   

        使用包dbms_shared_pool.keep將這些物件常駐記憶體,儘可能在例項啟動後實施操作,因為此時記憶體比較空閒，不會因為記憶體不足導

        致aged out。

            EXECUTE dbms_shared_pool.keep('package_name');   

       

    3.將SQL語句常駐記憶體

        對於單獨的SQL語句，且被經常使用，同樣可以將其常駐記憶體。

        此時，需要得到SQL語句的hash值，我們可以透過$sqlarea裡的address和hash_value列獲得

       

        SQL> select count(*) from all_objects;

 

        COUNT(1)

        --------

           40793

          

        SQL> select address,hash_value,sql_text from v$sqlarea where sql_text='select count(*) from all_objects';

       

        ADDRESS       HASH_VALUE SQL_TEXT

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

        2D33FF58      789896629 select count(*) from all_objects 

       

        SQL> exec sys.dbms_shared_pool.keep('2D33FF58,789896629','C');

       

        PL/SQL procedure successfully completed.

       

        如果我們要取消固定到記憶體的話，則呼叫DBMS_SHARED_POOL.UNKEEP即可，該過程的引數與KEEP相同。

       

    4.清空share pool的命令(如果在使用包keep物件沒有可用空間時，可以flush shared_pool)

        ALTER SYSTEM FLUSH SHARED_POOL    --此操作不會清除常駐記憶體的物件

       

    5.檢視當前已經常駐記憶體的物件

        select * from v$db_object_cache where kept='YES'       

 

    6.尋找較大匿名的PL/SQL 塊將其分割為小的PL/SQL塊，以提高共享池的利用率

        SELECT sql_text

        FROM v$sqlarea

        WHERE command_type=47

        AND LENGTH(sql_text)>500;

 

五、下列標準的系統包建議將其pin到保留池

    通常下列兩種情形將物件固定在保留池

    1.頻繁使用的包應       -->這些物件固定在SGA中將大大提高效能

    2.一些Oracle的標準包   -->避免過多的硬解析

 

        DBMS_ALERT         DBMS_DESCRIBE

        DBMS_DDL           DBMS_LOCK

        DBMS_OUTPUT        DBMS_PIPE

        DBMS_SESSION       DBMS_SHARED_POOL

        DBMS_STANDARD      DBMS_UTILITY

        STANDARD

 

六、實戰演練           

    1.以sys as sysdba帳戶安裝DBMS_SHARED_POOL包

    2.建立使用者並授予許可權

        CREATE USER tester

        IDENTIFIED BY password

        DEFAULT TABLESPACE users

        TEMPORARY TABLESPACE temp

        QUOTA UNLIMITED ON users;

       

        GRANT

         CREATE SESSION,

         CREATE PROCEDURE,

         EXECUTE_CATALOG_ROLE

        TO tester;

       

        GRANT

         EXECUTE ON DBMS_SHARED_POOL

        TO tester;       

   

    3.以tester身份建立過程

        sys@ORCL> conn tester/password

        Connected.

        tester@ORCL> CREATE OR REPLACE PROCEDURE p1 AS

          2  BEGIN

          3   NULL;

          4  END p1;

          5  /

       

        Procedure created.

       

        tester@ORCL> BEGIN

          2   SYS.DBMS_SHARED_POOL.KEEP('P1','P');

          3  END;

          4  /

       

        PL/SQL procedure successfully completed.   

       

    4.以sys身份查詢當前pin住的物件

        sys@ORCL> set linesize 180

        sys@ORCL> col owner format a20

        sys@ORCL> col name format a40

        sys@ORCL> col type format a15

        sys@ORCL> col namespace format a30

        sys@ORCL> select owner,name,type,namespace from v$db_object_cache

          2  where kept='YES' and type!='INVALID TYPE' and owner='TESTER';

       

        OWNER                NAME                                     TYPE            NAMESPACE

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

        TESTER               P1                                       PROCEDURE       TABLE/PROCEDURE   

       

    5.使用alter system flush shared_pool清空共享池，從下面的查詢中可知，被pin住的對像並沒有被aged out。   

        sys@ORCL> alter system flush shared_pool;

       

        System altered.

       

        sys@ORCL> select owner,name,type,namespace from v$db_object_cache

          2  where kept='YES' and type!='INVALID TYPE' and owner='TESTER';

       

        OWNER                NAME                                     TYPE            NAMESPACE

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

        TESTER               P1                                       PROCEDURE       TABLE/PROCEDURE   

       

    6.使用DBMS_SHARED_POOL.SIZES顯示超出指定大小的物件

        sys@ORCL> execute sys.dbms_shared_pool.sizes(70)

        SIZE(K) KEPT   NAME

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

        429 YES    SYS.STANDARD                  (PACKAGE)

        388        SYS.DBMS_RCVMAN               (PACKAGE BODY)

        258        SYS.DBMS_BACKUP_RESTORE       (PACKAGE)

        239        SYS.DBMS_RCVMAN               (PACKAGE)

        149 YES    SYS.DBMS_SQL                  (PACKAGE)

        95        SYS.DBMS_BACKUP_RESTORE       (PACKAGE BODY)

       

        PL/SQL procedure successfully completed.   

       

    7.使用DBMS_SHARED_POOL.UNKEEP儲存過程將物件aged out.

        sys@ORCL> exec sys.dbms_shared_pool.unkeep('TESTER.P1','P')

       

        PL/SQL procedure successfully completed.

 

        sys@ORCL> select owner,name,type,namespace from v$db_object_cache

          2  where kept='YES' and type!='INVALID TYPE' and owner='TESTER';

       

        no rows selected   

   

    8.查詢當前library cache中pin住的物件

        set linesize 180

        col owner format a20

        col name format a30

        col type format a15

        col namespace format a30

        sys@ORCL> select owner,name,type,namespace from v$db_object_cache where kept='YES' and type!='INVALID TYPE';

       

        OWNER                NAME                           TYPE            NAMESPACE

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

        SYS                  STANDARD                       PACKAGE         TABLE/PROCEDURE

        SYS                  IND_STATS$                     TABLE           TABLE/PROCEDURE

        SYS                  CON$                           TABLE           TABLE/PROCEDURE

        SYS                  CLU$                           TABLE           TABLE/PROCEDURE

        SYS                  I_OBJ#_INTCOL#                 INDEX           INDEX

        SYS                  C_TS#                          CLUSTER         CLUSTER

        SYS                  HISTGRM$                       TABLE           TABLE/PROCEDURE

        SYS                  HIST_HEAD$                     TABLE           TABLE/PROCEDURE

        SYS                  C_FILE#_BLOCK#                 CLUSTER         CLUSTER

       

    9.清除tester使用者及其資料

        sys@ORCL> drop user tester cascade;