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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

leshami 2011-06-24 09:45:00 浏览542
展开阅读全文

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

-- 使用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_POOLTESTEXECUTE限,然后需要在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里的addresshash_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 cachepin住的

        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;

   

        User dropped.

   

    10.使用存储过来实现pin象到library cache考前面的解,此不再演示

 

七、有DBMS_SHARED_POOL请参   

    https://netfiles.uiuc.edu/jstrode/www/oraview/V$DB_OBJECT_CACHE.html

    http://www.dba-oracle.com/art_proc.htm

    http://docstore.mik.ua/orelly/oracle/bipack/ch12_02.htm

 

八、 快捷参考

性能请参

    Oracle 硬解析与软解析

    共享池的与优(Shared pool Tuning)

    Buffer cache 与优(一)

    Oracle (caching table)的使用

 

ORACLE体系结构请参

    Oracle 表空间与数据文件

    Oracle 文件

    Oracle 参数文件

    Oracle 机重做日志文件(ONLINE LOG FILE)

    Oracle 控制文件(CONTROLFILE)

    Oracle 归档日志

    Oracle (ROLLBACK)和撤(UNDO)

    Oracle 库实启动关闭过

    Oracle 10g SGA 的自化管理

    Oracle 例和Oracle(Oracle体系结构)

 

关闪回特性请参

    Oracle 回特性(FLASHBACK DATABASE)

    Oracle 回特性(FLASHBACK DROP & RECYCLEBIN)

    Oracle 回特性(Flashback Query、Flashback Table)

    Oracle 回特性(Flashback Version、Flashback Transaction)

 

基于用管理的备份备份请参

    Oracle 备份

    Oracle 热备份

    Oracle 备份复概

    Oracle 例恢

    Oracle 基于用管理恢(详细描述了介及其)

    SYSTEM 表空管理及备份

    SYSAUX表空管理及恢

 

RMAN备份复与管理请参

    RMAN 述及其体系结构

    RMAN 配置、管理

    RMAN 备份详

    RMAN

    RMAN catalog 建和使用

    基于catalog RMAN脚本

基于catalog 的RMAN 备份与

使用RMAN迁移文件系统数据库到ASM

    RMAN 备份困惑(使用plus archivelog)

 

ORACLE故障请参

    ORA-32004 错误处

    ORA-01658 错误

    CRS-0215 错误处

    ORA-00119,ORA-00132 错误处

    又一例SPFILE错误导无法启动

    对参数FAST_START_MTTR_TARGET = 0 解及

    SPFILE 错误导无法启动(ORA-01565)

 

ASM请参

    ASM例及ASM

    ASM 、目的管理

    使用 ASMCMD 工具管理ASM及文件

 

SQL/PLSQL请参

    SQLPlus 常用命令

    替代SQL*Plus

    使用Uniread实现SQLplus功能

    SQL -->SELECT 查询

    SQL --> NEW_VALUE 的使用

    SQL --> 集合(UNION UNION ALL)

    SQL --> 常用函

    SQL --> 视图(CREATE VIEW)

    SQL --> 建和管理表

    SQL --> 多表查询

    SQL --> 过滤和排序

    SQL --> 查询

    SQL --> 组与

    SQL --> 次化查询(START BY ... CONNECT BY PRIOR)

    SQL --> ROLLUPCUBE算符实现数汇总

    PL/SQL -->

    PL/SQL --> (Exception)

    PL/SQL --> 言基

    PL/SQL --> 流程控制

    PL/SQL --> PL/SQL记录

    PL/SQL --> 包的管理

    PL/SQL --> 式游(SQL%FOUND)

    PL/SQL --> 包重、初始化

    PL/SQL --> DBMS_DDL包的使用

    PL/SQL --> DML 触发

    PL/SQL --> INSTEAD OF 触发

    PL/SQL --> 储过

    PL/SQL -->

    PL/SQL --> 动态SQL

    PL/SQL --> 动态SQL的常见错误

 

ORACLE特性

    Oracle 常用目录结构(10g)

    使用OEM,SQL*Plus,iSQL*Plus 管理Oracle

    日志记录模式(LOGGING 、FORCE LOGGING 、NOLOGGING)

    表段、索引段上的LOGGINGNOLOGGING

    Oralce OMF 功能

    Oracle 限、系统权  

    Oracle 角色、配置文件

    Oracle

    Oracle 外部表

    使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)

    簇表及簇表管理(Index clustered tables)

    EXPDP 出工具的使用

    IMPDP 入工具的使用

    Oracle

    SQL*Loader使用方法

    用用户进程跟踪

    配置非默端口的动态

    配置ORACLE 接到

    system sys,sysoper sysdba 区别

    ORACLE_SID、DB_NAME、INSTANCE_NAME、DB_DOMIAN、GLOBAL_NAME

    Oracle 丁全集 (Oracle 9i 10g 11g Path)

    Oracle 10.2.0.1 10.2.0.4

    Oracle kill session

 

网友评论

登录后评论
0/500
评论
leshami
+ 关注