SQLチューニングについて思うこと [オプティマイザ]

OracleのDBAとして永遠の課題と思っていることの一つにSQLの性能をどう担保していくべきか、という話があると思っている。今回は日ごろ感じている出口のないこの問題と、最近手にしたSQLチューニングの本でとても良い本があったので、紹介しておきたい。

1.はじめに

運用中のシステムでSQLの遅延が発生した際、実行計画の変化が原因のケースがある。多くは統計情報が最新化され、ハードパースが走った際にオプティマイザが実行計画を変更してしまう、というケースである。オプティマイザが良かれと思って実行計画を変更する訳であるが、不幸にも極端に性能が悪くなることが多々発生する。大規模システムにおいて、性能が極端に悪化するリスクは避けなければならない。このようなことから、多くの場合、以下の方法を複合的に採用することにより安定的な性能(すなわち変動しない実行計画)を(完全ではないにしろ)実現している。

(1)SQLにヒントを埋め込み、実行計画を固定化する
(2)統計情報の変動を抑えるために、統計をロックする
(3)SPM等の実行計画を管理する手法を使う

2.ヒントについて

説明するまでもないが、ヒントはSQL文の中に実行計画を指定するためのコメントを入れることにより、オプティマイザが設計者の意図したとおりの実行計画を選択し易いようにする構文である。多くの場合、駆動表や結合順、結合方法、使用する索引、パラレル度、などを指定するために利用される。これにより、統計情報がどのような状態にかかわらず、意図した実行計画に固定することが可能となる。

一見、これにより問題は解決するようであるが、この手法を積極的に採用するには以下の点で議論がある

・SQLにヒントを入れ、維持し続けるためのコスト(DBA体制の維持費)の問題
・SQLに非機能のロジックが埋め込まれることによるAP保守性低下の懸念(アップグレード時にヒント見直しなど)
・統計情報が適切に取得されていれば、オプティマイザはヒントなしでも良い実行計画が選択できる、という考え方の排除(Oracleの製品開発投資に対し高いライセンスを払っている訳で、それを活用していないのではないか)

従って、ヒントを入れるにしても実際はすべてのSQLにヒントを入れるのではなく、重要なSQLや運用中遅延が発生したSQLに限りいれているというところが多いのが実情だろう。

3.統計情報のロックについて

一方、統計をロックにより実行計画を安定化させる手法は、ヒントより間接的に実行計画を安定化させる方法である。これもシステムによってさまざまな方法があるが、概ね以下の3通りの手法があるだろう。

・ある時点の統計でロックする(特にテンポラリのテーブルなどゆらぎの大きいものなど)
・適当な値を設定(dbms_stats.set_table_stats等)しロックする
・NULL統計(デフォルト統計)としロックする

統計ロックは、ある時点の統計で統計情報をロックし、自動統計取得対象外とする手法である。これにより、統計情報がかわらないので、当然、オプティマイザの挙動は安定する。また、統計情報の取得にかかわるシステムの負荷を少なくできることは利点である。しかし、少し考えるといろいろな懸念があることがわかる。どの時点の統計でロックすべきか(本番環境の統計を正とすべきか、性能試験で実績の積んだテストデータの統計にするのか)。統計のリリース方法はどうするのか。カラム追加時の統計はどうするのか。日付や数値等のカラム統計は、ロックしてしまうと最大・最小値などのヒストグラムが実情に合わなくなるが、はたして問題ないのか、など懸念は尽きない。

適当な値を設定する方法は、NUM_ROWSやAVG_ROW_LENGTH,BLOKCS等をdbms_statsで設定する方法である。これは比較的容易に統計を設定することができ、かつ、DDLと一緒にリリースできるため、管理の容易である。しかし、どこまで設定するべきか、という議論となると、答えは難しい。結局、オプティマイザがどこまで設定すれば良いという答えは出ないため、管理の容易性を優先し、表の主要な統計のみを設定するにとどめるしかない。しかも、この手法はそもそもOracle非推奨である(dbms_statsパッケージの説明に記載されている)。

NULL統計は統計がNULL(欠落)しているときのデフォルトの挙動をあえて利用する手法である。実は統計NULLの場合は、テーブルのセグメントサイズ(HWM下ブロックサイズ)をベースに1行100バイトと仮定してNUM_ROWSを推定する。セレクティビティも同様に等価は0.01(索引アクセスなら0.004)、等価以外は0.05(索引アクセスなら0.009)等、決めうちの値をもとに推定するので、それなりの精度でオプティマイザは動いてくれる。しかし、セグメントサイズは動的に変更されるので、その意味でNULL統計ロックは統計をロックできていない。したがって、ハードパースのタイミングや再起動のタイミングで実行計画が変更してしまうことは十分起こりうる手法である。また、今は12cのマニュアルからNULL統計の記載がなくなった。内部的な挙動は今も同じらしいが、NULL統計の挙動についてサポートに問い合わせてもまともな回答が返ってこないことから、基本的に使わない方が良いだろう。

4.SPMについて

SPM(SQL Plan Management)とはSQLの実行計画を管理するための機能で、Oracle11gより提供されている(それまではプラン・スタビリティという機能があった)。SPMでは、SQLの実行計画を事前に保存(SQL計画ベースライン)しておき、SQLの実行計画が変動しないようにすることができる。

SPMの実態は内部的にはヒントの集合であり、それがSQL(正確にはSQL_ID)毎にOracle内部(SYSAUX)に格納されているに過ぎない。オプティマイザはハードパースの際に、このベースラインの実行計画とハッシュ値で比較し、一致していなければ格納されたヒントで再度ハードバースし、ベースラインの実行計画で実行する。このため、SPMではハードパースが2回実行されることとなる。

APに手の入れられない状況、例えばシステムの運用開始の直前や、パッケージ製品の内部SQLのチューニングであれば、SPMを用いて局所的な対処を行えることが有用であろう。逆に、システムの全業務SQLに対してSQL計画ベースラインで管理しようとすると、ヒントの代わりにこのベースラインを維持管理していく必要があるだろう。

5.結局どうしたらよいのか

Oracleのセミナ等では結局ケースバイケースです、といった結論で終わることが多い。しかし、安定した性能、ひいてはDBAの生活の質の向上を考えるなら、おのずと答えが見えてくる。SQL性能にまつわる多くの問題は実行計画の変動であることを考えれば、その変動する要因を極力取り除くことが有効であると考える。それは以下①~⑥の流れで発生することから、それぞれに対してブロックするような対策を考えてゆけばよい。

①データの変動→許容する
②自動統計→許容する
③統計情報が変更される
 →上で述べた統計ロックの手法を採用し変更されないようにする
  ※特に一時テーブルや超大規模なテーブル等
④ライブラリキャッシュからエージアウト
 →バインド変数を使うことを徹底する(コーディング規約等)
 →共有プールに十分なメモリを確保する(shared_pool_size)
 →カーソルをPIN(dbms_shared_pool.keep)しage outを防ぐ
⑤ハードパースが走る
 →ヒントを使い、実行計画を明示的に指定する
 →dynamic samplingは無効化し、サンプル統計取得による実行計画の変動を抑える
 →12c新機能である適応問い合わせ最適化は停止(OPTIMIZER_ADAPTIVE_REPORTING_ONLY=TRUE)し、実行計画が変更されないようにする
 →ハードパースが走っても実行計画が変動しないようSPMを採用する
⑥実行計画が変わる

6.最後はやはりヒント

しかし、上記のように対策したとしても、最後のチューニングはやはりヒントであろう。ヒントはSQLに埋めるため、局所的な対処が可能(他への影響をほとんど考慮する必要ない)ことから、唯一無二のチューニング方法となることが多い。また、ヒントには実行計画をかなり細かく記載することができるため、非常に強力である。しかし、それを正確に理解し、SQLに入れられるレベルにはかなり個人差があることを理解しておかなければならない。現場でいろいろなヒントを見るが、意図が不明なもの、中には間違ったものまであったりする(恐らくわかってはいるものの、そのためにAPを修正する訳にもいかず、ずっと残ってしまっている状態なのだろう)。

私の経験上、多くのSQLのチューニングで使用するのは、実行順を指定するORDERED,LEADING、結合方法を指定するUSE_NL, USE_HASH、索引を指定するINDEX、フルスキャンを指定するFULLくらいである。ヒントの使い方自体はそれ程難しいことはない。その一番難しいところは、あるべき実行計画をイメージする部分である。業務(APの開発者)であれば、直感的にどのテーブルから、どのような順番でアクセスすべきかイメージできるだろう。しかし、SQLと実行計画だけ渡されてチューニングするとなると、遅い原因はわかっても、あるべき結合順をイメージするのは難しいため、結局思考錯誤で対応することが多かった。

そんな中で出会ったのが、Kevin Meade氏のOracle SQL Performance Tuning and Optimization: Its all about the Cardinalitiesである。カーディナリティからSQLの結合順を導くFRP法(Filtered Rows Percentage Method)について記している。これは、クエリの実行計画の中で、常に処理する行数をできる限り少なく制御する、つまり、別の言葉でいうと、クエリの実行段階のできるだけ早い段階で、より多くの行を取り除く、という方法である。

基本的に、表の結合順をFilterによる絞り込み率が大きい順に並ぶように実行計画をチューニングするという考え方である。例えば3つの表があれば、結合順は6通りあるが、各表の絞り込み率が大きい(より絞り込みができる)表を駆動表に、その後は絞り込みができる順に表を結合していく。もちろん、実際は結合可能な表の順番に制約があるのでその制約を加味した上での話である。

この本の5章にヒントについての記載があるが、この思想が興味深い。そもそもヒントはチューニングの手段として考えるべきではなく、他の方法がどうしても有効でない場合に使うべきである、というのだ。つまり、ヒントは実行計画を強制するものではなく、オプティマイザが最適な実行計画を導き出すために何が問題で何を変更する必要があるのかを解析するためのツールとして認識すべきである、という見方である。また、SQLチューニングの3つの要素~カーディナリティ、駆動表と結合順、結合方法~を制御するため、ヒントの中で最も重要なもの3つは以下であると述べている。

・CARDINALITY / OPT_ESTIMEATE
・OREDERD / LEADING
・NO_INDEX

なお、本書の一章は無償で公開されている。ここに著者の提唱するFRPの具体的なやり方が記載されているので、一読の価値はあるだろう。また、本書の中で利用されているスクリプトもここからダウンロードできる。SQLチューニングの際に取得すべき情報は、チューニングを依頼する際にこれだけはそろえてほしい情報が記載されている。現場ではあいまいな情報だけで無茶な依頼が来ることもあるので、こういった基本事項を明文化しておくことは大切なことだと感じる。

http://www.orafaq.com/forum/m/624976

Oracle SQL Performance Tuning and Optimization v26 chapter 1.pdf
 →本書の1章 駆動表と結合順 が読めます
scripts.rar
 →本書の中で利用されているスクリプト一式
Information needed in Tuning a SQL Query.docx
 →SQLチューニングの際に取得すべき情報


以上

◆参考

Oracle SQL Performance Tuning and Optimization: It's All About the Cardinalities

Oracle SQL Performance Tuning and Optimization: It's All About the Cardinalities

  • 作者: Kevin Meade
  • 出版社/メーカー: Createspace Independent Pub
  • 発売日: 2014/09/16
  • メディア: ペーパーバック



nice!(0)  コメント(0) 

nice! 0

コメント 0

コメントを書く

お名前:
URL:
コメント:
画像認証:
下の画像に表示されている文字を入力してください。

※ブログオーナーが承認したコメントのみ表示されます。