Oracleで適切なバッファキャッシュサイズを見極めるポイント

photo credit: nickwheeleroz via photopin cc

はじめに

Oracle DBは、SQLの結果を一時的なメモリ領域（バッファキャッシュ）に記憶して、次に同じような問い合わせがあった場合に、いちいちディスクを読みに行かずにバッファキャッシュから結果を取得することで、高速に結果を返すことができるようになっています。

このバッファキャッシュのサイズは、システムや運用環境に応じた適切な値を設定するのが良いとされています。そこで、適切なバッファキャッシュサイズを見極めるための方法を調べてみました。

RDBMS解剖学よくわかるリレーショナルデータベースの仕組み (DB Magazine SELECTION)

posted with amazlet at 13.04.23

鈴木幸市藤塚勤也
翔泳社
売り上げランキング: 322,732

Amazon.co.jpで詳細を見る

バッファキャッシュサイズを調べる

現在のバッファキャッシュサイズを調べるには、sysユーザで以下のコマンドを実行すると分かります。

show parameter DB_CACHE_SIZE

DB_CACHE_ADVICEを有効にして統計をとる

Oracleには、バッファキャッシュアドバイザという機能があります。
この機能は、バッファキャッシュの使用状況について統計をとり、データベース管理者が適切なバッファキャッシュサイズを見極めるための情報を提供してくれます。

バッファキャッシュアドバイザは、ON/OFFの切り替えができ、一定期間ONにしておくことで、運用しているシステムのバッファキャッシュの状況を知ることができます。
バッファキャッシュアドバイザを有効*1にするには、以下のコマンドを実行します。

alter system set DB_CACHE_ADVICE=ON

バッファキャッシュアドバイザによる予測を参考にする

DB_CACHE_ADVICEをONにしてしばらく統計情報を収集したら、今度はV$DB_CACHE_ADVICEというビューを見てみます。

V$DB_CACHE_ADVICEビューを見ると、現在のバッファサイズを基準に、バッファキャッシュを小さくした場合と大きくした場合で、物理読み込み（ディスクアクセス）が何回発生するかを予測した値がわかります。

メモリーの構成と使用方法

COLUMN size_for_estimate          FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate       FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor  FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads        FORMAT 999,999,999 heading 'Estd Phys| Reads'

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
   FROM V$DB_CACHE_ADVICE
   WHERE name          = 'DEFAULT'
     AND block_size    = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
     AND advice_status = 'ON';