僕がAWRレポートを好きな理由（DBメモリ編）
この記事は、JPOUG Advent Calendar 2019の14日目の記事です。
13日目の記事は三原 健一さんの記事『負荷テストデータ作成に関するTips — 指定率行の抽出・更新 — | サイクル＆オラクル』でした。
はじめに
Oracle DBAとして経験を積んでちょっとデキる感と自信を持てるようになるのは、やはりDBパフォーマンス・チューニングの領域に足を踏み入れてからだと思います。
私もインフラエンジニアとしてまだOracle DBを担当していなかったころは、DBのトラブルシューティングで呼ばれたエンジニアやコンサルの人たちがAWRレポートをふむふむと眺めてはボトルネック分析している姿をみてカッコいいなーと憧れていました。
DBパフォーマンス・チューニングは大きく分類すると
に分かれると思いますが、AWRレポートは主にインスタンス・チューニングで利用します。
この記事ではOracle DBのパフォーマンス・チューニングにおいて要となるAWRレポートが好きな理由を書きたいと思います（技術解説じゃないです）。
AWRの概要
AWRについて
Oracle DB 10gからOracle DBのチューニング方法としてDB Timeベース・チューニング ができるようになり、Oracle DBの稼働情報をスナップショットとして保存するAWR（Automatic Workload Repository）の機能が導入されました。Oracle 8iから導入されているStatspackの進化版の機能になります。
SGA（System Global Area）上にあるOracle DBの稼働情報をバックグラウンド・プロセスのMMON（Memory Monitor）が収集して定期的にSYSAUX表領域上のリポジトリにスナップショットとして記録します。記録されたスナップショットは一定期間保存されます（デフォルトでは60分間隔で8日間保存されます）。
また、AWRを利用するにはOracle DBのEnterprise EditionとDiagnostics Packオプションが必要になります。
AWRレポートについて
AWRレポートは任意の2時点で取得したスナップショット間の差分からOracle DBのパフォーマンス関連の統計をレポート形式で出力したものになります。
Oracle DBインスタンス全体のアクティビティ、アプリケーションの傾向、待機イベントの発生状況などの負荷状況を把握することができます。
定期的に正常稼働時のAWRレポートを取得しておいて、DBトラブル発生時のAWRレポートと負荷傾向などを比較分析するのが理想的ですが、DBトラブル発生時のAWRレポートだけでも分析はできます。
AWRレポートが好きな理由
私がAWRレポートを好きなのは基本的にパフォーマンス分析やトラブルシューティングが好きなのもありますが、Oracle DBは内部がブラックボックスなのにAWRレポートを分析するとOracle DBの内部でなにが起きているのかがいろいろ想像できるからです！
今回はそのなかでもDBメモリの分析について書きました〜
DBのメモリ状況がわかる！
基本的にOracle DBのアーキテクチャはメモリ上にアクセス頻度の高いデータを置くことによって、ディスクI/Oを減らして高速化を実現していますので、DBのメモリ状況はとても重要です。
AWRレポートからSGAやPGA（Program Global Area）のメモリの確保状況などを分析できます。
キャッシュヒット率とアドバイザリをみる
Report SummaryのInstance Efficiency Percentagesでバッファ・キャッシュとライブラリ・キャッシュのヒット率をみることができます。
システムの特性にもよりますが、OLTPの場合は95％以上が一般的です。
ヒット率が悪い場合、Buffer Pool Advisoryをみてサイズ拡張による物理ブロック読込時間の減少が顕著であれば必要なブロックがバッファ・キャッシュ上にキャッシュできずにI/O待機が発生している可能性がありますし、Shared Pool Advisoryをみてサイズ拡張によるロード時間の減少が顕著であれば必要なライブラリ・キャッシュ・オブジェクトがキャッシュアウトしている可能性がありますので、SGAの各コンポーネントのサイズ拡張を検討します（Size Factor 1.0が現在のサイズ）。
各メモリ・コンポーネントの状況をみる
Memory StatisticsのMemory Dynamic Componentsで各メモリ・コンポーネントの状況をみることができます。
自動メモリ管理（AMM）や自動共有メモリ管理（ASMM）を使用しているときにSGAの各メモリ・コンポーネントでメモリ不足が発生した場合には、バッファ・キャッシュとの間で自動的にサイズ調整されます。
各メモリ・コンポーネントのサイズ調整には定期取得した統計情報に基づいて行われる場合（DEFERREDモード）とメモリ割当てエラー（ORA-04031）の発生を回避するために緊急で行われる場合（IMMEDIATEモード）がありますが、IMMEDIATEモードでのサイズ調整が発生している場合には当該メモリ・コンポーネントのサイジングが適切でない可能性が高いです（IMMがIMMEDIATEモード）。
REDOログ・バッファのリトライ状況をみる
SQLによる更新処理が発生するとREDO ログを生成とともにREDOログ・バッファに領域を確保して配置しますが、REDOログ・バッファはSGAのメモリ・コンポーネントのなかでもAMMやASMMによる自動サイズ調整の対象外のため、REDOログ・バッファのサイズが小さい場合には領域を確保できずにリトライ処理が発生する場合があります。 REDOログ・バッファの領域が確保できないと更新処理を開始できないため、リトライ処理が多く発生していると更新処理が性能劣化する要因となります。
REDOログ・バッファの領域確保のリトライ処理が発生しているかどうかは、Instance Activity StatisticsのOther Instance Activity Statsでredo buffer allocation retriesで確認することができます。
また、REDOログ・バッファのサイズが小さくて領域が確保できない場合だけではなく、I/O性能の問題でバックグラウンド・プロセスのLGWR（Log Writer）によるオンラインREDOログ・ファイルのスイッチに時間を要している場合などにもリトライ処理は発生しますので、Wait Events StatisticsのForeground Wait Eventsでlog file switch *の待機イベントの発生回数を確認してREDOログ・バッファの領域不足の可能性が高ければ拡張を検討します。
PGAのサイズをみる
すべてのサーバ・プロセスで使用するPGA サイズの合計（総PGAサイズ）がpga_aggregate_target（初期化パラメータ） で設定したターゲット値に近づくと1 SQL 作業領域の確保可能なサイズを減少させることで PGA サイズの合計がターゲット値を超えないように自動調整されます。
1 SQL 作業領域毎の確保サイズが減少するとソート処理やハッシュ結合を実施する場合に処理に必要な PGA メモリが確保できなくなるため、一時表領域への読み書きによる I/O 増加で処理時間が大きく増加する可能性があります。
各処理で適切な PGA メモリが確保できているかどうかは、Advisory StatisticsのPGA Aggr Target StatsでPGA サイズの合計がターゲット値に近づいていないことや1 SQL 作業領域の確保可能サイズが減少していないこと、
PGA Aggr Target Histogramでマルチ・パスが大量に発生していないことを確認することなどで判断します。
適切なPGAメモリが確保できていない可能性が高い場合には、pga_aggregate_targetの拡張を検討します。
