ITPub博客

首页 > 数据库 > Oracle > 使用DBMS_SHARED_POOL包将对象固定到共享池

使用DBMS_SHARED_POOL包将对象固定到共享池

Oracle 作者:数据与人 时间:2021-04-20 16:27:07 0 删除 编辑

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


-- 使用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;


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

请登录后发表评论 登录
全部评论
聚焦技术与人文,分享干货,共同成长!公号:数据与人(shujuyr)

注册时间:2021-04-06

  • 博文量
    25
  • 访问量
    8074