データベースバッファキャッシュのチューニング(2) --- チューニング目標、診断方法

1Z0-033
  • チューニング目標
    • ディスクI/Oは負荷が高いのでなるべく減らすことが重要
    • OLTP系システム：バッファヒット率90%以上
    • DSS系システム：ディスクから読み込むことがほとんどなので、メモリよりもI/Oチューニング重視する
    • 他のプロセスのためのメモリも必要なので、過度なページフォルトが無いことを確認してキャッシュを増やす。
    • 前回のキャッシュ追加が効果的ではなかったら、増やしても意味が無い
      • DSS系はフルスキャンがメインのため、キャッシュを増やしても効果は出ない
      • フルスキャンした場合は、LRUリストのL側にのせられる
      • ソート処理やパラレル処理ではデータベースバッファキャッシュは使用されない
  • 診断方法
    • ヒット率＝1−（ダイレクト処理を除く物理読込ブロック数／総読込ブロック数）
    • システム統計（v$sysstat）の下記の情報より算出

session logical reads 総読込ブロック数
physical reads 物理読込ブロック数
physical reads direct ダイレクト読込ブロック数（パラレルクエリなど）
physical reads direct(lob) ダイレクト読込ブロック数（画像・音声データなど） 

SQL> select 1-(phy.value - dir.value - lob.value) / ses.value 
     "Hit rate"
     from v$sysstat ses, v$sysstat phy,
     v$sysstat dir, v$sysstat lob
     where ses.name='session logical reads'
     and phy.name='physical reads'
     and dir.name='physical reads direct'
     and lob.name='physical reads direct (lob)' ;

  Hit rate
----------
.999937215

　※DBにある程度負荷をかけてから診断する

  • ヒット率以外で確認しておきたい情報
    • free buffer inspected (v$sysstat)：空きリスト探した数
    • buffer busy waits (v$system_event)：バッファにアクセスするのを待機した数
    • free buffer waits (v$system_event)：使用済みリストが満タンで空きバッファが見つけられなかった数
  • バッファキャッシュアドバイザの使用
    • 9i新機能
    • alter system set DB_CACHE_ADVICE=ON|READY|OFF
    • OFFにするとアドバイザ用に割り当てられた共有プールのメモリは開放される
    • V$DB_CACHE_ADVICEビューに予測結果が格納される
SQL> show parameter db_cache_size

NAME             TYPE         VALUE
---------------- ------------ -----------
db_cache_size    big integer  67108864

SQL> select name,
            block_size "block",
            size_for_estimate "size(MB)",
            buffers_for_estimate "buffers",
            estd_physical_read_factor "read_factor",
            estd_physical_reads "physical_reads"
     from v$db_cache_advice ;

NAME     block   size(MB)    buffers     factor    p_reads
-------- ----- ---------- ---------- ---------- ----------
DEFAULT   8192         16       1985     3.5866      14852
DEFAULT   8192         32       3970     1.1064       4582
DEFAULT   8192         48       5955          1       4141
DEFAULT   8192         64       7940          1       4141
DEFAULT   8192         80       9925          1       4141
DEFAULT   8192         96      11910          1       4141
DEFAULT   8192        112      13895          1       4141
DEFAULT   8192        128      15880          1       4141
DEFAULT   8192        144      17865          1       4141
DEFAULT   8192        160      19850          1       4141
DEFAULT   8192        176      21835          1       4141
DEFAULT   8192        192      23820          1       4141
DEFAULT   8192        208      25805          1       4141
DEFAULT   8192        224      27790          1       4141
DEFAULT   8192        240      29775          1       4141
DEFAULT   8192        256      31760          1       4141
DEFAULT   8192        272      33745          1       4141
DEFAULT   8192        288      35730          1       4141
DEFAULT   8192        304      37715          1       4141
DEFAULT   8192        320      39700          1       4141

※現在64MBで、32Mに減らすと物理読み込みが増加する。またこれ以上データベースバッファキャッシュを増やしても全然効果が無いことがわかる。