共享池的調整與優化(Shared pool Tuning)

chuanzhongdu1發表於2011-10-11
優化

共享池(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)+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_ratio DESC,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/1024 FROM v$sgastat WHERE name 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/1024 FROM v$sgastat WHERE pool LIKE '%shar%';

           

                SUM(BYTES)/1024/1024

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

                                1216

                   

            SELECT * FROM v$sgainfo  WHERE name 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.name IN ('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_text from v$sqlarea               

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

       

        --查詢解析的次數

            scott@ORCL> select sql_text,parse_calls,executions from v$sqlarea order by 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_factor AS 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;

原文地址：http://blog.csdn.net/robinson_0612/article/details/6208268

相關文章