門外不出のOracle現場ワザ
日本オラクル株式会社 コンサルティング統括本部テクノロジーコンサルティング本部 小田 圭二(おだ けいじ)
第4章 Oracleデータベースの頭脳 「オプティマイザ」徹底研究
日本オラクル株式会社 コンサルティング統括本部テクノロジーコンサルティング本部 小田 圭二(おだ けいじ)
目次
- Part2 CBOは何を見てどう判断するのか
- Part3 オプティマイザ統計の管理
- Part4 実行計画の固定と自動チューニングオプティマイザの活用方法
Part4 実行計画の固定と自動チューニングオプティマイザの活用方法
実行計画を固定する機能
オプティマイザヒント
統計情報の再収集によってオプティマイザが実行計画を変え、結果的に性能の劣る計画を選択してしまう可能性があることはパート3で説明しました。 業務上、極めて重要なSQL文については、人間が最適な実行計画を判断し、変動しないように固定するほうが良い場合があります。極めて重要なSQL文とは、次のようなSQL文です。
実行頻度が極めて高い
当該SQLの遅延がアプリケーション全体の性能に重大な影響を与える
このようなSQL文については、「オプティマイザヒント」を使用して実行計画を固定することをお勧めします。 ヒントの記述法は次のとおりです。
{DELETE|INSERT|MERGE|SELECT|UPDATE}
/*+ hint [text] [hint[text]]... */
または
{DELETE|INSERT|MERGE|SELECT|UPDATE}
--- hint [text] [hint[text]]...
ヒントの記述に文法的な誤りがあってもエラーは発生せず、単に無視されます。また、お互いに矛盾がある複数のヒントや、物理的に実行不可能なヒントも無視されます。したがって、ヒントを使用して実行計画をチューニングする場合は、意図どおりに計画が変更されているかを必ず確認してください。
なお、ヒントの使用は最小限にするように心がけてください。実行計画が固定されることは、RBOと同様にデータの変動に追随できないことを意味します。最適な実行計画が変わり得るようなSQL文については、ヒントを使用しないことをお勧めします。
SQLにオプティマイザヒントを記述することで、実行計画の手動制御と固定が可能
ヒントの使用は極めて重要なSQLのみとする。最適な実行計画が変わり得るようなSQLについては、なるべくヒントを使用しない
プランスタビリティ
実行計画を固定する方法としてSQL文にヒントを埋め込む方法を説明しましたが、「パッケージアプリケーションであるため、SQL文の書き換えができない」というケースもあります。また、ヒントの使用は極めて重要な一部のSQL文のみに限定することをおすすめしましたが、要件によってはシステム全体（もしくは大部分）のSQL文について実行計画を固定する必要がある場合もあります。そのような要件として、以下のものが考えられます。
運用中の実行計画変動のリスクを限りなくゼロにしたい
開発環境での実行計画を本番環境で確実に再現する必要がある
Oracleのバージョンアップを考えているが、バージョンアップ後も現在の実行計画を確実に保持したい
RBOからCBOに移行する必要があるが、RBOで使用していた実行計画を確実に保持したい
「プランスタビリティ」は、これらのようなケースにおいて最適な機能です。プランスタビリティは、SQLの実行計画をストアドアウトラインとしてディクショナリ（OUTLNスキーマ内の表）に格納します。ストアドアウトラインとは、その実行計画を再現するためのオプティマイザヒントのセットです。プランスタビリティが有効にされたセッションで、ストアドアウトラインが作成されたSQL文を実行すると、格納されたオプティマイザヒントが取り出され、そのSQL文に適用されます。
使用方法の例を見てみましょう。ここではパート2の「高さ調整ヒストグラム」で利用した、次のSELECT文の実行計画の固定を考えます。
●ポピュラー値を指定
select count(cul1) from TEST1 where Cul3=30;
このSELECT文を実行すると、100万行中の10万行（10％）がヒットします。すでに説明しましたが、バケット数が20のヒストグラムを作成すると偏りが検出され、フルスキャンが選択されます。 現状の実行計画はLIST1のとおりです。
LIST1 現状の実行計画
このSELECT文の最適なアクセスパスは現状のアクセスパス（フルスキャン）であるとして、以下の手順でストアドアウトラインを作成します。
ストアドアウトライン作成の開始を宣言
alter session set CREATE_STORED_OUTLINES=true;ストアドアウトラインを作成する対象のSQL文を実行
select count(cul1) from TEST1 where Cul3=30;ストアドアウトライン作成の終了を宣言
alter session set CREATE_STORED_OUTLINES=false;
ここではカレントセッションで実行した1つのSQL文のアウトラインのみを生成していますが、システムレベルでアウトライン作成を宣言し、適切なワークロードがかけられた後に生成終了を宣言すれば、アプリケーションが発行するすべてのSQL文についてアウトラインを作成して格納できます。ストアドアウトラインが作成された結果は、LIST2のとおりです。
LIST2 ストアドアウトラインの作成結果
アプリケーションが発行するSQL文の大部分について実行計画の変動を回避する必要がある場合や、SQL文にヒントを記述できない場合は、プランスタビリティを利用する
プランスタビリティはSQL実行計画を再現するためのヒントセット（ストアドアウトライン）を生成している。ストアドアウトラインは編集可能であり、別のDBへの移行も可能
オプティマイザ統計を再収集した場合
ここで、TEST1のオプティマイザ統計を、ヒストグラムのバケット数を1として再収集してみましょう。統計再収集後に同じSELECT文の実行計画を確認すると、LIST3のようにインデックスアクセスに変わっていることが分かります。
LIST3 統計再収集によって変化した実行計画
ストアドアウトラインを使用するモードで、再び実行計画を確認してみます。
ストアドアウトラインの使用を宣言
alter session set USE_STORED_OUTLINES=true;EXPLAIN PLANで実行計画を確認
explain plan for select count(cul1) from TEST1 where Cul3=30;
LIST4のような実行計画が確認できました。
LIST4 ストアドアウトラインが適用された実行計画
このように、ストアドアウトラインを使用すると、SQL文を修正することなく、ヒントを記述した場合と同等の固定化を実現できます。 また、プランスタビリティは以下のような使い方もできるため、適切に利用することで効率的な運用が可能になります。
ストアドアウトラインのカテゴリ化 同じSQL文に対して複数のバージョンのアウトラインが必要な場合、別カテゴリにできる
未使用のストアドアウトラインの一括削除 領域を有効活用するため、生成したが利用されていないアウトラインを一括削除可能
ストアドアウトラインのヒントを編集して実行計画を手動制御 単なる実行計画の固定だけでなく、アウトラインヒントの編集が可能
ストアドアウトラインのデータをエクスポートしてほかのDBへ移行 テスト環境で生成したアウトラインを本番環境へ移行可能
>自動チューニングオプティマイザ
本稿の目的は、SQLのチューニングテクニックをただ紹介するのではなく、「オプティマイザとはそもそもどういうものなのか」を理解することにあります。オプティマイザの動作原理を理解していれば、チューニングの場でも応用が効くと言えるでしょう。とはいえ、実際のSQLチューニング時には、もっと基礎的な（表面的な）多くのルールを知っておかなければなりません。例えば「否定形の条件句を指定した場合、Bツリー索引は使用されない」とか、「列に関数を適用すると索引が利用されない」といったものです。
このようなルールをできるだけ多く知っていることが、開発者やチューニング担当者の「スキル」であると言えます。しかし、このようなノウハウがあまりない状態で開発が進められていくケースもあると思われます。その場合にOracle自身がルールをチェックし、効率的なSQL文の書き方を教えてくれるとしたらどうでしょう。また、コーディングガイドを整備して開発するようなケースでも、すべてのSQL文がガイドに従っているかどうかを確認するのは困難です。そのような場合にOracleが自動的に問題のあるSQL文を特定し、チューニング方法を提案してくれるとしたら、効率的です。 Oracle 10gではこのようなチューニング支援をするオプティマイザの機能が強化されています。Oracle 10gには、オプティマイザの動作モードとして「ノーマルモード」と「チューニングモード」の2つがあります。なお、チューニングモードで動作する際のオプティマイザを「自動チューニングオプティマイザ」と呼びます。
ノーマルモード ノーマルモードは、以前からある通常のオプティマイザのモードです。実行計画の最適性と、それを得るために要する時間のバランスを考慮するので、長時間かけて最上の実行計画を探索しません。「準最適」な実行計画をできるだけ早く見つけ出すモードとも言えます。
チューニングモード チューニングモードは、10gから新たに追加されたモードです。ノーマルモードでは行なわない追加の分析を行ない、さらに良い実行計画がないかをチェックします。時間をかけて最も優れた実行計画を探すアプローチと言えます。システムの性能に大きな影響を与える複雑で高負荷なSQL文のチューニングを支援するために用意されたモードです。
SQL Tuning Advisor
自動チューニングオプティマイザは、「SQLTuning Advisor」と呼ばれるサーバーユーティリティを通して利用されます。SQL Tuning Advisorの主要なインターフェイスは、Oracle Enterprise Managerです。
SQL Tuning Advisorの使用例 次に、SQL Tuning Advisorの使用例を紹介します。 Oracle 10gではデフォルトで「自動ワークロードリポジトリ（AWR：Automatic Workload Repository）」と「自動パフォーマンス診断（ADDM：Automatic Database Diagnostic Monitor）」が有効になっています。まず、これらの機能を簡単に説明します。
Oracle 10gは定期的に（デフォルト：1時間ごと）DBのあらゆるパフォーマンス統計をDB内に格納し（AWR）、その統計を分析してパフォーマンスに問題が起きていないかどうかを調べ、問題を発見した場合は推奨事項とともに報告します（ADDM）。ここでは、ADDMの報告を契機としてSQL Tuning Advisorを実行し、自動チューニングオプティマイザの診断結果を確認する例を示します。
手順1：パフォーマンスの分析
画面1はOracle Enterprise Manager 10gDatabase Contrulのトップ画面の一部です。
画面1 ADDMによるパフォーマンス分析結果
自動的に行なわれているADDMによるパフォーマンス分析の結果として、何種類かの問題が起きていることを報告しています。「データベース処理に長時間かかるSQL文が見つかりました。」をクリックし、詳細を確認します。
手順2：アドバイザの実行
処理に時間のかかっているSQL文が4つ挙げられており、それぞれについてSQL Tuning Advisorの実行が推奨されています（画面2）。
画面2 ADDMによるパフォーマンス分析結果の詳細
一番上の「select /* IMPLICIT FUNCTION USE */ * from TEST1 where cul1 like '10000％'」 注20に対してSQL Tuning Advisor 注21を実行します。［アドバイザをただちに実行］ボタンをクリックします。
手順3：実行結果の出力
SQL Tuning Advisorが実行されると、やや時間を要しますが画面3のように実行結果が出力されます。
画面3 SQL Tuning Advisorによる分析結果
SQL Tuning Advisorは、このSELECT文に対してSQL文の書き方を見直すように助言しています。ここでは「Cul1 LIKE '10000％'」という条件を指定していますが、実はCul1列はNUMBER型の列だからです。このような記述をすると、左辺のCul1に対して「暗黙の型変換（implicit data type conversion）」が発生し、Cul1列の索引が使用できなくなってしまいます。このため、この文は必ずフルテーブルスキャンを行ないます。これが、このSELECT文に時間がかかる原因だと分かりました。 この例の場合、暗黙の型変換により、Cul1に対してTO_CHAR（）関数が適用され、内部的には「TO_CHAR（Cul1）LIKE‘10000%'」という条件句として処理されています。例えば、この文が対象としているデータが6桁の数値に限定されるのであれば、「Cul1 BETWEEN 100000AND 100009」のように条件句を書き直すことで、NUMBER型の列に作成された索引を利用できるようになります。 暗黙の型変換による索引の不使用などはSQLを見ただけでは分からないものですが、SQLTuning Advisorは正しく検出しています。
注20：/* IMPLICIT FUNCTIONUSE */ はオプティマイザヒントではなく、SQL文を分かりやすくするための単なる「コメント」であり、SQLの実行計画などに何の影響も与えません。ヒントの場合は/*+ */で囲いますが、+記号がない場合はコメントとして扱われます。
注21：SQL Tuning Advisorを使用する場合は、Oracle Enterprise Managerのライセンスが必要になります。
10gでは自動チューニングオプティマイザ機能が追加された。この機能はSQL Tuning Advisorから利用可能であり、通常のモードのオプティマイザでは実施しない追加の分析作業を行なう
10gで利用可能な自動パフォーマンス診断機能と併用することにより、パフォーマンス問題の特定からチューニング方針の決定までを最適パスで行なえる
CBOを使いこなすためには
CBOの本質を理解する
CBOの本質を理解するために、もう一度RBOと比較してみましょう。 図1はRBOにおけるデータの変動（データ量、ヒット件数など）と、性能（レスポンス）の推移を概念的に表わしたものです。
図1 RBOのデータ変動と性能の推移
RBOでは実行計画が変動しないため、データの変動に伴って初期性能から徐々に性能が劣化していきます。最終的に、ユーザーが耐えられないレスポンスになったところでSQL文をチューニングします。データ変動のペースがゆっくりであれば、性能のぶれがほとんどなく安定した性能が期待できます。 これに対し図2は、CBOのデータ変動と性能の推移を表わしたものです。
図2 CBOのデータ変動と性能の推移
図の「実行計画A」と「実行計画B」は、それぞれデータの変動に対して相反する性能特性を持つ2種類の実行計画と考えてください（例えばフルスキャンとインデックスアクセスなど）。ここで初期性能は、実行計画Aで得られていたとします。データの変動に伴って、実行計画Aで得られるレスポンスは徐々に劣化していきます。このとき統計を収集していなければ、RBOの場合と同様に性能が劣化し続けます。
統計情報が適切に再収集されていれば、どこかの時点で実行計画Bに切り替わります。実行計画AでもBでも性能が変わらない点が性能臨界点です。この臨界点で実行計画が切り替わるのが最適なのですが、現実にはその近傍で切り替えが発生します。性能臨界点より前に実行計画の切り替えが起これば、「統計収集によって性能が劣化した」という状況が起こります。逆に臨界点よりも遅れて切り替えが起これば、「統計収集によって大幅に性能が改善した」という状況になります。
CBOでは、このような「動的な実行計画の切り替え」が性能臨界点の近傍で起こるため、性能のぶれが生じます。これがリスクとなるため、実行計画の切り替えと真の性能臨界点を近づけることが、CBOによる運用の最終目的と言えます。
切り替えが臨界点からずれて発生する理由は、一言で言えば「オプティマイザは性能（コスト）をモデル化して評価しているが、そのモデルが現実と完全に一致することはあり得ないため」です。オプティマイザが使用しているモデル（もしくは仮定）については、パート1で説明しました。パート1で説明していることだけがすべてではありませんが、オプティマイザが統計から何をどのように読み取っているのか、現実とは異なる可能性が高い仮定にはどのようなものがあるのかを紹介しました。パラメータの設定によってオプティマイザの仮定を補正できますが、システムの状態は常に変動するので、どんなケースにも対応できる最適値というものは有り得ません。統計の精度や鮮度、付加的な統計（システム統計、ヒストグラムなど）の収集や初期化パラメータなどに十分注意すれば性能のぶれの幅を最小化できますが、決してゼロにはできないのです。したがって、極めて重要なSQLについてはヒントを使用して実行計画を固定化すべきですし、統計の再収集によって性能問題が発生した場合は、すぐに以前の統計情報に戻せるような仕組みが必要です。
おわりに
Oracle 10gではCBOの運用に関するユーザーの負担を軽減するために、自動統計収集や統計情報の自動保存が可能になりました。また、性能に問題があるSQLを自動的に検出し、チューニング方針をOracleに考えさせることもできます。
Oracle DBAの方々には、前述のようなCBOの本質を理解した上で、これらの自動化機能を効果的に使用していただければと思います。オプティマイザに関する運用設計時やトラブル時の問題解析に、この記事の内容がお役に立てばうれしく思います。
Copyright © 2009, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。
Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。
小田 圭二(おだ けいじ) 1996年日本オラクル入社。人事教育本部にて、新卒や中途採用社員に対し、データベースやOS、ネットワークの講師を5年ほど経験した後、2000年にテクノロジーコンサルティング本部に異動。 テクノロジーのコンサルタントとして、主に大規模ミッションクリティカルシステムを担当。 ポリシーは、「OracleもOS上で動くアプリケーションにすぎない。だから、OS、ストレージ、ネットワークを学ぶべき」。 スキル面の興味は、アーキテクチャ、DBA、インフラ技術、教育、コンサル手法など。