SQLチューニング　2nd Season（第2回）

「SQLチューニングはどのように取り組んでいくべきか？」(2/3)

いつもSQLチューニングブログをご愛読いただきありがとうございます。

今回は2nd Season 第2回目をお届けしていきます。
「SQLチューニングはどのように取り組んでいくべきか？」(2/3) についてお送りしていきます。

それでは早速！スタートしていきましょう。

1.3　インデックスがある場合

選択 　　製品bから、 　　　　セールc 　　where b.goods_no = c.goods_no —> SALEテーブルは結合条件のみ。 　　and b.goods_name = ‘GOODS_Z’ と b.regdate between to_date(:b1, ‘YYYYMMDD’) between and to_date(:b2, ‘YYYYMMDD’) 　———————————————————————————————— 　| ID｜操作｜名前｜スタート｜A-Rows｜A-Time｜Buffers｜バッフル 　———————————————————————————————— 　|* 1 | FILTER | | 1 | 1 |00:01:32.28 | 2689K| 　| 2 | NESTED LOOPS | | 1 | 1 | 00:01:32.28 | 2689K|| NESTED LOOPS 　|* 3 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 1 | 2606 | 00:00:00.11 | 371 | 　|* 4 | INDEX RANGE SCAN | IDX02_PRODUCT | 1 | 3846 |00:00:00:00.04 | 12｜インデックスレンジスキャン 　|* 5 | TABLE ACCESS FULL | SALE | 2606 | 1 |00:01:32.14 | 2689K| 　————————————————————————————————

　

　1.3.1 Step1.インデックスの有無確認

前回説明した“インデックスがない場合” – “Step1.インデックスの有無を確認する”過程と同じように、Table Full Scan対象である
SALEテーブルのジョインカラムであるGOODS_NOにインデックスが存在するかどうかを先に確認するようにします。

INDEX_NAME COLUMN_NAME ————— ——————- IDX04_SALE GOODS_NO

Dictionary Viewを照会した結果、GOODS_NOカラムには単一カラムで構成されたIDX04_SALEインデックスが存在し、
SQL(2)はインデックスを通じたデータアクセスが可能であることが分かります。

“ところで、なぜSQL(2)はインデックスを使わなかったのか?”

Index Scanが可能なインデックスの有無を確認したら、上記のような疑問を持つことがあります。
この疑問に対する答えを見つけるため、”インデックスがない場合”の内容のようにWhere節カラムの効率性分析過程を進めます。
オプティマイザが生成されているインデックスを使用してIndex Scanを実行することが、むしろ性能上非効率的だと判断して
上記のような実行計画を策定したという仮定が成立することもあるからです。

　1.3.2 Step2.インデックス使用時の効率性を判断する。

SQL(1)の”インデックスがない場合” – “Step2.Where節カラムの効率を分析する”のような方法でインデックスを構成している
Where節カラムの効率を先に分析します。

効率性分析を通じて、インデックスの使用が非効率的なのであれば、”Step3.インデックスが使用されていない原因を把握する”
の部分は、確認する必要はなく”インデックスがない場合”部分のStep3-2の内容のうち、 “インデックスを作成せずに性能改善策を
模索する必要がある” の内容を参考にすればよいことになります。
また、別の改善方法として、既存のインデックスを変更することを検討することができます。

しかし、SQL(2)におけるインデックスの効率を分析した結果、必ずインデックスを使わなければならないほど効率的であると
分析されたのであれば、” Step3.インデックスが使われなかった原因を把握する “ の部分に移動し、なぜインデックスを
使わなかったのか？について把握しておく必要があります。

ここでは、インデックスの使用が効率的だと仮定した上でこのまま話を進めていきます。

　1.3.3 Step3.インデックスが使用されていない原因を把握する

“使用可能なインデックスが存在し、インデックスを使うのが効率的なのに、なぜTable Full Scanで実行されたのか？”

インデックスが存在し、Index Scanが性能上有利であるにも関わらず、Table Full Scanが実行されてしまう場合には、
大きく以下の3つのケースが考えられます。

・　統計情報の不正確なデータによる非効率的な実行計画の策定 　・　インデックスカラムのデータ型変換 　・　誤ったAccess Path(結合順序など)による非効率的な実行計画策定

統計情報の確認

上記の疑問を解決するために、まず確認すべきは「統計情報の不確実性」になります。

前述している通り、クエリオプティマイザーは、SQLの実行計画を策定する過程で統計情報を活用します。
しかし、統計情報データが実際のテーブルが持つデータ情報と大きく異っている場合、インデックスの使用が効率的であるにも
関わらず、誤って計算されたCardinalityによってTable Full Scanが効率的な実行であると判断される場合があります。

不正確な統計情報が生成されているほとんどのケースにおいては、データの変化を考慮しない統計情報の収集時期
（※　初期統計情報の収集後に情報が更新されなかった場合　）や、テーブルにデータが入力される前に統計情報を生成し、
データ入力後に統計情報の更新が行われなかった場合などに発生します。

クエリオプティマイザーは、正確な情報が生成されていない状態の統計情報を参照することになるので、それだけ効率的ではない
実行計画を策定してしまう可能性が高くなってしまうのは当然の結果であると言えます。
従って、上記のように、インデックスの使用に必要なすべての条件を満たしているにも関わらず、インデックスを使用できない
場合には、まず、統計情報が正しく収集されているかどうか？を確認する必要があります。

インデックスカラムのデータ型変換

統計情報を確認した結果、実際のデータと大きな差がなければ、原因はSQL自体にあると判断できます。

クエリオプティマイザーが､間違った結合順序や結合方法で実行計画を策定する場合には、FROM節に多くのテーブルがあったり、多数のサブクエリが存在するなど、コスト計算が複雑な場合です。
しかし、SQL(2)は2つのテーブルを結合する比較的単純なクエリであり、統計情報が正確なデータで収集されているため、
クエリオプティマイザが非効率的な実行計画を策定する確率はかなり減少します。
従って、実行計画の非効率の原因は、二つ目に述べたインデックスカラムの型変換によるものである可能性が比較的高くなります。では、この部分に焦点を当てて見てみましょう。

カラムのデータ型変換はいくつか確認することができますが、まず、テーブルカラムのDescriptionを確認する必要があります。
カラムの情報はDictionary ViewのAll_Tab_Columnsで確認することができます。

以下はPRODUCTとSALEテーブルに対するコラムDescriptionの内容です。

製品一覧表 COLUMN_NAME DATA_TYPE GOODS_NO NUMBER GOODS_NAME VARCHAR2 登録日

セール表 COLUMN_NAME DATA_TYPE ORD_NO NUMBER CUST_NO VARCHAR2 GOODS_NO VARCHAR2 ORDDATE DATE

二つのテーブルのGOODS_NOカラムのデータ型を見ると、異なるデータ型で生成されていることが確認できます。
これにより、インデックスを使用できないカラムのデータ型変換が内部的に発生したことが予想できます。

カラムのデータ型変換を確認できる二番目の方法は、SQLの実行履歴の中でPredicate Information情報を活用することです。
この章では詳しい説明は省略しますが、よく使われるチューニングツールであるDBMS_XPLANのPredicate Informationから
ヒントを得ることができます。この情報には、SQLが実行される段階でWhere節に使われたカラムがどのような形で変形されたか？
についての情報が含まれています。

述語情報（操作IDで識別）： 　————————————————————- 　…B.GOODS_NO = TO_NUMBER(C.GOODS_NO)

SQL(2)実行情報のうち、Predicate Informationを確認してみると、GOOD_NOカラムに型変換が発生したことを
明確に確認することができます。

このような事実については、オプティマイザーがデータ型変換のために SALE.GOODS_NOカラムにTO_NUMBER()関数 を
適用したことが、Predicate Information 情報を通じて証明されます。

さらに上記の情報は、Dictionary ViewのV$SQL_PLANのカラムのうち ACCESS_PREDICATES、FILTER_PREDICATES
カラムからも確認することができます。

Description や Predicate Informationで確認すると、PRODUCT.GOODS_NOとSALE.GOODS_NOカラムが異なるデータ型で
作られていることも確認することができます。

従って、二つのカラム間の結合時、二つの結合カラムに対するデータ型を一致させる作業のために、オプティマイザーは
データ型変換優先順位によって VARCHAR2であるSALE.GOODS_NO カラムがNUMBER型に変更する暗黙のデータ型変換を
実行します。

上記のようにカラムのデータ型型変換のために使われた関数によるカラムの加工が発生すると、インデックスを使用できなく
なりますが、SQL(2)の場合、ジョインがPRODUCT.GOODS_NO = TO_NUMBER(SALE.GOODS_NO) で行ってSALEテーブルにGOODS_NOカラムでインデックスが生成されています。
この問題によりインデックスを使用できなくなったのです。

このような現象が発生した根本的な理由は、間違って設計されたテーブル構造が原因ですが、これを修正するためにテーブルを
データ型に合わせて再作成するのは現実的に無理があります。

このような場合、どのような改善点があるでしょうか？まず、Function Based Index (以下、FBI)の生成を考えることができます。FBIを利用してGOODS_NOカラムではなく、関数で適用されたTO_CHAR(C.GOODS_NO)の形でインデックスを生成すれば、SQL(2)のように暗黙的なデータ型変換によるインデックスが使用されない問題は改善することができます。
しかし、FBIを生成すると、不必要にインデックスをもう一つ生成しなければならない問題が発生します。

もし、FBIの生成が難しい場合、SQLを修正して非効率を除去できる方法を模索してみる必要があります。
SQL(2)の場合、結合カラムのデータ型が異なり、型変換優先順位によってインデックス使用を希望するカラムに加工が発生したことがその原因だとしました。
そうすると、このような暗黙的な型変換が起こらず、インデックスを使用するためにSALE.GOODS_NOのジョイン対象となるPRODUCT.GOODS_NOカラムにTO_CHAR()関数を明示的に使用すれば、SALE.GOODS_NOカラムのデータ型である
VARCHAR2形式でデータを比較することになり、SALE.GOODS_NOカラムには何の処理も発生しないため、インデックスの使用が可能になります。

さらに、インデックスカラムのデータ型変換の問題は、ジョインカラム間のデータ型が不一致で発生する場合以外にも、Applicationで当該クエリを呼び出して実行する際、間違ったデータ型で照会する場合にも多く発生します。
この場合、SQLを実行したバインド変数値のデータ型を確認する必要があります。

1.4 チューニング手順と情報

“SQLのパフォーマンスチューニングを行う際、確認すべきデータ、考慮すべき事項はSQLによって異なります。“

前述したように、Table Full Scanという共通の非効率を持つ異なるSQLに対するチューニングの進め方は同じではありませんでした。
問題の原因を探す大きな枠組みは似ていますが、問題のタイプによってSQLが持っている情報を把握する対象が多様であることは
ご理解いただけたのではないか？思います。

つまり、どんなに単純なSQLであったとしても、非効率を発生させる原因を把握し、その原因に合った改善方法を導き出すためには、
SQLが持つ多くの情報を活用する必要があります。

SQLが持っている情報には、代表的なものとして下記のようなものがあります。
（さらに詳しい内容は、第2章と第3章で詳しく説明します。)

・　SQLの意味（業務的なアプローチ） 　・　SQLに使われたテーブル及びカラムの統計情報 (Dictionary View: all_tables, all_tab_columns) 　・　Where節の条件カラムのインデックス情報 (Dictionary View: all_indexes, all_ind_columns など) 　・　Dictionary View中のSQL実行履歴 (V$SQLAREA, V$SQL_PLAN, DBA_HIST_SQLSTAT など) 　・　SQL実行履歴（DBMS_XPLAN、TRACE）の活用など。

このように様々な情報を総合的に分析して得られる結果がSQLチューニングなのです。
今回のテーマを参考にして、SQLチューニングを始めようと考えている読者の皆様に伝えたいメッセージとしては、
SQLチューニングとは、SQLが持つ情報を収集し、総合的な思考で分析をすることで、自然な結果だが得られるということです。
この後に続いていく第2章と第3章では、SQLチューニングに必要なシステムでチューニング対象を選別する際に活用するデータと
チューニング対象をチューニングする過程において、どのような情報を活用して改善結果を導き出すことができるか？について
詳しく説明していきたいと思います。今回のブログはこれにて終了とします。

SQLチューニングブログ　2nd season（第2回）　終

今回のテーマはいかがでしょうか？

次回は「SQLチューニングはどのように取り組んでいくべきか？」の続きです

---

SQLチューニング　2nd Season（第3回）
「SQLチューニングはどのように取り組んでいくべきか？」のまとめ

---

次回もどうぞお楽しみに
それでは See you next time！

★　日本エクセムのおすすめ　★