共享池的调整与优化 Shared pool Tuning

               

--=======================================

-- 共享池的调整与优化(Shared pool Tuning)

--=======================================

 

    共享池(Shared pool)是SGA中最关键的内存片段,共享池主要由库缓存(共享SQL区和PL/SQL区)和数据字典缓存组成。其中库缓存的作用是存

放频繁使用的sql，pl/sql代码以及执行计划。数据字段缓存用于缓存数据字典。在内存空间有限的容量下，数据库系统根据一定的算法决定何

时释放共享池中的sql，pl/sql代码以及数据字典信息。下面逐一解释各个部件并给出调整方案。

 

一、共享池的组成

    Library cache(库缓存)                    --存放SQL ,PL/SQL 代码,命令块,解析代码，执行计划

    Data dictionary cache(数据字典缓存)      --存放数据对象的数据字典信息

    User global area(UGA) for shared server session --用于共享模式，可以将该模块移到lareg pool来处理。专用模式不予考虑。

       

二、Library cache 作用与组成

    Library Cache 由以下四个部件组成

        Shared SQL areas

        Private SQL areas

        PL/SQL procedures and packages

        Various control structures

    Library Cache 作用 

        存放用于共享的SQL命令或PL/SQL块

        采用LRU算法(最近最少使用算法)

        用于避免相同代码的再度解析

        ORA-04031则表明共享池不够用

   

三、Data dictionary cache组成与作用

    组成

        Row cache

        Library cache

    作用

        存储数据库中数据文件、表、索引、列、用户和其它数据对象的定义和权限信息

       

四、Shared pool的大小

    Library cache与Data dictionary cache两者共同组成了shared pool的大小，由参数shared_pool_size来决定

        查看：show parameter shared_pool_size

        修改：alter system set shared_pool_size=120m;

   

    sys@ORCL> select * from v$version where rownum < 2;

 

    BANNER

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

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

 

    sys@ORCL> show parameter shared_pool_

 

    NAME                                 TYPE        VALUE

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

    shared_pool_reserved_size            big integer 3M

    shared_pool_size                     big integer 0         --为0，表明由系统自动分配

 

    sys@ORCL> show parameter sga_

 

    NAME                                 TYPE        VALUE

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

    sga_max_size                         big integer 176M

    sga_target                           big integer 176M           --非零值，表示由系统自动调整sga

 

五、SGA_MAX_SIZE与SGA_TARGET   

    sga_max_size 决定了为Oracle分配内存的最大值

    sga_target   决定了基于sga_max_size的大小来自动分配内存，sga_target <= sga_max_size

    sga_target会为下列组件自动分配内存

        Buffer cache

        Shared pool

        Larege pool

        Jave pool

        Streams pool

    当设定sga_target参数为非零值，且又单独设定sga_target的五个组件为非零值，在这种情形下，这几个组件设定的值则为该组件所必须要

    分配的最小值。

 

    下列sga组件不受sga_target的管理和影响，即需要单独为以下几个组件分配大小

        Log buffer(日志缓冲)

        Other buffer caches, such as KEEP, RECYCLE, and other block sizes(保留池，回收池，nK 池)

        Fixed SGA and other internal allocations

 

    有关SGA的自动管理，更详细请参考：Oracle 10g SGA 的自动化管理

 

六、Library pool 共享SQL,PL/SQL 代码标准

    当发布一条SQL或PL/SQL命令时，Oracle会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析。

    SQL语句的执行过程如下：

    a.SQL代码的语法(语法的正确性)及语义检查(对象的存在性与权限)

    b.将SQL代码的文本进行哈希得到哈希值

    c.如果共享池中存在相同的哈希值，则对这个命令进一步判断是否进行软解析，否则到e步骤。

d.对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格，        注释等，如果一致，则对其进行软解析，转到步骤f。否则到d步骤。

    e.硬解析，生成执行计划。

    f.执行SQL代码，返回结果。

 

    有关硬解析与软解析请参考：Oracle 硬解析与软解析

 

七、共享池中闩的竞争

    共享池中闩的竞争或Library cache闩的竞争表明存在下列情形

        非共享的SQL需要硬解析

        重新解析共享的SQL(由于Library cache大小不足导致共享的SQL被LRU算法淘汰掉)

        过多的负荷导致Library cache 大小不足

       

八、v$librarycache视图

    scott@ORCL > desc v$librarycache;

     Name                          Null?    Type

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

     NAMESPACE                              VARCHAR2(15)  --存储在库缓存中的对象类型,值为SQL area,table/procedure,body,trigger

     GETS                                   NUMBER   --显示请求库缓存中的条目的次数(或语句句柄数)

     GETHITS                                NUMBER   --显示被请求的条目存在于缓存中的次数(获得的句柄数)

     GETHITRATIO                            NUMBER   --前两者之比

     PINS                                   NUMBER   --位于execution阶段，显示库缓存中条目被执行的次数

     PINHITS                                NUMBER   --位于execution阶段，显示条目已经在库缓存中之后被执行的次数

     PINHITRATIO                            NUMBER   --前两者之比

     RELOADS                                NUMBER   --显示条目因过时或无效时在库缓存中被重载的次数

     INVALIDATIONS                          NUMBER   --由于对象被修改导致所有参照该对象的执行计划无效的次数，需要被再次解析

     DLM_LOCK_REQUESTS                      NUMBER

     DLM_PIN_REQUESTS                       NUMBER

     DLM_PIN_RELEASES                       NUMBER

     DLM_INVALIDATION_REQUESTS              NUMBER

     DLM_INVALIDATIONS                      NUMBER

 

    get表示请求条目或对象、获得对象句柄；

    pin根据句柄找到实际对象并执行，但对象内容可能因为老化而pin不到所以出现reload；

    一个session需要使用一个object时，如果是初次使用，则必然是先get然后pin并维护这个object的句柄。下次再使用这个object时，因为

    已经维护该句柄，所以直接pin而没有了get过程。如果对象老化则移除共享池，再次请求则会出现reload。

 

    有关Library cache的详细说明：V$LIBRARY

 

    由上面所列出的字段可知，v$librarycache视图可以用来监控library cache的活动情况。

    重点关注字段

        RELOADS列：表示对象被重新加载的次数，理论上该值应该接近于零。过大是由于对象无效或library pool过小被换出。

        INVALIDATIONS：列表示对象失效的次数，对象失效后，需要被再次解析。

        GETHITRATIO：该列值过低，表明过多的对象被换出内存。

        GETPINRATIO：该列值过低，表明会话没有多次执行相同的游标，即使对象被不同的会话共享或会话没有找到共享的游标。

   

    下面查询v$librarycache的性能状况：

        sys@ASMDB > select * from v$version where rownum < 2;

 

        BANNER

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

        Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

       

        SELECT namespace,gets,gethits,ROUND(GETHITRATIO*100,2) gethit_ratio,pins,pinhits,

          ROUND(PINHITRATIO*100,2) pinhit_ratio,reloads,invalidations FROM v$librarycache;

 

        NAMESPACE             GETS    GETHITS GETHIT_RATIO       PINS    PINHITS PINHIT_RATIO    RELOADS INVALIDATIONS

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

        SQL AREA         336824947  326237186        96.86 1137146337 1113509653        97.92    1202492      38273

        TABLE/PROCEDURE 1536310611 1536263944          100 1591415343 1591166141        99.98      85574          0

        BODY                144906     143990        99.37     144969     142474        98.28        128          0

        TRIGGER           47765371   47765105          100   47765381   47765113          100          0          0

        INDEX              1104164    1103706        99.96    1104133    1103467        99.94          0          0

        CLUSTER              42341      42038        99.28      42860      42260         98.6          0          0

        OBJECT                   0          0          100          0          0          100          0          0

        PIPE                     0          0          100          0          0          100          0          0

        JAVA SOURCE             40         19         47.5         40         19         47.5          0          0

        JAVA RESOURCE           40         19         47.5         40         19         47.5          0          0

        JAVA DATA              116         71        61.21        237        147        62.03          0          0

 

    分析上面的查询，在此仅仅分析SQL AREA对象，其余的类似分析

    a.在SQL AREA中，执行的次数为次1137146337 (PINS 列)。

    b.重载(RELOADS)的次数为1202492，表明一些对象无效或因librarycache过小被aged out，则这些对象被执行了重载。

    c.无效的对象(INVALIDATIONS)为38273次。

    d.基于查询的结果，可以用于判断shared_pool_size的reloads,invalidations的情况，是否调整share_pool_size请参考后面十,十一,十二点

   

九、数据字典缓存(data dictionary cache)

    使用视图v$rowcache获取数据字典缓存的信息

        该视图中包含字典对象的定义信息

        gets: 请求对象的次数

        getmisses:在data dictionary cache中请求对象失败的次数

    调整目标：避免请求失败

    也可根据statspack来调整data dictionary cache

    通常情况下，应保证数据字典缓存命中率为95%或高于95%

        --下面查询数据字典缓存的命中率与缺失率

        SELECT ROUND(((1-SUM(getmisses)/(SUM(gets