「基本からわかる！高性能×高可用性データベースシステムの作り方」indexページ▶▶
今回は、SQL実行環境をチューニングするにあたり、Oracle Databaseでは何を調べるときにどんなツールが用意されているかを整理します。個別のSQLの実行計画から、データベース・サーバー全体のリソースの使われ方の分析まで、Oracle Databaseの性能統計モデルの解説から行います。
1 SQL実行
SQLという言語の特徴の一つに、入力と出力の対応の定義のみを記述するというものがあります。SQLを見ただけでは、それがどのような手続き的アルゴリズムで実行されるかはわかりません。Javaなどの手続き的言語でデータ処理をプログラミングする場合、プログラマがデータ処理のアルゴリズムを記述します。これに対し、SQLではDBMSが手続き的アルゴリズムを自動生成します。
一般的に、同じ出力結果を得るための手続き的アルゴリズムは複数存在します。Oracle DatabaseではSQLを実行するとき、表や索引の構造および値の分布を考慮し、複数のアルゴリズムの候補から実行時間が最小と推測したものを採用します。この自動生成された手続き的アルゴリズムをSQL実行計画と呼びます。
SQL実行計画はOracleサーバー・プロセス（フォアグラウンド・プロセス）によってCPU上で処理されます。Oracleサーバー・プロセスがCPUにスケジューリングされ、CPU上で処理が進行している時間をCPU時間（CPU Time）と呼びます。しかし、処理の途中で必要なデータ・ブロックがデータベース・バッファ・キャッシュ上になく、ストレージから読み込む必要がでる場合があります。このような時、CPU上でのOracleサーバー・プロセスの処理の進行は「待機」させられます。データ・ブロックのキャッシュ・ミスを例に挙げましたが、CPU上での処理の進行を待機させる事象には様々なものがあります。Oracle Databaseではこれらの一つ一つの事象に名前が付けられており、どんな事象でどのくらいの時間SQL処理が待機させられたかがわかるようになっています。これらの事象を「待機イベント」といい、その時間を待機イベント時間（Wait Time）と呼びます。
Oracleサーバー・プロセスのCPU時間と待機イベント時間の合計をDB時間（DB Time）と呼び、SQL実行時間の短縮はこのDB時間の短縮を目標とするものです。最適なSQL実行計画の生成はCPU時間を短縮し、ハードウェア・リソースの適切な使用は待機イベント時間を短縮します。
SQL実行中に発生した待機イベントに対して、SQL実行中でないOracleサーバー・プロセスの待機動作をもたらす事象を「アイドル待機イベント」と呼びます。OracleクライアントはOracleサーバーに接続しているけれども、SQLを発行していない時間を考えます。このとき、Oracleサーバー・プロセスは何もしていないのではなく、Oracleクライアントからのリクエストを「待機」しています。これらのアイドル待機イベントによる待機時間をアイドル待機イベント時間（Idle Wait Time）と呼びます。ほとんどの場合、アイドル待機イベント時間は性能分析の観点からは無視できます。そのため、Oracle Databaseの性能分析ツールはDB時間（=CPU時間+待機イベント時間）に着目してレポートします。
2 Oracle Databaseの性能分析ツール
Oracle Databaseにはいくつかの性能分析ツールが付属していますが、大別すると個別のSQL実行計画の分析にかかわるものと、複数セッションにわたるDB時間の分析にかかわるものに分類できます。
2.1 個別のSQL実行計画の分析
アプリケーション開発でSQLを記述している段階で行うことは、そのSQLを単発で実行したときに実行時間が要件を満たすようにSQL実行計画を決めることです。アプリケーションの性質がオンライン・トランザクション系なのか、分析系なのかで処理の傾向が異なります。
オンライン・トランザクション系のSQLは、表の中からごく少数の行を特定するものになる傾向があります。適切な索引を作成し、SQL実行計画は少ないデータ・ブロックへのアクセスになるよう目指します。そのため、個別のSQL実行時間は極めて短くなります。結合する表の数も少なく、索引アクセスになっているかを確認すればよいため、人間がSQL実行計画をみてもその良し悪しの判断が付きやすくなっています。
分析系のSQLは、表のかなりの割合の行にアクセスします。結合する表の数も多くなる傾向があります。そのため、索引を作成することが有効であるかの判断が難しくなります。索引の代わりにパーティショニングが効果的である場合があります。 オンライン・トランザクション系でも分析系でも、SQLチューニングの基本的な方針はアクセスするデータ・ブロック数を減らすことです。
SQL実行計画を調べる最も原始的なツールはEXPLAIN PLANやAUTOTRACEです。
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|
| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."EMPLOYEE_ID"<103)
5 - access("E"."JOB_ID"="J"."JOB_ID")
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
EXPLAIN PLANで推定されたSQL実行計画は実際に実行されるものと異なる場合があるので、簡易的な確認にしかなりません。正確なSQL実行計画を出力したい場合はDBMS_XPLAN.DISPLAY_CURSORプロシージャを使用します。
AUTOTRACEは実行時のデータ・ブロック・アクセス数なども出力できるため、EXPLAIN PLANよりももう少し有用です。
SQL実行計画に加えて、実行時にどのような待機イベントが発生したかの詳細を調べるにはSQLトレースを使用します。しかし、SQLトレースは莫大な量のログを生成するので、SQLトレースを取得すること自体がSQL実行時間に影響を与えます。また、一つ一つの待機イベント時間は極めて短いため、それらを把握しても削減すべき対象なのか判断するのは難しいところです。そのため、待機イベント時間の分析は個別の待機イベントを追跡するのではなく、待機イベントの種類ごとに累積した時間を検討します。そのためには後述するActive Session History（ASH）やAutomatic Workload Repository（AWR）レポートを使用します。
個別のSQLの実行時統計を調べるツールとして、Oracle Database 11gからリアルタイムSQL監視（SQL Monitor）という機能が導入されました。
リアルタイムSQL監視はOracle Enterprise Managerだけでなく、SQL開発に有用なクライアント・ツールであるSQL Developerからも使用することができます。そのため現在ではSQLトレースよりもリアルタイムSQL監視を使用します。リアルタイムSQL監視は以下のいずれかの条件を満たすSQLの実行時統計を取得します。
- パラレル実行された
- 5秒以上消費した
- /*+ MONITOR */ヒント文が付けられた
2.2 複数セッションにわたるDB時間の分析
Oracle Databaseは複数のセッションを同時に接続することができ、複数のOracleクライアントからのリクエストを同時並行で処理を進めることができます。各Oracleサーバー・プロセスはCPU時間、待機イベント時間、アイドル待機イベント時間を目まぐるしく遷移していきます。複数のOracleサーバー・プロセスのアクティビティを合算し、全体として時間を消費している要素は何かを調べるツールにActive Session History（ASH）とAutomatic Workload Repository（AWR）レポート、statspackレポートがあります。
2.2.1 Active Session History
Active Session HistoryはSQL実行状態にあるセッション（=アクティブなセッション）を1秒ごとにサンプリングし、全アクティブ・セッションのCPU時間、待機イベント時間を累積します。Active Session Historyのグラフの高さは、アクティブな状態のセッション数をあらわしています。占める割合の大きな要素を削減する（チューニングする）ことでSQL実行時間を削減できます。アクティブなセッション数がCPU数を超えていると、CPU数が不足しているかセッション数が多すぎることを示唆しています。CPUに同時にスケジューリングできるプロセス数の上限はCPU数であるため、それ以上の数のセッションがアクティブな場合、それらのセッションは何らかの待機イベントの状態として観測されます。
Oracle Enterprise ManagerのActive Session Historyの画面からは、時間消費の多いSQLやセッションを抽出することができます。Oracle Enterprise ManagerはASHとリアルタイムSQL監視を簡単に行き来することができるようになっています。
2.2.2 AWRレポート/statspackレポート
ここまでで紹介してきたリアルタイムSQL監視やActive Session HistoryはOracleサーバー・プロセス（フォアグラウンド・プロセス）の消費する時間に着目するツールです。時間消費以外のアクティビティにも着目するOracleサーバー全体の総合的な分析ツールがAWRレポート/statspackレポートです。歴史的にはstatspackがOracle8iで実装され、それを改良したAWRがOracle Database 10gで実装されました。性能試験もしくは本番稼働のOracle Databaseの性能分析を依頼されたとき、最初に尋ねるのが「負荷の高い時間帯のAWRレポートかstatspackレポートはありますか？」です。
ASHは1秒ごとのフォアグラウンド・プロセスのサンプリングでしたが、AWR/statspackはASHに比べるとかなり長い間隔のレポートを想定しています。AWRはデータベースを作成した時点で自動的にスナップショットの取得を開始しており、その間隔はデフォルトで1時間です。statspackはスナップショットを定期的に取得する仕組みを設定しなければいけません。Oracleインスタンスは自身のアクティビティ、例えばREDOログ生成量はOracleインスタンス起動時からの累積値を保持しています。AWR/statspackはこれらの統計値をスナップショットとして表に保存します。スナップショットを取得した任意の2点を指定すると、その差分を計算してその期間の性能統計レポートを作成します。
AWRレポートはかなり詳細な項目が出力されますが、主に見るのは以下の項目です：
- Load Profile
- フォアグラウンド・プロセス待機イベント時間
- リソース使用の多いSQL
- I/O統計
- セグメント統計
Load ProfileはこのOracleインスタンスの負荷の概観です。DB時間、CPU時間、アクセス・ブロック数、更新ブロック数、REDOログ量、I/O量、SQL数などがわかります。
SQLを実行する主体はフォアグラウンド・プロセス（Oracleサーバー・プロセス）なので、フォアグラウンド・プロセスの待機イベント時間の大きな割合を占めるものを削減できると、SQL実行性能の改善が期待できます。AWRレポートには累積時間の大きな順で待機イベントが並びます。負荷の小さな時でもとにかく累積時間の大きな順に出てくるので、性能に影響があるかどうか（チューニング個所の候補になるか）の判断が必要になります。そのため、チューニングの検討をするときは負荷の高い時間帯のレポートを参照します。
バックグラウンド・プロセスも待機イベントは観測されていて、AWRレポートにはこれらも出力されます。通常はあまり見ることはありませんが、Oracle Databaseでファイルに書くプロセスはLGWRとDBWRのバックグラウンド・プロセスなので、書き込みI/Oに疑いがある場合はこれらも参照します。
3 自動チューニング
ここまで紹介してきた分析ツールはOracleサーバーの状態を提示するためのものでした。その内容を解釈してチューニングを検討するのは人間の役割です。もう一歩踏み込んで、Oracle Databaseにはこれらの分析ツールの統計情報から、SQLの実行時間をさらに改善する余地があるかを自動探索させる機能があります。
AWRレポートにはOracleインスタンスのメモリ割り当てを増減させた場合のI/O量の推定などが出力されます。
SQLの実行に直接かかわる項目のアドバイザはSQLチューニング・アドバイザとSQLアクセス・アドバイザがあります。これらはAWRやASHなどから抽出されたSQLに対し、索引の作成などの提案を行います。
SQLチューニング・アドバイザとSQLアクセス・アドバイザはチューニング方法の提案を行い、それを実装するかの判断は人間が行います。Oracle Database 19cではさらに踏み込んで、索引の作成までを自動化するAutomatic Indexingの機能が実装されます。