Oracle SQLチューニング（第22回）「FUNCTION実行とSQL性能問題の理解」(3/3)
今回は、「FUNCTION実行とSQL性能問題の理解」に関する情報の第三回目「サブクエリを活用したSQL性能改善」に関して情報をご提供いたします。それでは、早速始めましょう。
６.３ FUNCTION実行とSQL性能問題
６.３.１ FUNCTIONは最終抽出結果にだけ実行しよう
Select節で使用されるFunctionの実行に関連するパフォーマンスの問題の多くは、誤った実行位置によって必要以上に実行される場合です。 Select節で使用されるFunctionの用途は、ほとんどFrom節から抽出されたデータを持ち、追加の情報を取得したいときに使用されます。このとき、Functionで抽出したデータは追加条件として使用されず、純粋にデータのみを抽出する役割を担うのが一般的です。
上記のようなFunctionの実行を含むSQL中にデータ処理過程中に処理するデータは多いが、最終抽出データ件数は多くない場合があります。このような場合、Functionが多くのデータを処理する部分で行われると、最終抽出データ件数に関係なく、不必要にFunctionの実行回数が増えることがあります。不要な機能の過剰実行は、SQL自体のパフォーマンスの問題だけでなく、DBサーバー全体のパフォーマンスにも影響を与える可能性があります。
それでは、Functionの間違った実行場所で発生したパフォーマンスの問題について、以下のケースを使用してSQLの問題と改善をまとめてみましょう。
[性能問題SQL]
[パフォーマンス問題SQL]は、SAF_TEST_APPLYテーブルからSTATE = ‘A’のデータのうち、INSERTされたデータ（a.insert_date DESC）3件（ROWNUM <= 3）を抽出するSQLです。トレース結果を見ると、抽出された全データをINSERT_DATEカラムに降順順に並べた後、そのうち上位3件のみ抽出しました。
ところで「性能問題SQL」のトレース結果を見ると不思議なことがわかります。トレース結果のTOTALラインでQUERY部分を確認してみると1,901ブロックと少ないにもかかわらず、ELAPSED TIMEは30.67（秒）かかったという点です。また、ROW SOURCE OPERATION部分のうち3件に減少する部分を見ると、CR=605408でI/Oスループットが急増したことが分かります。
[性能問題SQL]のトレース内容がやや不思議だと思うのですが、この部分は次のように解釈すればよいと思います。 TOTAL ラインで QUERY が 1,901 の理由は SQL の From 節以下をすべて処理するために読み取った I/O スループットであり、 ROW SOURCE OPERATION 部分のうち「SORT ORDER BY STOPKEY (CR=605408 ~)」は From 節で 1,901 ブロックを読み出した60,344件をFetch段階で、Select節で使用されたFunction 3つの実行結果が合計され、605,408ブロックに増加しました。つまり、Functionを除いたデータを抽出することは1,901ブロックだけ処理すればよいので、上記SQLの性能問題が発生した部分はFetch段階で発生したことが分かります。
以下の3つのFunction実行回数の合計は、60,344回でFrom節から抽出したデータ件数(60,344)だけ行われたことを確認することができます。
[GET_COM_BRANCH_NAME()実行の部分]
SQL作成者はROWNUM <= 3の条件があるため、Functionの実行も当然3回ずつ行うと予想したかもしれません。 しかし、作成意図通りSQLが実行されず、性能問題を発生させているのです。
この場合、上記のパフォーマンスの問題があるSQLを作成者が意図したとおりに実行されるように再作成する必要があります。
作成者が意図したとおりに実行するには、SQLの最も外側の部分で最終抽出件数を3件に制限するROWNUM<=3条件を処理した後、Functionが行われるようにSQLを変更すればよいのです。
以下に書き換えられたSQLのFunctionの実行位置とトレース結果を確認してみましょう。
Note.前のトレース結果はFUNCTION実行位置変更、Index構成情報変更とOrder By節を再調整して部分範囲処理で実行されるように改善されたSQLを実行した結果だ。
機能実行位置がROWNUM <= 3が実行される部分に移動されたことを確認することができます。 すなわち、ROWNUM<=3条件を処理して3件を抽出した後、Fetch段階でFunctionが行われるように誘導しました。
トレース内容のうち、Functionの実行履歴を見ると、3つのFunctionが各3回ずつ行われたことが分かります。 つまり、不要な機能の実行を排除して非効率性を改善しました。
[改善後FUNCTION実行内訳]
前述のように、SQLに属するFunctionは、最終的な抽出数が少ない場所で実行されるようにSQLを作成して、不要なリソースを無駄にしないようにする必要があります。
６.３.２ FUNCTIONがスカラーサブクエリで実行するように変更しよう
SQLのうち、最終抽出結果が少ない場合には、Functionの実行位置を変更して、過剰に行われる性能問題を解決することができます。しかし、SQLの最終抽出結果が多い場合には、Functionの実行位置を変えるだけでパフォーマンスを改善するのは難しくなります。しかし、Main SQLから抽出したデータのうち、Functionを行う入力値の種類が少ない場合、スカラーサブクエリでFunctionを行うように変更すれば、Multi Bufferを利用して性能を向上させることができます。
Oracle 9iからスカラーサブクエリが繰り返し実行される場合DBMS CALLによる負荷を防ぐために入力値と実行結果セットをあらかじめ保存し、同じ入力値で再実行される場合、スカラーサブクエリを直接行わずにあらかじめ保存した結果セットで結果値をすぐに返す機能を提供しています。
これから、Not Deterministic Functionの多くの実行回数によるパフォーマンス問題について調べてみましょう。
上記のSQLは、5,353ブロックを読み、処理が完了したように見えます。 しかし、抽出データは4,708件で、合計2つのFunctionがそれぞれ入力値だけ異なるようにして2回ずつ使用されました。 Not Deterministic FunctionがSelect節に位置しているので、Functionの実行回数を予測してみると、それぞれ抽出データ件数だけ実行されるので、4,708（抽出件数）×4（Functionの使用数）＝18,832回行われたと予想されます。 実際に予想どおりに行われたかどうかを見てみましょう。
[JISANAME()実行内訳]
[GROUPNAME()実行内訳]
予想通り、各機能は9,416回行われ、合計18,832回行われました。
ところで、Functionで使用される入力値はコードを意味する値であり、総抽出件である4,708件の多くが重複した入力値を持つと予測することができます。 すなわち、コード値を入力値としてコード名を抽出する場合は、同じ入力値に対して常に結果値が同じであってもよいのです。 このような場合、Functionをスカラーサブクエリで実行するようにSQLを変更すると、Multi Bufferの使用が可能になり、関数の実行回数を減らすことができます。
以下は、Functionの実行をスカラーサブクエリで実行するように変更したSQLです。
それでは、Functionの実行回数が減ったことを確認してみましょう。
[JISANAME()実行内訳]
なりました。 特にJISANAME()の実行回数が2回に大幅に減少した理由は、入力値で使用されるカラムであるJISACODE1、JISACODE2がすべて同じ値を持っており、スカラーサブクエリで実行するように変更した場合、最初の1回ずつ実行し、残りは Cacheから結果の値を取得できたからです。
このように入力値に対応する値の種類が多様ではなく、同じ入力値に対する結果値が常に同じである場合、Functionはスカラーサブクエリで実行するように変更して、Function実行による負荷を減少させなければならないでしょう。
６.３.３ FUNCTIONを呼び出す値のパターンを分析しよう
Functionをスカラーサブクエリで実行するように変更しても、Unique値がFunctionの入力値である場合、Cache効果を得ることができず、性能上有利な点がありません。 むしろ、ユニークな値が入力値として使用される機能の場合、キャッシュ効果ではなくマルチバッファを管理するコストのみが発生します。 したがって、関数の呼び出し値（入力値）のパターンを分析せずに関数をスカラーサブクエリで実行するようにSQLを作成することは望ましくありません。
以下のCASE1、CASE2でもっと詳しく調べてみましょう。
- CASE1: select fn_c1_codenm(c1), c1 from function_table
- CASE2: select fn_c1_codenm(c4), c4 from function_table
CASE1の場合、C1カラムを入力値として持ち、CASE2はC4カラムを入力値として持ちます。 C1 とC4 に同じ値が多ければ、(カラムのNUM_DISTINCT が少なければ)スカラーサブクエリで実行すれば性能は改善されるだろうが、カラムデータがUnique 値を持ったら、スカラーサブクエリで実行するようにSQL を変更しても性能は改善されないでしょう。
言い換えれば、カラムデータの分布を知って、その機能をスカラーサブクエリで実行するかどうかを決定できます。 データ分布は、最近収集された統計情報のみ存在すれば、列のNUM_DISTINCT値を持って十分に予測可能です。
したがって、テーブルの統計情報を調べてから使用する必要があります。
[テーブル統計情報]
テーブル統計情報のNUM_ROWSとNUM_DISTINCTを見ると、列のデータ分布がわかります。 NUM_ROWSはテーブルの総数を意味し、NUM_DISTINCTは対応する列の値の種類を意味します。 列Ｃ１の場合、テーブル総件数に対応する１０万件のうち１０万個の値を有することを意味します。 つまり、テーブルの総数と一致するため、同じ値が存在しないユニーク値を持つ列です。 一方、列Ｃ４は、合計１０万件のうちの値の種類は３つだけで、同じ値が非常に多く存在することを予測することができます。
実際のFUNCTION_TABLEにデータを生成するとき、C1はUniqueに、C4はMOD（LEVEL、3）で値の種類が3になるように生成しました。
もしそうなら、CASE1、CASE2のFunctionをスカラーサブクエリに変更して、Functionの実行回数に変化があるかどうかを調べてみましょう。
CASE [1]：CASE 1の入力値として使用されるC1は、NUM_DISTINCTが10のみでテーブル全体の件数と同じであるため、C1はUnique値であることを統計情報分析を通じて知ることができました。 したがって、Unique値がFunctionの入力値として使用されるため、スカラーサブクエリで実行してもCache効果を見ることができず、合計10万回実行されると予想できます。 実際にそのようなことを確認してみましょう。
[FUNCTION実行内訳]
予想通り10万回Functionが実行されました。
CASE [2]：入力値として使用されるC4はNUM_DISTINCTが3で、値の種類が3つで構成されています。 つまり、Functionを実行するときにほとんど同じ入力値で実行されると予想できます。 実際に3回だけ行われることを確認してみましょう。
[FUNCTION実行内訳]
予想通り3回だけ行われました。 このように CASE[2] のように Main SQL の結果のうち Function の入力値として使用されるカラムの NUM_DISTINCT が少ないとき、Function の実行をスカラーサブクエリに変更すると大きな効果が得られ、逆の場合は利点が得られない結果が見られました。
今後は、Functionをスカラーサブクエリで行うべきか否かを決定する際に、入力値に対するデータ分布を分析してから決めることにしましょう。
６.３.４ SELECT節に使われたFUNCTIONを結合に変更しよう
CASE [1]で使用されている関数を削除して結合に変更しましょう。
CASE[1]のFunction処理を結合に変更した後、Functionの実行部分が6秒から0.32秒に実行時間が短縮され、I/Oスループットも300,000ブロックから7,350ブロックに大幅に減少しました。トレースの結果を見ると、Functionの実行部分を結合に変更したとき、結合接続はOuter Joinで処理され、FUNCTION_TABLEとC1_CODE_NMテーブルの結合方法はHash Joinを選択しました。ここで、Outer JoinはSelect句のFunctionを結合に変更するときに適用されるべき制約であり、Hash JoinはFrom句から抽出されたデータの数だけ繰り返し実行（インデックススキャン）したときに発生する非効率性を排除するための結合方式です。
上記のように多くのデータ処理を行うバッチプログラムのSQLに使用されるFunctionの性能問題を解決するための改善方法として、Functionをスカラーサブクエリに変更することと結合に変更する2つの方法が存在します。まず、Functionをスカラーサブクエリに変更してパフォーマンス改善する場合は、ジョインに変更することは考慮する必要はありませんが、Functionを呼び出す値の種類が多く、スカラーサブクエリに変更することがパフォーマンス改善に役立たない場合は、可能であればジョインに変更することも考慮する必要があります。
６.３.５ WHERE節のFUNCTIONをSELECT節に移そう
（注）T_MEMBERテーブルがWHERE節条件がなく、HASH JOINで実行する場合T_MEMBERテーブル全体件数分FUNCTION実行されます。
Where句で呼び出されるGET_BIRTHDAY_BY_JUMINNO関数は、住民番号を用いて誕生日を抽出してくるFunctionです。 SQLを見ると、抽出データの照会条件は、顧客の加入日が「20100219～20100301」であり、誕生日が「19000101～19911231」の顧客のうち、地域情報条件まで満足する顧客情報を抽出するSQLです。
誕生日に関する条件を見ると、住民番号を利用して誕生日情報を抽出するGET_BIRTHDAY_BY_JUMINNO関数を使用して誕生日情報を抽出した後、「19000101～19911231」期間に該当するかどうかをチェックしています。
Functionの実行時点は、Functionを使用した条件を除くWhere句のすべての条件が最初に処理され、フィルタリングされたデータに対してのみFunctionを呼び出すことがSQL作成者の作成意図であり、またそのように実行されることが最も望ましいと考えられます。しかし、残念ながら、実際の実行時にFunctionの入力値である住民番号（JUMIN、JUMIN2ENC）を提供するT_MEMBERテーブルの合計数だけFunctionを呼び出すことになります。なぜなら、T_MEMBERテーブルは何の条件もなく実行され、結合方式がHash Joinであるからです。
上記のFunctionは、元の作成意図とは異なり、過度に実行され、パフォーマンスの問題が発生しています。そのため、最初の作成意図のように関数が実行されるようにSQLを変更してパフォーマンスの問題を解決する必要があります。
最初の作成意図に合うようにFunctionを実行するには、Where句で使用したFunctionを一度Select節に移動して誕生日情報を抽出し、この結果をインラインビューにする必要があります。そしてインラインビューの外から抽出した誕生日情報に対する条件を処理すれば、最初のSQLの作成意図どおり Function の実行回数を減らすことができるのです。なぜなら、Where句で行われたFunctionをSelect句に移すことになる場合、データをFetchするときに行われるという特性で、誕生日条件を除いた残りの条件を先に処理し、その処理されたデータに対してのみFunctionを実行するからなのです。
(注２）スカラーサブクエリから抽出した後のFUNCTIONデータチェック
しかし、このようにSQLを書き換えた後、予期しない問題に直面する可能性があります。 これは、作成者の意図を知らないOptimizerがパフォーマンスを向上させるために、インラインビューの外側で使用されたFilter条件をビューに浸透させるFPD（Filter Push Down）操作によるものです。
上記のように変更したとき、11gR2では問題はなかったが、10gR2ではむしろ性能が悪くなる事例がありました。
それでは、上記の内容についてテストでもっと詳しく調べてみましょう。
SQLの説明：FUNCTION_TABLEとC1_CODE_NMテーブルの結合後にデータが大幅に減るSQLです。 そして FN_C2_CODENM(T2.C4) BETWEEN ‘A’ AND ‘B’ 条件はデータが減る主な条件ではありません。
以下のSQLトレースの結果とFunctionの実行履歴を確認してみましょう。
[FUNCTION実行内訳]
SELECT COUNT(*) FROM C1_CODE_NM T2 WHERE T2.C3 IN (‘A’) の実行結果は 5,000 件です。 したがって、5,000件に対してのみFunctionが行われると予想できます。 しかし、Functionの実行履歴を見ると10,000回行われました。
その理由は、Optimizerが内部的にBETWEEN条件を次のように変更して処理するためです。
"T2"."C3"='A' AND "FN_C2_CODENM"("T2"."C4")>='A' AND "FN_C2_CODENM"("T2"."C4")<='B'
それでは、前のSQLに対する改善案を導き出してみましょう。
SQLの最終抽出データは1,667件で、Functionの実行回数は10,000回です。 ところで、1,667件のデータのうち、Functionから抽出したデータで、Where句でフィルタリングされるデータはありません。 したがって、Functionの実行は、最終抽出データに対してのみ実行するようにSQLを書き換えなければ実行回数を減らすことができます。 したがって、Where句のFunctionをSelect句から値を抽出し、インラインビューにし、Functionを実行し、抽出した値を持ち、インラインビューの外で条件を実行するようにSQLを再作成する必要があります。
では、まずFunctionをSelect句に移動し、Functionの実行回数を確認してみましょう。 Functionを用いた条件はコメントアウトしてSQLを実行しました。
[FUNCTION実行内訳]
上記のようにSQLを変更すると、Functionは結合に成功したデータに対してのみ行われるため、実行回数は10,000回から1,891回に確実に減少しました。
次に、Functionを実行して抽出されたデータをFT2C4 BETWEEN ‘A’ AND ‘B’条件でチェックするように条件を追加してSQLを実行しました。 以下のトレース結果を確認してみましょう。
[FUNCTION実行内訳]
最終抽出件数は1,667件で、データは正常に抽出されました。 しかし、Functionの実行回数が大幅に増加しました。 さらに、原文SQLでは、10,000回実行されたFunctionの実行回数がさらに増え、実行時間も増加しました。 このような現象がなぜ発生したのでしょうか?
その理由を調べるために、10053トレースを使用して、Optimizerが内部で何をしたかを分析してみましょう。
10053トレースでFPDが発生した部分を確認してみましょう。
トレースの結果を見ると、OptimizerがFPD（Filter Push Down）に成功し、インラインビューの外側の条件をビュー内に反映させたことがわかります。
FunctionはSelect句で実行され、Functionから抽出された値を使用してビューの外側で条件を処理するためにSQLを書き換えました。 ところで、QUERY TRANSFORMATIONによってFPDが発生し、Where句とSelect句のFunctionがそれぞれ実行され、実行回数が異常に増えたのです。
実行計画でPredicate InformationとColumn Projection Informationを確認してみましょう。
Predicate Informationを見ると、PLANにID 4に該当するFilter条件がFPDによりSQLが変更され、これによりFunctionが2回行われました。 そして、Column Projection Informationを見ると、ビューに対応するID 1にFT2C4がVARCHAR2（4000）タイプであるとみなされ、FunctionがSelect句でもう一度行われたことが分かります。
上記の情報に基づいて、Oracleが変更したSQLの様子を見てみると、次のようになります。
上記のような性能問題を解決するためには、意図しなかったFPDが発生しないようにすればよいのです。 Functionをスカラーサブクエリで実行するようにSQLを作成すると、Functionを実行する動作方式が異なるため、FPDが発生せずSQLのパフォーマンスを向上させることができます。
Select句のFunctionの実行をスカラーサブクエリに変更し、実行結果を確認してみましょう。
[FUNCTION実行内訳]
Predicate Informationを確認すると、Functionの実行後に比較する条件が消えてFPDが発生しませんでした。
Oracleのバージョンによっては、FunctionをSelect句に移動してインラインビューで作成し、ビューの外側からフィルタリングする場合は、FPDによってパフォーマンスが悪くなる可能性があるため、SQLを書き換えた後にパフォーマンスの問題が発生しないことを確認する必要があります。
これまで、Functionの動作方式とSQLのパフォーマンス問題を改善した事例について一緒に調べてきました。 Functionの使用はプログラムの開発やメンテナンスなど様々な面で効率的ですが、逆に非効率的に使用されたFunctionはDBサーバの性能に大きな悪影響を及ぼす可能性があります。
したがって、この章では、Functionの正確な動作方法を理解し、Functionを使用するときに常に効率的に実行されるようにSQLを作成してください。
これで、三回に渡ってお送りしました「FUNCTION実行とSQL性能問題の理解」に関しましての情報提供は終了です。
いかがでしたでしょうか？次回からは、「DECODE & CASE WHENの理解および条件ステートメントの処理」に関します情報を、二回に渡ってお送りする予定にしております。ご期待ください。では See you^^
