SGAに起因するパフォーマンス障害を発見する：Oracleパフォーマンス障害の克服（5）（1/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　リスト1の出力結果（クリックすると拡大します）

　データの取得に際し、データファイルからの物理的なI/Oが発生して取得したブロック数（physical reads）と、メモリ上（データベース・バッファ・キャッシュ）から取得したブロック数（consistentgets＋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　リスト2の出力結果（クリックすると拡大します）

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