Oracle SQLチューニング Season2（第1回）「SQLチューニングはどのように取り組んでいくべきか？」（1/3） (1)

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

　今回からSQLチューニングブログは、新たに2nd Seasonに突入します。
　1st Seasonに続いて、DBA業務に役立つでデータベースの技術情報を発信していきたいと思います。

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

第１章　「SQLチューニングはどのように取り組んでいくべきか？」

　SQLの性能改善についての相談を受ける時、私たちはよくこの質問をされることがあります。

●質問　　

SQLチューニングとは何をすることですか？

●答え　　

SQL + チューニング をすること

　チューニング対象のSQLが持つ情報をきちんと理解し、精緻に分析して得られた結果が「SQLチューニング」だからです。
　SQLが持つ情報を正しく抽出し、それを正しく分析することが、私たちは非常に重要であると考えています。
　尚、この答えに関しての根拠は『 SQLチューニングの始まり-SQLの理解と効率的な書き方 』 の序文で説明しています。

さらにこんな質問をされることもあります

●質問

チューニング対象はどのように選定すれば良いですか？
SQLチューニングを行う場合、分析すべき情報としてはどのようなものがありますか ？

　SQLチューニングという言葉は何となく知ってはいるものの、どのように進めるべきなのか？についての明確な答えを持っていない人は、おそらくこのような質問をするのだろうな？と感じています。
　これらの質問に共通して言えることは、SQLチューニング時に必ず検討する必要がある事項であることです。
　SQLチューニングとは結局、その対象を適切に選別をおこない、必要な情報収集と分析を通じて作られた成果物だからです。
　この話題に関する副題として、SQLチューニング方法論と言うものがあります。
　用語からすると難しく見えますが、実際に理解することはそれほど難しくありません。
　このブログで紹介するチューニング方法論の内容は、Web画面のDynamic SQLに対するチューニング方法論、Batchプログラムに対するチューニング方法論、そして、SQLチューニング時には欠かせないHintの理解と適用方法論のような内容になります。
　これらの方法論については、第4章、第5章、第6章の方で説明していきます。 
　では、全体的な内容を概略的に把握していくために、ここでまずは、簡単なSQLチューニングをしてみましょう。

　チューニング対象は既に抽出したと仮定して始めていきます。

1.1 　SQLチューニングをやってみよう

　一般的に、SQLの非効率原因の中で最も頻繁に直面するパフォーマンス問題は、Table Full Scanによる場合です。
　ここでは、Table Full Scanを実行する2つのタイプのSQLをチューニングするにあたり、進行手順に沿って必要となる情報と分析方法について説明します。

　尚、SQLチューニング時に必要な情報を把握する部分については、第3章でもっと詳しく説明していきます。
　本章では、SQLチューニングの進行手順とどのような情報が必要かを理解することを目標に全体的な内容を簡単に説明します。
　この章は、簡単なチューニング例を通じて実際のチューニングに必要な情報を紹介するPreviewの過程です。
　例題の進行に使用される用語の説明は最小限にするようにして、詳細な解説は第3章の中で確認していきましょう。
　チューニング手順の内容を説明するために使用する例は下記の SQL(1),、SQL(2) です。二つのSQLはTable Full Scanで実行され、抽出件数に比べて多くのI/Oが発生する非効率性を持っています。

各SQLの説明

SQL(1) : XM_EVENT テーブルを特定の SID で照会して CREATE_DT カラムに対する Counting クエリ
SQL(2) : PRODUCT（製品）テーブルにREGDATE（登録日）に登録された製品のうちGOODS_Z製品がSALE（販売）テーブルに該当する情報を抽出するクエリ

SQL (1)

　select count(create_dt)
　 from xm_even
　where sid = :b1
　——————————————————————————————————————
　| ID｜操作｜名前｜スタート｜A-Rows｜A-Time｜バッファ｜読み込み｜読み取り
　——————————————————————————————————————
　| 0 | SELECT STATEMENT | | 1 | 1 | 00:00:07.06 | 7924 | 7915 | 00:00:07.06
　| 1 | SORT AGGREGATE | | 1 | 1 | 00:00:07.06 | 7924 | 7915 | 00:00:07.06
　|* 2 | TABLE ACCESS FULL | XM_EVENT | 1 | 9 |00:00:07.06 | 7924 | 7915 |
　—————————————————————————————–

述語情報（操作IDで識別）：

　—————————————————
　 2 – filter(“SID”=:B1)

SQL (2)

選択 製品bから、 セールc

where b.goods_no = c.goods_no
　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|
　————————————————————————————————

Table Full Scanを実行してSQLを改善してみる

　上記の二つのSQLはいずれもTable Full Scanによる非効率が発生しています。
　一般的に、Table Full Scanの実行で非効率が発生した場合、インデックスの有無を確認した後、　SQLに記述されたテーブルに対する情報(テーブル/カラム/インデックス)を照会及び分析を通じて問題の原因を把握することになります。
　したがって、この部分に焦点を当ててSQLチューニングを行うことになります。
　前の二つのSQLは任意に作った例で、SQL(1)はインデックスが存在せず、非効率が発生し、SQL(2)はインデックスが存在するが、カラムのデータ型変換でインデックスを使用できず、Table Full Scanで実行します。
　この二つの場合に対するチューニングを手順別に情報の照会及び活用を通じて改善結果を導き出すようにします。

1.2 　インデックスがない場合

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

　SQL(1)は、Where節でSIDカラムだけ照会される単純なSQLです。
　Where節に使われたSIDカラムの効率を判断する前に、まずSIDカラムに生成されたインデックスの有無を確認する必要があります。
　特定テーブルのインデックス情報、及び構成カラムに関する情報については、下記のようなオラクルが提供するDictionary Viewを通じて確認することが可能です。

　SQL(1)で使用されたXM_EVENTテーブルのインデックスを照会した結果、当該テーブルのSIDカラムにインデックスが存在しないことが確認できました。

View: ALL_INDEXES
　Column: TABLE_NAME, INDEX_NAME, INDEX_TYPE など

　View: ALL_IND_COLUMNS
　Column: TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION など

1.2.2　Step2.Where節カラムの効率分析

　Step1.インデックスの有無確認”の過程で、Where節に使用されたSIDカラムで始まる(またはSkip Scanが可能なインデックス)インデックスが存在しないことを確認しました。
SQL(1)の場合、Where節の条件に適したインデックスが存在しないため、XM_EVENTテーブルは仕方なくTable Full Scanを実行することになります。

　インデックスが存在しないことが確認された後には、おそらく以下のような疑問が生じるのではないか？思います。
　『じゃあ、Where節の条件カラム(SID)で始まるインデックスを生成すれば、非効率は解消されるのか？』
　これに疑問に対する私たちの答えは「インデックスを生成する場合には、SQL(1)が効率的かもしれないし、むしろ性能が悪くなる可能性もあります。」

“　それはなぜですか？　”

　Table Full Scan実行の代替のためにWhere節条件カラムで使用するカラムに対してインデックスを生成することができます。
　インデックスを生成するための根拠、つまりカラムの効率性に対する判断をまだ行っていないため、インデックスの生成による効率を判断するのは時期尚早であると言えます。

　効率を確認する段階を無視したままで、無闇にインデックスを生成してしまうと、新しい性能問題を引き起こす可能性があるので注意しておく必要があると思います。

　もし、SQL(1)のDBMS_XPLANの内容をよく理解したみなさんには、インデックスの生成が答えであることが分かるのではないでしょうか？

　しかし、常にインデックス作成を改善方法として決める前に、インデックスを作成するカラムの効率性について検討する習慣を持つようにしましょう。

ではどのようにすれば、インデックス作成に対する効率を判断できるのでしょうか？

※　この質問に対する回答には、SIDカラムのデータ性質の2つの場合を想定して説明します。

　まず、Where節に使われたSIDカラムの性質が、性別のように”男性”もしくは”女性”という2種類のデータしか持っておらず、データ分布が似ていると仮定した場合です。
　もし、このようなデータ特性を持つSIDカラムにインデックスを作成したらどうなるでしょうか？
　SIDカラムにインデックスを生成し、SQL(1)が新しく生成されたインデックスでIndex Scanを実行すると、テーブルの全体データ数の半分程度のデータをIndex Scanで抽出することになります。
テーブルのSizeが大きいほど、インデックスを通じて抽出されるデータ件数は非常に多くなります。
　このような場合、SQL(1)の非効率はIndex Scanを処理する作業量より、インデックスから抽出されたデータ件数だけテーブルでのデータ処理(*Table Random Access)する部分が非効率になります。
　これは、インデックスで処理されたI/O量よりはるかに多くのI/O量がテーブルデータを抽出する時に発生するからです。
　上記の場合、インデックスをSID + CREATE_DTカラムで結合インデックス構成(照会が頻繁で、パフォーマンス問題の主な原因であれば、このようにインデックスを生成することも効率的な場合があります)にすれば、インデックスだけでデータ処理が可能なので、Table Full Scanに比べて処理I/O量を減らすことができるでしょう。

　しかし、SQLに使用されたカラムの数が多い場合、Index Scanだけで処理ができるようにすべてのカラムでインデックスを構成することはできません。
　したがって、このようなデータ性質を持つテーブルは、インデックス生成ではなく、Table Full Scanで実行するようにするのがより良い方法かもしれません。
　一方、SIDカラムの内容が住民登録番号や顧客番号のようなデータだとします。
　一般的にこのようなデータはUniqueだったり、値の種類が多様なので、SQL(1)のように”=”で照会すると、抽出件数は非常に少ないと予想できます。該当カラムにインデックスを生成して、インデックスを通じて抽出されるデータ件数が少なければ、テーブルデータ処理量(Random Access)も少ないので、インデックスを生成すると効率的です。
　結局、カラムの効率を判断してインデックスを作成するかどうかを決定しなければ、目的の改善効果を得ることができません。

　上記の例で言及した値の種類(Number of Distinc)と予想抽出件数(Cardinality)、値の分布(Histogram)などがインデックス生成を検討するための要素であり、これらの情報(統計データ=統計情報)がOracleによってよく収集されている場合、単純にOracleが提供するDictionary Viewを照会するだけで、インデックス生成の是非を判断することができます。

　しかし、統計情報を収集していないか？、または収集されたデータが不確かな場合には、ユーザーが直接このような情報を照会する必要があります。

　1.1.3　　統計情報を活用してカラムの効率を分析する場合

　先ほど、インデックス生成を決定する際に必要な主要要素を収集しておいたものを統計情報と言いました。
　定期的に、あるいは特定の時点で生成しておいた統計情報が既に存在し、その統計データが実際のテーブルのデータをよく反映している場合、OracleのMeta Dataを照会できるDictionary Viewを通じて、インデックス生成に必要なカラムの効率を判断することができます。
　以下のDictionary Viewのカラムの中で最も重要なのはNum_Rows、Num_Distinct、Num_Nullsカラムで、Num_Rows (All_Tables)カラムはテーブルの全体データ数Num_Distinct (All_Tab_Columns)は特定のカラムの値の種類、そしてNum_NullsカラムはカラムデータのうちNullのデータ数を意味します。

　View: ALL_TABLES
　Column: TABLE_NAME, NUM_ROWS, BLOCKS など

　活用 View: ALL_TAB_COLUMNS
　主要 Column: TABLE_NAME, COLUMN_NAME, DATA_TYPE, NULLABLE, NUM_DISTINCT, NUM_NULLS 　等など

　一部の特殊な状況を除けば、上記のDictionary Viewで抽出した情報のうち、以下の3つのカラムで計算してみると、インデックスを生成した後、抽出される予想件数(=照会時)を予測することができます。
　前述したように、予想抽出件数が少ない場合にインデックスを生成する方が効率的に実行されるので、インデックスを生成する時、下記の簡単な式で効率性を考えてみてください。

Cardinality =(ALL_TABLES.NUM_ROWS – ALL_TAB_COLUMNS.NUM_NULLS)/(ALL_TAB_COLUMNS.NUM_DISTINCT)

　SQL(1)のSIDカラムに対するインデックス効率を確認するために、上記の式を使ってCardinalityを計算した結果は、9 ((900608-0)/100012) であり、インデックスを生成する場合には、非常に効率的に動作することが予想できます。

ABLE_NAME COLUMN_NAME DATA_TYPE NUM_DISTINC NUM_NULLS NUM_ROWS
————– —————— ——————- ———– ———– —————-
　XM_EVENT SID VARCHAR2 100012 0 900608

1.1.4　　統計情報が収集されていない場合

　信頼できる統計情報が収集されている場合にはDictionary Viewを照会してカラムの効率を判断することができますが、統計情報が信頼できないか、収集されていない場合は、データ分布や値の種類など、判断の根拠となる情報をユーザーが直接計算して判断する必要があります。

　情報を抽出する

• Num_Rows 照会: Select count() as Num_Rows from XM_EVENT；
• Num_Distinct 照会: Select count(distinct SID) as Num_Distinct from XM_EVENT
• Num_Nulls 照会する: Select count(*) as Num_Nulls from XM_EVENT where SID is null;

　Num_Rows、Num_Distinct、Num_Nullsデータは、インデックス生成時の効率を判断するための最小限の情報データで、上記の3つのSQLで簡単に抽出することができます。
　この時、ユーザーがデータを照会するためにはDistinct、Count関数などを使用しなければならないが、このような照会SQLはデータの数が多いテーブルを対象に実行する場合、DBシステムに負荷を与える可能性があるので注意しなければなりません。

　このようなテーブル照会時、業務担当者やDBAに問い合わせたり、Table & ColumnsにCommentが設定されている場合は、情報を照会して業務的にデータを判断するのも良い方法です。

1.1.5　　SQL実行履歴を活用する：Trace、DBMS_XPLAN、Dictionary View 

　テーブルとカラムに対する適切な統計データ(All_Tables, All_Tab_Columns)が収集されていない場合、”統計情報が収集されていない場合”のように直接照会しなくても、SQLの実行履歴をよく把握すれば、カラムの効率を大体把握することができます。以下はSQL(1)のDBMS_XPALN.DISPLAY_CURSORの実行内訳です。

select count(create_dt)
　from xm_event where sid = :b1
　—————————————————————————————–
　| ID｜操作｜名前｜スタート｜A-Rows｜A-Time｜バッファ｜読み込み｜読み取り
　—————————————————————————————–
　| 0 | SELECT STATEMENT | | 1 | 1 | 00:00:07.06 | 7924 | 7915 | 00:00:07.06
　| 1 | SORT AGGREGATE | | 1 | 1 | 00:00:07.06 | 7924 | 7915 | 00:00:07.06
　|* 2 | TABLE ACCESS FULL | XM_EVENT | 1 | 9 |00:00:07.06 | 7924 | 7915 |
　—————————————————————————————–

　述語情報（操作IDで識別）：
　—————————————————
　2 – filter(“SID”=:B1)

　上記のDBMS_XPLANの内容を見ると、Where節はPredicate Informationに記述されているSID=:B1です。

　この条件が実行される部分はIDが2回で、実際の抽出件数(A-Rows)が9件です。
　つまり、SIDカラムのデータ分布が均一であるとすると、=照会時に少ない件数が抽出されると予想できます。
　また、この情報は、インデックス作成時に活用できる判断基準になることができます。
　このような情報は、SQLのDBMS_XPLAN(またはTrace)の実行内訳だけでなく、オラクルが提供するDictionary ViewのうちSQLのStatisticsデータを照会できるV$SQL、V$SQLAREAなどでも抽出することができます。
　この部分は第2章で詳しく説明します。

1.1.6　　Step3-1. カラムの効率が良い場合 → インデックスの生成

　Step2.Where節カラムの効率性分析”を通じて統計情報と照会SQLの実行によってCardinalityを計算してみた結果、インデックス生成が効率的な改善方法として導き出した場合、インデックス生成を適用するかを検討します。

　新規インデックスの生成は、追加のDiskスペースを割り当てる必要があり、トランザクション処理(DML)が頻繁に発生するテーブルに既に様々なインデックスが存在する場合、別の性能問題を引き起こす可能性があります。また、新規インデックスの追加で当該テーブルを照会する他のSQLの実行計画が変更される予期せぬ結果を引き起こす可能性があります。したがって、新規インデックスの生成時に発生可能なSide Effectに対する慎重な検討が必要です。

　例えば、インデックス生成で同じテーブルを照会する他のSQLの実行計画の変更有無を検討するためにPredicate情報を抽出して把握する作業を行ったり、新規インデックス生成の対象となるカラムが既存のインデックスに既に類似して構成されているかどうかを把握したり、新規インデックスを生成したが、オプティマイザが実行計画を立てるときに参照する統計情報が不適切に生成された場合、SQLにインデックスヒントを追加して希望する実行計画で実行できるように誘導する方法などです。

1.1.7　　Step3-2.カラムの効率が悪い場合

　もし、SQL(1)のSIDカラムの効率が良くないと判断した場合、インデックスを通じて抽出される件数が多く、テーブルのデータ処理(Random Access)時に発生するI/O量が多く、むしろTable Full 　Scanで実行する方が性能上有利な場合です。
　この場合、性能向上のためには他の方法を模索しなければ駄目でしょう。

1.1.7.1　　テーブルアクセスなしのインデックス構成を検討する必要がある

　インデックス生成時にカラムの効率が悪いことにより発生する性能問題は、Index Scanで抽出される件数が多く、テーブルでデータ処理実行時に非効率な処理が発生する割合が高くなります。
　その場合、結合インデックスで生成したテーブルデータへのアクセスなしにインデックスデータだけで結果を抽出する方法が代替案になります。
　しかしもし、SQLのWhere節条件が変更されるDynamic SQL(第４章　SQLチューニング方法論で説明)の場合、変更される条件(カラム)によって、希望するIndex Scanで実行されない可能性が高くなります。

　上記のような別途の考慮事項がないと仮定した場合に、性能問題を解決しなければならないSQLが頻繁に実行され、DBシステムの性能に大きな影響を与えており、必ず性能改善が必要な場合、テーブルデータへのアクセスなしにインデックスだけで処理されるようにSQLのすべてのカラムで構成されたインデックスの作成を検討する必要があります。

1.1.7.2　　インデックスを生成せずに性能改善策を模索する必要がある

　SQLに対してインデックス構成ができない場合は、下記のような方法を検討することができます。

• 　SQL書き換え（業務的な協議が必要）
• 　Hintを使用した実行計画補正(例：異常統計情報)
• 　集計テーブルの使用(リアルタイムデータを使用しない場合)
• 　11g 結果キャッシュ
• 　M-View活用
• 　Keep Bufferなどの活用など
• 
  

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

今回から 2nd Serson としてスタートしたSQLチューニングブログはいかがでしたか？

次回は 第1章の続き をお届けしていきます。
それでは See you next time！