Oracleパフォーマンス障害の克服（5）

SGAに起因するパフォーマンス障害を発見する　Page 1/3

高橋 潤
2004/12/11
Oracleデータベースの運用管理者は、突発的に直面するパフォーマンス障害にどうやって対処したらよいか。本連載は、非常に複雑なOracleのアーキテクチャに頭を悩ます管理者に向け、短時間で問題を切り分け、対処法を見つけるノウハウを紹介する。対象とするバージョンはOracle8から9iまでを基本とし、10gの情報は随時加えていく。（編集局）
主な内容
--Page 1--
データベース・バッファ・キャッシュはヒット率90％以上を維持する
　・データベース・バッファ・ヒット率の算出
--Page 2--
　・データベース・バッファ・キャッシュの見積もり
--Page 3--
共有プールは全体と使用目的を意識して設定する
　・共有プール全体のサイズを確認
　・ライブラリ・キャッシュ・ヒット率の算出
　・ディクショナリ・キャッシュ・ヒット率の算出
初期化パラメータの変更

　前回「オプティマイザの判断ミスを疑ってみよう」では、オプティマイザの挙動について確認しました。今回は、OracleのSGA（System Global Area）と呼ばれるメモリ部分（データベース・バッファ・キャッシュ、共有プール）に起因するパフォーマンス障害を乗り切るテクニックを紹介します。

　当初適切に設定したOracleのメモリ割り当てでも、日々の運用の中でデータ量の増大など状況の変化により、問題が発生することもあります。SGAの問題を切り分けるために

  • データベース・バッファ・キャッシュ
    　データベース・バッファ・ヒット率
    　データベース・バッファ・キャッシュの見積もり
  • 共有プール
    　ライブラリ・キャッシュ・ヒット率
    　ディクショナリ・キャッシュ・ヒット率

について、メモリの使用状況を確認するSQLを発行し、適切な設定値を見つける手順を解説します。なお、ラージプール、Javaプールはオプション設定によりSGA内に配置されますが、今回の解説からは除外します。

　Oracleのパフォーマンスや挙動に障害の兆候が認められた場合、サーバの負荷がかかる時間帯を選びメモリ関連の情報を取得します。今回の内容は、メモリの動的な状況を把握するものであるため、稼働中のサーバに対する負荷が最も高い状態で調査する必要があります。また、定期的に情報を取得し、深刻な障害に陥る前に対策を打つといった運用を心掛けることが重要です。

■データベース・バッファ・キャッシュはヒット率90％以上を維持する

　データベース・バッファ・キャッシュは、アクセスされたデータファイル内にあるデータをいったんメモリ上に展開し、保持しておくメモリ領域です。アクセスするデータがデータベース・バッファ・キャッシュにあれば、データファイルにアクセスせずにメモリ上のデータで応答を返します。これにより再利用される可能性が高いアクセスに対しディスクI/Oを削減し、サーバ処理負荷を減らしパフォーマンスを向上させています。割り当てメモリサイズが大きいほどユーザーデータをメモリ上に保持できるのでいくらでも増やしたいところですが、メモリには上限があり、OSやOracleのバックグラウンドプロセスなどメモリ上に展開されるプロセスにも影響を与えるので、最適なサイズを割り当てる設計が必要です。

データベース・バッファ・ヒット率の算出

　V$SYSSTATはOracleデータベースサーバの起動時から、アクセスしたデータブロックの累積値を確認できます。

SQL> SELECT name,value
     FROM v$sysstat
     WHERE name IN ('db block gets','consistent gets',
                    'physical reads');
リスト1　V$SYSSTATからdb block gets、consistent gets、physical readsの値を取得

図1　リスト1の出力結果（クリックすると拡大します）

　データの取得に際し、データファイルからの物理的なI/Oが発生して取得したブロック数（physical reads）と、メモリ上（データベース・バッファ・キャッシュ）から取得したブロック数（consistent gets＋db block gets）により、以下の計算式からデータベース・バッファのヒット率が求められます。

データベース・バッファ・ヒット率＝
　 1 -（physical reads / （consistent gets＋db block gets））

　一般的に、このヒット率が90％以上を維持できるようなデータベース・バッファ・キャッシュ・サイズを作成すべきとされています。リスト2はデータベース・バッファ・キャッシュを取得するSQLです。

SQL> SET SERVEROUTPUT ON;
     DECLARE
       d_gets NUMBER;
       c_gets NUMBER;
       p_reads NUMBER;
       result NUMBER;
     BEGIN
       SELECT VALUE INTO d_gets FROM V$SYSSTAT
         WHERE NAME = 'db block gets';
       SELECT VALUE INTO c_gets FROM V$SYSSTAT 
         WHERE NAME = 'consistent gets';
       SELECT VALUE INTO p_reads FROM V$SYSSTAT 
         WHERE NAME = 'physical reads';
       result := 
         ROUND((1 - (p_reads / (c_gets + d_gets))),3) * 100;
       DBMS_OUTPUT.PUT_LINE(
         'データベース・バッファキャッシュヒット率->' ||
         result || '%');
     END;
     /
リスト2　V$SYSSTATからデータベース・バッファ・キャッシュのヒット率を取得

図2　リスト2の出力結果（クリックすると拡大します）

　ヒット率が90％に満たない場合は、初期化パラメータファイル（init.ora）の値を変更し、データベース・バッファ・キャッシュのサイズを変更すべきです。ここで重要となるのは、db block getsとconsistent getsの値の信頼性です。筆者はこの値について、単純にデータベース・バッファ・キャッシュ内にユーザーデータがロードされていない状況で、physical readsはdb block getsとconsistent getsの単純な和でなければこの計算式は成り立たないと考え、わざわざ確認した経験があります。筆者の経験上、この値にはOracleデータベースサーバが内部的にアクセス（具体的には未確認ですが）したブロック数も含まれる場合があるようです。physical readsはバッファ上にデータがなく、すべての結果をデータファイルから取得した場合のブロック数とは異なり、単純にdb block getsとconsistent getsの和であると考えてしまうのは危険であるようです。ヒット率が90％を超えているから大丈夫と考えず、多少柔軟性を持たせた判断が必要であると考えます。（次ページへ続く）

　 1/3

　Index
連載 Oracleパフォーマンス障害の克服（5）
SGAに起因するパフォーマンス障害を発見する
Page 1
・データベース・バッファ・キャッシュはヒット率90％以上を維持する
　 −データベース・バッファ・ヒット率の算出
  Page 2
　 −データベース・バッファ・キャッシュの見積もり
  Page 3
・共有プールは全体と使用目的を意識して設定する
　−共有プール全体のサイズを確認
　−ライブラリ・キャッシュ・ヒット率の算出
　−ディクショナリ・キャッシュ・ヒット率の算出
・初期化パラメータの変更


Oracleパフォーマンス障害の克服

TechTargetジャパン

Database Expert フォーラム 新着記事

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

RSSフィード

＠IT 新着記事

キャリアアップ

- PR -
＠IT Sepcial
→ ＠IT Special ヘ

イベントカレンダー

PickUpイベント

- PR -
もっと見る
- PR -

お勧め求人情報

ホワイトペーパー（TechTargetジャパン）

＠IT Sepcial
→ ＠IT Special ヘ
ソリューションFLASH