oracle調優之-共享池尺寸調優+library cache+dicitonary library 命中率

轉載：http://blog.csdn.net/csucxcc/article/details/5290569

當構建生產系統，我們的一般做法是首先根據經驗給出一個共享池尺寸，然後，在一定的工作載荷壓力下，檢查相關的統計
來檢查共享池的效率。
  共享池的效率對於不同的應用來說是不同的。對於大多數OLTP應用系統，共享池尺寸將直接影響系統的效能。因為SQL、
PL/SQL的硬解析將導致資源的浪費和共享池栓（資源鎖）的爭用。但對於決策支援（DSS)系統，其效能代價相對較小。

   庫快取的使用效率可以透過命中率的統計來進行分析。如前所述，共享池中的庫快取用來保證多次執行的SQL語句將被
快取起來，以避免硬解析。我們可以透過V$LIBRARYCACHE動態檢視來得到關於庫快取的命中率資訊，包括SQL的過載相關資訊、
SQL解析結果不可用的相關資訊。下面的示例查詢了當前庫快取的使用狀態：
   SQL>SELECT NAMESPACE,PINS,PINHITS,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE ORDER BY NAMESPACE;

 

SQL> SELECT NAMESPACE,PINS,PINHITS,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE ORDER BY NAMESPACE;

NAMESPACE             PINS    PINHITS    RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
BODY               8851836    8756370      61901             0
CLUSTER             230680     212963      13929             0
INDEX               231760     172036       2933             0
JAVA DATA                0          0          0             0
JAVA RESOURCE            0          0          0             0
JAVA SOURCE              0          0          0             0
OBJECT                   0          0          0             0
PIPE                     0          0          0             0
SQL AREA        3145910657 2031153447    2855257        440049
TABLE/PROCEDURE  433904619  429595986    2167680             0
TRIGGER            2732465    2637826      90366             0

 

其中，RELOAD列表示SQL被重新解析的累積次數，INVALIDATION列表示解析結果不可用的累積次數。這兩個值都應接近零。

另一個關鍵的統計是在系統執行峰值狀態下共享池中自由記憶體的數量。自由記憶體數量應該勁量低，以使其被充分利用，
避免過載（RELADS)的發生。該數值透過V$SGASTAT檢視進行查詢，列入下面的查詢示例。

SQL>select * from V$SGASTAT where name like '%free memory%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                2180229864
large pool   free memory                  15701144
java pool    free memory                  16777216

上面查詢了系統最近一次啟動系統後的SQL過載次數、失效次數、自由空間等統計。這些統計和庫快取的命中率、栓爭用資訊
結合起來，就可以判斷庫快取的利用率了。

動態效能檢視V$LIBRARYCAHCE統計了系統自啟動後累積的關於庫快取使用的資訊。該檢視中每行顯示了儲存在庫快取在庫快取中的
資訊。該檢視中每行顯示了儲存在庫快取中的各類物件和庫快取使用統計。下列查詢顯示了當前系統庫快取的使用統計:

SQL>SELECT NAMESPACE,PINS,PINHITS,PINHITRATIO,RELOADS,INVALIDATIONS from V$LIBRARYCACHE;

SQL> SELECT NAMESPACE,PINS,PINHITS,PINHITRATIO,RELOADS,INVALIDATIONS from V$LIBRARYCACHE;

NAMESPACE             PINS    PINHITS PINHITRATIO    RELOADS INVALIDATIONS
--------------- ---------- ---------- ----------- ---------- -------------
SQL AREA             44904      41857   .93214413        950             1
TABLE/PROCEDURE      14568       9677  .664264141       1078             0
BODY                  2038       1948  .955839058         55             0
TRIGGER                292        276  .945205479          6             0
INDEX                  101          6  .059405941         38             0
CLUSTER                373        359  .962466488          6             0
OBJECT                   0          0           1          0             0
PIPE                     0          0           1          0             0
JAVA SOURCE              0          0           1          0             0
JAVA RESOURCE            0          0           1          0             0
JAVA DATA                0          0           1          0             0

因此，分析上面的結果，SQL的執行（SQL AREA 名稱空間）有 44904次(PINS列）其中41857次（PINHITS 列）可以從庫中獲得，命中率（PINHITRATIO列）為93.21%以上。

結合共享池中自由記憶體量的查詢（查詢V$SGASTAT中關於SGA的分配統計資訊），可以看到共享池有 2180229864位元組的自由記憶體。因此，
本例中增加共享池的大小，將不會對系統效率產生邊際效益。

共享池中的另外一個資料結構是資料字典快取。一般而言，如果共享池滿足庫快取的需求，則資料字典快取部分也同樣夠用，無須單獨
考慮其大小。和庫快取一樣，字典快取在系統啟動初期是空白的，隨著資料字典資料不斷的被讀入快取，字典快取的使用趨於穩定。
在一定的執行時間之後，大多數常用資料字典資訊將被快取在字典快取區域。

 

動態效能檢視V$ROWCACHE可以用來顯示字典快取的使用情況。該檢視中每一行資料反映一種資料字典的統計，統計值從最近一次的例項
啟動開始累計。該檢視的下面四列反映了最常用資訊:
  PARAMETER:資料字典項
  GETS:透過資料字典快取對資料字典請求的獲得
  GETMISSES:不能透過資料字典快取獲得資料的請求總數，即丟失總數。
  MODIFICATIONS:在資料字典快取中資料項被更新的次數。

 

下面是一個常用來檢視關於資料字典快取使用統計的查詢，並以命中率的形式檢視其使用率:
SQL> SELECT parameter,
     sum(gets),
     sum(getmisses),
     100*sum(gets - getmisses)/sum(gets) get_rate,
     sum(modifications) modified
     from v$rowcache
     where gets>0
     group by parameter;

PARAMETER                         SUM(GETS) SUM(GETMISSES)   GET_RATE   MODIFIED
-------------------------------- ---------- -------------- ---------- ----------
dc_tablespaces                         5827              7 99.8798696          0
dc_awr_control                          193              1 99.4818653          7
dc_object_grants                         16              2       87.5          0
dc_histogram_data                      4254            642 84.9083216          0
dc_rollback_segments                   1040             11 98.9423077         31
dc_sequences                             10              5         50         10
dc_usernames                           1185              7 99.4092827          0
dc_segments                            2991            579 80.6419258          8
dc_objects                             6028            679 88.7358991         73
dc_histogram_defs                     10585           2783 73.7080775          0
dc_users                               7323              9 99.8770995          0

PARAMETER                         SUM(GETS) SUM(GETMISSES)   GET_RATE   MODIFIED
-------------------------------- ---------- -------------- ---------- ----------
outstanding_alerts                      137             14 89.7810219          2
dc_files                                  6              6          0          0
dc_object_ids                         10653            725 93.1944053         56
dc_global_oids                          517             31 94.0038685          0
dc_profiles                             171              1 99.4152047          0

16 rows selected.

SQL>

上面的查詢結果顯示了當前資料字典快取具有較高的命中率。但某些項的值並不高。這可能和開機時間不常有關。
下面的一個示例綜合計算了資料字典快取的整體命中率:
SQL>SELECT  (SUM(GETS - GETMISSES - FIXED))/SUM(GETS) "Data Dict Hint Rate" from v$ROWCACHE;
Data Dict Hint Rate
-------------------
         .890128386

顯然，在一般情況下增加共享池的記憶體總量將增加庫快取和資料字典快取的可使用量，可能有利於增加共享池的命中率。

Oracle中沒有關於庫快取和字典快取的初始化引數。增加共享池（初始化引數 SHARED_POOL_SIZE)的值後，資料字典快取也
同時得到了增加。一般而言，如果庫快取、字典快取的命中率低於90%，則考慮增加初始化引數SHARED_POOL_SIZE的值。

這裡面要注意增加初始化引數 SHARED_POOL_SIZE所能帶來的邊際效益。當SHARED_POOL_SIZE大到一定程度時，例如300MB，
大多數系統的庫快取命中率及資料字典快取的命中率都會達到一種穩定的狀態。再增加此引數的值，將不能帶來實際的效益。

SQL 快取設定引數
在庫快取命中率保持較高的情況下，設定CURSOR_SPACE_FOR_TIME 引數可以起到加快SQL執行速度的作用。該引數的作用是
指定那些已被快取的SQL語句及其執行計劃、已編譯可執行程式碼，是否可被“置換出”庫快取，以容納新產生的SQL。該引數預設為
false:

SQL>show parameter CURSOR_SPACE_FOR_TIME

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_space_for_time                boolean     FALSE

這表明，當庫快取需要空間來容納新的SQL時，可以釋放當前儲存在庫快取中的SQL來維護空閒空間，即便這條SQL語句當前處於被
引用狀態（當前應用系統正在使用這條被快取的SQL)
   因此，是否設定該引數為true,取決於系統庫快取的命中率。在保持命中率的前提下，可以考慮設定這個引數，這將提高Oracle
的SQL執行效率。

下面查詢當前資料庫得到庫快取命中率，其結果顯示快取命中率較高：
SQL>SELECT NAMESPACE,PINS,PINHITS,PINHITRATIO ,RELOADS,INVALIDATIONS from v$librarycache
order by namespace;

如果命中率高，就可以使用下面命令設定該引數為true

SQL>alter system set cursor_space_for_time=true scope=spfile;

需要注意的是，當庫快取命中率較低時，不可以進行這樣的設定，因為這會造成庫快取排程錯誤，進而引發Oracle執行SQL中的錯誤。