門外不出のOracle現場ワザ

第4章 Oracleデータベースの頭脳 「オプティマイザ」徹底研究

日本オラクル株式会社 コンサルティング統括本部テクノロジーコンサルティング本部
小田 圭二(おだ けいじ)

目次

Part1 SQLを最適化するコストベースオプティマイザの基本機能

はじめに

RDBMSのオプティマイザ(クエリオプティマイザ)について皆さんはどれほど理解しているでしょうか。
オプティマイザはすべてのSQLの処理効率に関わるものであり、「RDBMSエンジンの最も重要なコンポーネント」と言っても過言ではありません。しかし一般には「アクセスパスを最適化するための内部エンジン」程度にしか理解されていないように思われます。
また、開発者やDB管理者の方々の中にはオプティマイザに振り回された経験をお持ちの方もいるのではないでしょうか。「意図した索引が使われない」とか「ANALYZEをしたら、突然パフォーマンスが劣化した」という話はよく聞きます。
そこで本章では、オプティマイザの基本的な動作から、Oracleのコストベースオプティマイザを使用する場合の勘所までを説明します。まず本パートでは、コストベースオプティマイザ 注1とルールベースオプティマイザを比較し、なぜコストベースオプティマイザを使用すべきなのか説明します。また、コストベースオプティマイザとは何なのか、その概要を説明します。
なお、本章の記載はOracle 10g(10.1.0.3)での検証結果に基づいています。今後のリリースにおいて動作が変わる可能性もあることをあらかじめお断りしておきます。

注1:SQL文の処理方法がいくつかある場合に、それぞれのコストを比較して最適な方法を選択するオプティマイザ。反対にルールベースオプティマイザは、あらかじめ設定されているルール(優先順位)に基づいて処理方法を選択します。

CBOを使用する理由

まず、アクセスパスを最適化する機能であるCBO(Cost Based Optimizer)とはどのようなものなのか、簡単に説明します。CBOを「どんなSQLでも高速に処理できるようにする魔法の杖」と認識しているとしたら、それは間違いです。
そもそも、オプティマイザはどんなSQLでも高速に処理できるわけではありません。極端な例ですが、問い合わせ結果にまったく関係ないテーブルをFROM句に記述した場合、オプティマイザはそのテーブルにアクセスせざるを得ません。通常はこの結果として発生する無駄な結合処理により、性能が大幅に劣化します(特にビューに対する問い合わせの実行時に、このような状況が発生することがあります)。
また、CBOを使う場合はANALYZE(統計収集)の必要があることはよく知られています。しかし、必要最低限の統計収集を行なっただけでは適切な実行計画を得られない場合があります。オプティマイザはある「仮定」の下に統計情報を参照してコストを計算しているため、この仮定が成り立たない環境では、最適な実行計画は得られません。例えば、列値のヒストグラム統計を収集していない環境では、CBOはすべての値が一様分布している 注2とみなします。そのため、せっかく作った索引が使用されないケースがあるかもしれません。
また、Oracle 10gではRBO(Rule Based Optimizer)がサポートされなくなりました。オラクルではOracle7でCBOがリリースされて以来、下位互換のためにRBOをサポートしていましたが、将来的にサポートされなくなるとアナウンスしていました。つまり、下位のバージョンでRBOを使用していた場合は、上位バージョンへのアップグレード時に大きな変更が必要になるということです。そのため、これからOracle 10g上で新たにシステムを開発する場合は、CBOを意識した開発や運用設計が不可欠となります。

注2:すべての値が同じ発生頻度で存在している状態。値の偏りを検出するためにはヒストグラム統計を収集する必要があります。

RBOのメリット/デメリット

RBOの後継としてCBOが登場してから10年以上が経ちますが、いまだにRBOで運用しているサイトが少なくないのも事実です。単純に、オプティマイザ統計の収集をしていない(忘れている?)ためRBOで動作しているというサイトもあると思いますが、次のような点をRBOのメリットとして評価している方もいるでしょう。

  1. 開発者にとってRBOの考え方は理解しやすい。ルールを覚えてしまえばチューニングしやすく、機能も少ないため既存の知識が応用できる
  2. RBOは機能も限られており、技術的に枯れているため安心感がある
  3. SQL実行計画の変動がほとんど起こらないため、運用中のアクセスパスの変更により突然の性能劣化を起こすといったトラブルが起こりにくく、手離れが良い

一方、RBOを使用する際の最大のリスクは、データの変動に追随できないことと言えます。CBOを使用せずRBOで運用している場合、データの変動に追随するためにはSQLの書き直しが必要になることがあります。
また、RBOでSQL実行計画を作り込むと、その後のアプリケーションのメンテナンスも困難になります。なぜならRBOではSQL文内に記述される項目の順番や索引を作る順序などが重要な意味を持つため、コードのわずかな修正や索引のメンテナンスが原因でパフォーマンスに大きな影響を与える可能性があるためです。
さらに、RBOに固執する場合、Oracle7以降、バージョンを重ねるたびに追加されてきたオプティマイザに対する機能強化の恩恵をまったく受けられないことになります。新機能を利用することで、非常に高速に、かつ少ないリソースで処理可能な業務があるかもしれません。実際、RBOでは何時間もかかる問い合わせが、CBOのみが使用できる結合方法を使用すると数分で完了するというケースもあります。

SQL文の処理におけるオプティマイザの役割

まずはオプティマイザとは何をしているものなのか、概要を見てみましょう。

SQL文の処理の流れ

SQL文の処理において、オプティマイザがどのように関わってくるかを簡単に説明します。図1は、SQL文の処理の概要を示したものです。処理の流れにおけるオプティマイザの位置付けを理解しやすいように簡易表示にしてあります。

図1 SQL文の処理ステップ
図1 SQL文の処理ステップ

SQL文の処理には次の2つの方法があり、オプティマイザが関わる場合と関わらない場合に分かれます。

  1. SOFT PARSE
    発行されたSQL文は、パーサによってパース(parse:解析)されます。パーサはSQL文をコンポーネントに分解し、文法的に正しいか、意味は正しいか(例えばその表に存在しない列名を指定していないかなど)をチェックします。
    次にその文がパースされた結果が、共有プール(SGA:システムグローバル領域)にすでにキャッシュされているかどうかをチェックします。共有プールにキャッシュされている場合は、すぐにそのSQL文を実行できます。このような処理の流れをソフトパース(SOFT PARSE)と言います。
  2. HARD PARSE
    共有プールにキャッシュがなかった場合、そのSQL文ははじめて実行されたものか、もしくは共有プールからキャッシュアウトしていたと考えられます。このようなケースではハードパース(HARD PARSE)が行なわれます。オプティマイザが利用されるのはハードパースの場合のみです。
    ハードパースでは、オプティマイザのアウトプットとして「問い合わせ実行計画(QEP: QueryExecution Plan)」が作られます。実行計画とは、表からどのようにデータを取り出し、どういう順番で結合し、どういう結合方法を選択するかといったプランのことです。次に、オプティマイザが作り上げた実行計画を行ソースジェネレータが受け取り、この後の実行フェーズで必要なデータ構造を生成します。実行フェーズでは、行ソースジェネレータのアウトプットを利用してSQLを実行します。DDLや更新DMLの場合はこのフェーズで処理は完了しますが、問い合わせの場合にはフェッチ処理(データの取り出し)が行なわれます。
COLUMN:ハードパースとソフトパース

本文からも分かると思いますが、ハードパースはソフトパースに比べて重い処理です。したがって、パフォーマンスの高いシステムを設計するためには、まずはハードパースを減らす必要があります。このためには、次の点に注意します。

  • 同じ構文のSQLが確実に共有されるように記述法(大文字、小文字、空白の位置など)を統一する
  • バインド変数を使用してSQLを記述し、条件指定される値の違いによって共有プール内で別オブジェクトとなることを防ぐ
  • 共有プールを適切にサイジングし、必要なオブジェクトがキャッシュアウトされるのを防ぐ

処理投入頻度が高いシステムでは、さらに「パースなし」でのSQL再実行を検討する必要があります。プリコンパイラ 注3を使うアプリケーションなどでは、カーソルキャッシュを適切に使用することで、ソフトパースさえ行なわずにSQLを再実行できます。

オプティマイザが行なう処理

続いて、オプティマイザの処理をもう少し詳しく見ていきましょう。図2はオプティマイザのコンポーネントと処理ステップを表わしています(ここではCBOのみを対象としています)。

図2 オプティマイザのコンポーネント
図2 オプティマイザのコンポーネント

オプティマイザの最初の処理は、問い合わせの変換(変形)です。これはさまざまな形式で記述されているSQL文を等価な文に変形し、選択可能な実行計画を増やすために行なわれます。具体的には、問い合わせトランスフォーマによりビューや副問い合わせを展開して通常の結合にしたり、逆にビューの中に外側の条件句を追加したり、マテリアライズドビュー(実体のあるビュー)を参照する問い合わせへ変換するといった処理が行なわれます。
次に、エスティメータが変換された問い合わせを利用してさまざまなプラン(問い合わせ実行計画)を生成し、それぞれのコストを見積もって比較した上で、プランジェネレータが最適な実行計画を選択します。
プランのコスト見積もり時には、データディクショナリに格納されたオプティマイザ統計が利用されます。事前に統計が収集されていない場合には、動的サンプリング 注4が行なわれたり、内部デフォルト値が使用される場合もあります。
なお、オプティマイザ統計とは、以下のような情報です。

  • 表統計
    • - 行数
    • - ブロック数
    • - 平均行長
  • 列統計
    • - 列内の個別値数(NDV:Number of Dist inct Values)
    • - 列内のNULL数
    • - データ分布(最大値/最小値/ヒストグラム)
  • 索引統計
    • - リーフブロック数
    • - レベル(ツリーの高さ)
    • - クラスタリングファクタ
  • システム統計
    • - I/Oパフォーマンス
    • - CPUパフォーマンス
POINT

オプティマイザの基本に関するポイントをまとめます。

  • オプティマイザのアウトプットはSQL実行計画。実行計画とは、オブジェクトへのアクセスの仕方(アクセスパス)やアクセスする順序、結合方法などのプラン
  • オプティマイザはSQL文が発行されるたびに起動されるのではなく、共有プール内に解析済みの表現(共有カーソル)が存在した場合には起動しない
  • ハードパースによって共有カーソルが共有プールにキャッシュされる
  • オプティマイザは事前に収集されたオプティマイザ統計を利用してコストを計算する
  • オプティマイザは同一のSQL文に対して多くの異なる実行計画を生成し、それぞれのコストを見積もって比較することで、最適な実行計画を選択する
注3:Oracleプリコンパイラ製品。「Pro*C」や「Pro*COBOL」を使用したアプリケーションのこと。
注4:SQLの実行時(ハードパース時)に動的にサンプリングを行なって統計情報を収集し、その結果を元に実行計画を生成すること。

"門外不出のOracle現場ワザ" インデックスに戻る

Copyright © 2009, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず

この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。

小田 圭二 小田 圭二(おだ けいじ)
1996年日本オラクル入社。人事教育本部にて、新卒や中途採用社員に対し、データベースやOS、ネットワークの講師を5年ほど経験した後、2000年にテクノロジーコンサルティング本部に異動。 テクノロジーのコンサルタントとして、主に大規模ミッションクリティカルシステムを担当。
ポリシーは、「OracleもOS上で動くアプリケーションにすぎない。だから、OS、ストレージ、ネットワークを学ぶべき」。 スキル面の興味は、アーキテクチャ、DBA、インフラ技術、教育、コンサル手法など。