共享池的调整与优化(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 systemset 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,invalidationsFROM 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)+SUM(getmisses))))*100,3) "Hit Ratio"

           ,ROUND(SUM(getmisses)/sum(gets)*100,3) "Misses Ratio"

       FROM v$rowcache

       WHERE gets + getmisses<> 0;

       

       Hit Ratio Misses Ratio

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

          99.865         .135

 

   缺失率应当低于以下百分比

       <2% 对于常用的数据字典对象

       <15% 整个数据字典缓冲对象

   

   整个数据字典的缺失率

       SELECT ROUND((100*SUM(getmisses)/decode(SUM(gets),0,1,SUM(gets))),2) Getmiss_ratio

       FROM v$rowcache;

       

       GETMISS_RATIO

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

               .14

               

   不同的组件对象检查组件的缺失率及命中率的情况

       SELECT parameter

                ,SUM(gets)

                ,SUM(getmisses)

                ,ROUND((100*SUM(getmisses)/decode(SUM(gets),0,1,SUM(gets))),2) Getmiss_ratio

                ,ROUND((100*SUM(gets-getmisses)/SUM(gets)),2) Hit_Ratio

                ,SUM(modifications) updates

       FROM v$rowcache

       WHERE gets>0

       GROUP BY parameter

       ORDER BY Getmiss_ratioDESC,Hit_Ratio DESC;

 

       PARAMETER                         SUM(GETS)SUM(GETMISSES) GETMISS_RATIO HIT_RATIO    UPDATES

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

       dc_qmc_cache_entries                      1             1           100          0         0

       dc_constraints                           54            31         57.41      42.59        54

       dc_tablespace_quotas                    976           198         20.29      79.71       976

       dc_files                                539            32          5.94      94.06         3

       dc_global_oids                       564058          2459           .44      99.56         0

       dc_histogram_defs                 185645793        223703           .12      99.88         0

       dc_objects                         73470326         30375           .04      99.96      2228

       dc_segments                       112544251         50126           .04      99.96      2198

       dc_sequences                        7814295          1453           .02      99.98   7814291

 

       关于dc_qmc_cache_entries为100%还不清楚，请大家指正。

 

十、优化Library cache

   总原则尽可能使代码解析最小化

       确保用户尽可能使用共享的SQL执行计划

       为Library cache分配更多的空间以避免淘汰最老的代码与执行计划

       避免无效的再度解析(如Library cache已经存在某个对象的解析，而该对象结构发生了变化)

   避免Library cache中过多的碎片

       为Library cache使用保留空间

       锁定一些频繁使用的对象到Library cache中，以避免LRU算法淘汰掉

       排除较大的PL/SQL匿名块或对其进行拆分

       对于共享服务器模式可以分配large pool给UGA，避免对共享池的争用  

       

十一、调整shared_pool_size

   1.监控对象的重载情况

       SELECT NAMESPACE,

              GETS,

              GETHITS,

              round(GETHITRATIO* 100, 2) gethit_ratio,

              PINS,

              PINHITS,

              round(PINHITRATIO* 100, 2) pinhit_ratio,

              RELOADS,

              INVALIDATIONS

       FROM   V$LIBRARYCACHE;  --考虑是否存在过多的reloads和invalidations

       

   2.当库缓存的重载率大于零，应考虑增大shared_pool_size

 

       SELECT SUM(pins) "Executions",SUM(reloads) "Cache Misses while Executing",

          ROUND(SUM(reloads)/SUM(pins)*100,2)AS "Reload Ratio, %" FROM V$LIBRARYCACHE;

 

       Executions Cache Misses while Executing Reload Ratio,%

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

       2777717625                      1288253            .05

 

   3.库缓存的命中率应保持在95%，否则应考虑增大shared_pool_size

       SELECT SUM(pins) "Executions",SUM(reloads) "Cache Misses while Executing",

         ROUND((SUM(pins)/(SUM(reloads)+SUM(pins)))*100,2)

         "Hit Ratio, %" FROM V$LIBRARYCACHE;

 

       Executions Cache Misses while Executing Hit Ratio,%

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

       2777727542                      1288257       99.95

   

   4.估算Library cache占用大小，shared pool的可用空间，总大小

   

       --查看共享池可用空间，当shared pool有过多的可用空间，再调大shared pool则意义不大       

           SELECT pool,name,bytes/1024/1024FROM v$sgastat WHEREname LIKE '%free memory%' AND pool = 'shared pool';

 

           POOL        NAME                      BYTES/1024/1024

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

           shared pool free memory                     97.6241302

                   

       --查询已使用的Library cache大小总和

           WITH cte AS(

               SELECT SUM(sharable_mem) sharable_mem_count  --查询非SQL语句(包，视图)占用的Library cache大小

               FROM v$db_object_cache

               UNION ALL

               SELECT SUM(sharable_mem)                     --查询SQL语句占用的Library cache大小      

               FROM v$sqlarea

               )

           SELECT SUM(sharable_mem_count)/1024/1024         --查询已使用的Library cache大小总和

           FROM cte;                                        --实际上还有一部分为用户游标使用占用的空间，此处略去

           

           SUM(SHARABLE_MEM_COUNT)/1024/1024

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

                            820.59599971771

                             

       --查询分配的shared_pool_size的大小     

           SELECT SUM(bytes)/1024/1024FROM v$sgastat WHERE poolLIKE '%shar%';

           

               SUM(BYTES)/1024/1024

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

                               1216

                   

           SELECT * FROM v$sgainfo  WHEREname LIKE 'Shared%';

     

   

5.查看shared pool的分配大小，已使用空间，可用空间，已用空间的百分比

       column shared_pool_used format 9,999.99

       column shared_pool_size format 9,999.99

       column shared_pool_avail format 9,999.99

       column shared_pool_pct format 999.99

 

       SELECT SUM(a.bytes)/ (1024 * 1024) shared_pool_used,

              MAX(b.value)/ (1024 * 1024) shared_pool_size,

              (MAX(b.value)- SUM(a.bytes))/ (1024 * 1024) shared_pool_avail,

              (SUM(a.bytes)/ MAX(b.value))* 100 Shared_pool_per

       FROM   v$sgastat a, v$parameter b

       WHERE  a.nameIN ('table definiti',

                         'dictionary cache',

                         'library cache',

                         'sql area',

                         'PL/SQL DIANA')

              AND b.name= 'shared_pool_size';

             

       SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAIL SHARED_POOL_PER

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

                 965.49         1,152.00           186.51       83.809699           

 

6.根据上述的各个情况的判断，检查v$shared_pool_advice来判断增加shared_pool_size

        SELECT shared_pool_size_for_estimate est_size,

               shared_pool_size_factor size_factor,

               estd_lc_size,

               estd_lc_memory_objects obj_cnt,

               estd_lc_time_saved_factor sav_factor

        FROM  v$shared_pool_advice;

   

        EST_SIZE SIZE_FACTOR ESTD_LC_SIZE   OBJ_CNT SAV_FACTOR

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

             640       .5556         642      54947          1

             768       .6667         769      80736          1

             896       .7778         896     101860          1

            1024       .8889        1023     135536          1

            1152           1        1150     167927          1

            1280      1.1111        1277     200423          1

            1408      1.2222        1404     234144          1

            1536      1.3333        1535     257042          1

            1664      1.4444        1662     270800          1

            1792      1.5556        1789     282202          1

            1920      1.6667        1914     294138          1

            2048      1.7778        2040     306570          1

            2176      1.8889        2169     317104          1

            2304           2        2299     327659          1

       

十二、共享池调优工具

   1.几个重要的性能视图

       v$sgastat

       v$librarycache

       v$sql

       v$sqlarea

       v$sqltext

       v$db_object_cache

   2.几个重要参数

       shared_pool_size

       open_cursors

       session_cached_cursors

       cursor_space_for_time

       cursor_sharing

       shared_pool_reserved_size

   

   3.查询视图获得相关信息

       --查询执行次数小于5的SQL语句

           scott@ORCL> select sql_textfrom v$sqlarea               

             2  where executions< 5 order by upper(sql_text);    

       

       --查询解析的次数

           scott@ORCL> select sql_text,parse_calls,executionsfrom v$sqlarea orderby parse_calls;  

 

       对于那些相同的SQL语句，但不存在于Library pool，可以查询视图v$sql_shared_cursor 来判断v$sql_shared_cursor

       为什么没有被共享，以及绑定变量的错误匹配等。

 

       --查询特定对象获得句柄的命中率

           select gethitratio

           from v$librarycache

           where namespace='SQL AREA';

   

       --查询当前用户正在运行哪些SQL语句

           select sql_text,users_executing,

           executions,loads

           from v$sqlarea

 

           select * from v$sqltext

           where sql_text like'select * from scott.emp where %';

       

       --收集表的统计信息

           scott@ORCL> execute dbms_stats.gather_table_stats(-    --注意此处-表示转义

           > 'SCOTT','EMP');

 

           PL/SQL procedure successfully completed.

 

       --通过动态性能视图获得有关share pool size的建议

           SELECT Shared_Pool_size_for_estimate AS pool_size

                  ,shared_pool_size_factorAS factor

                  ,estd_lc_size

                  ,estd_lc_time_saved

           FROM v$shared_pool_advice;

 

       --通过视图v$sql_plan查看执行计划

           SELECT operation

                  ,object_owner

                  ,object_name

                  ,COST

           FROM v$sql_plan

           ORDER BY hash_value;

 

       --SQL语句与执行计划的对照

           --v$sql中有一列为plan_hash_value与v$sql_plan相互参照

           SELECT a.operation

                  ,object_owner

                  ,object_name

                  ,COST

                  ,b.sql_text

           FROM v$sql_plan a

                JOIN v$sql b

                         ON a.plan_hash_value=b.plan_hash_value

           WHERE a.object_owner= 'SCOTT'                 

           ORDER BY a.hash_value;