2022.01.20
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]
SELECT Z.*
FROM (
SELECT a.apply_code ,
b.branch_code ,
get_com_branch_name( b.branch_code ) AS branch_name,
get_saf_lecture_k2_name( d.lecture_kind2 ) AS lecture_kind_name,
get_commem_name( a.member_code ) AS member_name
FROM saf_test_apply a,
saf_brn_test b,
com_member c,
saf_test d
WHERE a.test_code = b.test_code
AND a.branch_code = b.branch_code
AND a.member_code = c.member_code
AND a.test_code = d.test_code
AND b.state <> 'C'
AND a.state = 'A'
ORDER BY a.insert_date DESC
) Z
WHERE ROWNUM <= 3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 28.74 30.63 0 1899 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 28.76 30.67 0 1901 0 3
Rows Row Source Operation
------- ---------------------------------------------------
3 COUNT STOPKEY (cr=605408 pr=0 pw=0 time=0 us)
3 VIEW (cr=605408 pr=0 pw=0 time=0 us cost=528 size=38511004 card=6356)
3 SORT ORDER BY STOPKEY (cr=605408 pr=0 pw=0 time=0 us …)
60344 HASH JOIN (cr=1901 pr=0 pw=0 time=311686 us …)
60345 HASH JOIN (cr=1081 pr=0 pw=0 time=167352 us …)
2000 TABLE ACCESS FULL SAF_TEST (cr=38 pr=0 pw=0 time=874 us …)
60345 HASH JOIN (cr=1043 pr=0 pw=0 time=89621 us …)
2343 TABLE ACCESS FULL SAF_BRN_TEST (cr=38 pr=0 pw=0 time=986 us …)
60716 TABLE ACCESS FULL SAF_TEST_APPLY(cr=1005 pr=0pw=0 time=30293 us …)
389419 INDEX FAST FULL SCAN SYS_C0011071(cr=820 pr=0pw=0 time=169690 us …)
[パフォーマンス問題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()実行の部分]
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 60344 0.81 1.06 0 0 0 0
Fetch 60344 0.55 0.61 0 120688 0 60344
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 120689 1.36 1.67 0 120688 0 60344
[GET_SAF_LECTURE_K2_NAME()実行の部分]
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 60344 0.80 0.94 0 0 0 0
Fetch 60344 1.62 1.75 0 241372 0 60344
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 120689 2.42 2.69 0 241372 0 60344
[GET_COMMEM_NAME()実行の部分]
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 60344 1.04 1.14 0 0 0 0
Fetch 60344 0.73 0.77 0 241387 0 60344
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 120689 1.77 1.92 0 241387 0 60344
SQL作成者はROWNUM <= 3の条件があるため、Functionの実行も当然3回ずつ行うと予想したかもしれません。 しかし、作成意図通りSQLが実行されず、性能問題を発生させているのです。
この場合、上記のパフォーマンスの問題があるSQLを作成者が意図したとおりに実行されるように再作成する必要があります。
作成者が意図したとおりに実行するには、SQLの最も外側の部分で最終抽出件数を3件に制限するROWNUM<=3条件を処理した後、Functionが行われるようにSQLを変更すればよいのです。
以下に書き換えられたSQLのFunctionの実行位置とトレース結果を確認してみましょう。
SELECT x.apply_code,
x.branch_code,
---> FUNCTION実行位置をSQLの最も外で翻訳
get_com_branch_name( x.branch_code ) AS branch_name ,
get_saf_lecture_k2_name( x.lecture_kind2 ) AS lecture_kind_name ,
get_commem_name( x.member_code ) AS member_name
FROM (
SELECT Z.*
FROM (
SELECT /*+ LEADING(A) USE_NL(A B C D) index(A INX_SAF_TEST_APPLY_05) */
a.apply_code ,
b.branch_code,
--->注釈開始(FUNCTION実行位置を変更するために注釈処理する)
--get_com_branch_name( b.branch_code ) AS branch_name ,
--get_saf_lecture_k2_name( d.lecture_kind2 ) AS lecture_kind_name ,
--get_commem_name( a.member_code ) AS member_name
--->注釈終わり
d.lecture_kind2,--実行位置を移した後
-- function実行に必要なINPUT値を抽出
a.member_code --実行位置を移した後
-- function実行に必要なINPUT値を抽出
FROM saf_test_apply a ,
saf_brn_test b ,
com_member c ,
saf_test d
WHERE a.test_code = b.test_code
AND a.branch_code = b.branch_code
AND a.member_code = c.member_code
AND a.test_code = d.test_code
AND b.state <> 'C'
AND a.state = 'A'
ORDER BY a.state, a.insert_date DESC
) Z
WHERE ROWNUM <= 3
) X
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 30 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 30 0 3
Rows Row Source Operation
------- ---------------------------------------------------
3 VIEW (cr=30 pr=0 pw=0 time=0 us cost=49 size=180 card=3)
3 COUNT STOPKEY (cr=30 pr=0 pw=0 time=0 us)
3 VIEW (cr=30 pr=0 pw=0 time=0 us cost=49 size=180 card=3)
3 NESTED LOOPS (cr=30 pr=0 pw=0 time=0 us cost=49 size=204 card=3)
3 NESTED LOOPS (cr=22 pr=0 pw=0 time=112 us cost=46 size=189 card=3)
3 NESTED LOOPS (cr=14 pr=0 pw=0 time=84 us cost=43 size=208 card=4)
3 TABLE ACCESS BY INDEX ROWID SAF_TEST_APPLY (cr=6 pr=0 pw=0 time=26 us)
3 INDEX RANGE SCAN INX_SAF_TEST_APPLY_05 (cr=3 pr=0 pw=0 time=18 us)
3 TABLE ACCESS BY INDEX ROWID SAF_BRN_TEST (cr=8 pr=0 pw=0 time=0 us)
3 INDEX RANGE SCAN SAF_BRN_TEST_IDX_BT (cr=6 pr=0 pw=0 time=0 us ...)
3 TABLE ACCESS BY INDEX ROWID SAF_TEST (cr=8 pr=0 pw=0 time=0 us cost=1)
3 INDEX UNIQUE SCAN SYS_C0011754 (cr=5 pr=0 pw=0 time=0 us cost=0 ...)
3 INDEX UNIQUE SCAN SYS_C0011071 (cr=8 pr=0 pw=0 time=0 us cost=1 size=5)
Note.前のトレース結果はFUNCTION実行位置変更、Index構成情報変更とOrder By節を再調整して部分範囲処理で実行されるように改善されたSQLを実行した結果だ。機能実行位置がROWNUM <= 3が実行される部分に移動されたことを確認することができます。 すなわち、ROWNUM<=3条件を処理して3件を抽出した後、Fetch段階でFunctionが行われるように誘導しました。
トレース内容のうち、Functionの実行履歴を見ると、3つのFunctionが各3回ずつ行われたことが分かります。 つまり、不要な機能の実行を排除して非効率性を改善しました。
[改善後FUNCTION実行内訳]
・GET_COM_BRANCH_NAME()実行の部分
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 6 0 3
・GET_SAF_LECTURE_K2_NAME()実行の部分
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 12 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 12 0 3
・GET_COMMEM_NAME()実行の部分
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 12 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 12 0 3
前述のように、SQLに属するFunctionは、最終的な抽出数が少ない場所で実行されるようにSQLを作成して、不要なリソースを無駄にしないようにする必要があります。
６.３.２ FUNCTIONがスカラーサブクエリで実行するように変更しよう
SQLのうち、最終抽出結果が少ない場合には、Functionの実行位置を変更して、過剰に行われる性能問題を解決することができます。しかし、SQLの最終抽出結果が多い場合には、Functionの実行位置を変えるだけでパフォーマンスを改善するのは難しくなります。しかし、Main SQLから抽出したデータのうち、Functionを行う入力値の種類が少ない場合、スカラーサブクエリでFunctionを行うように変更すれば、Multi Bufferを利用して性能を向上させることができます。
Oracle 9iからスカラーサブクエリが繰り返し実行される場合DBMS CALLによる負荷を防ぐために入力値と実行結果セットをあらかじめ保存し、同じ入力値で再実行される場合、スカラーサブクエリを直接行わずにあらかじめ保存した結果セットで結果値をすぐに返す機能を提供しています。
これから、Not Deterministic Functionの多くの実行回数によるパフォーマンス問題について調べてみましょう。
SELECT ---> Function実行の部分開始
jisaname( gr.jisacode1 , '00' , SYSDATE ) jisaname1 ,
jisaname( gr.jisacode1 , gr.jisacode2 , SYSDATE ) jisaname2 ,
groupname( gr.parent_groupno ) parent_groupname ,
groupname( gr.groupno ) groupname ,
---> Function実行の部分終わり
gr.groupno,
gr.parent_groupno,
gr.jisacode1,
gr.jisacode2,
mem.memberno
FROM t_member mem inner join t_group gr ON gr.groupno = mem.groupno
WHERE... 以下省略......
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 315 4.99 5.19 0 5353 0 4708
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 317 4.99 5.19 0 5353 0 4708
Rows Row Source Operation
------- ---------------------------------------------------
4708 SORT ORDER BY (cr=52433 pr=0 pw=0 time=12159 us …)
4708 HASH JOIN RIGHT ANTI (cr=5353 pr=0 pw=0 time=97956 us …)
......以下省略......
上記のSQLは、5,353ブロックを読み、処理が完了したように見えます。 しかし、抽出データは4,708件で、合計2つのFunctionがそれぞれ入力値だけ異なるようにして2回ずつ使用されました。 Not Deterministic FunctionがSelect節に位置しているので、Functionの実行回数を予測してみると、それぞれ抽出データ件数だけ実行されるので、4,708（抽出件数）×4（Functionの使用数）＝18,832回行われたと予想されます。 実際に予想どおりに行われたかどうかを見てみましょう。
[JISANAME()実行内訳]
SELECT MAX(jisaname)
FROM T_JISACODE
WHERE jisacode1 = :b3
AND jisacode2 = :b2
AND :b1 BETWEEN startday AND endday
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9416 0.36 0.34 0 0 0 0
Fetch 9416 0.17 0.22 0 18832 0 9416
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18833 0.53 0.56 0 18832 0 9416
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=0 us)
1 INDEX UNIQUE SCAN PK_T_JISACODE (cr=2 pr=0 pw=0 time=0 us …)
[GROUPNAME()実行内訳]
SELECT MAX(groupname)
FROM T_GROUP WHERE groupno = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9416 0.17 0.27 0 0 0 0
Fetch 9416 0.18 0.21 0 28248 0 9416
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18833 0.35 0.49 0 28248 0 9416
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
1 TABLE ACCESS BY INDEX ROWID T_GROUP (cr=3 pr=0 pw=0 time=0 us …)
1 INDEX UNIQUE SCAN PK_T_GROUP (cr=2 pr=0 pw=0 time=0 us …)
予想通り、各機能は9,416回行われ、合計18,832回行われました。
ところで、Functionで使用される入力値はコードを意味する値であり、総抽出件である4,708件の多くが重複した入力値を持つと予測することができます。 すなわち、コード値を入力値としてコード名を抽出する場合は、同じ入力値に対して常に結果値が同じであってもよいのです。 このような場合、Functionをスカラーサブクエリで実行するようにSQLを変更すると、Multi Bufferの使用が可能になり、関数の実行回数を減らすことができます。
以下は、Functionの実行をスカラーサブクエリで実行するように変更したSQLです。
SELECT ---> FUNCTIONをスカラーサブクエリに変更開始
(SELECT jisaname( gr.jisacode1 , '00' , SYSDATE ) FROM DUAL) jisaname1,
(SELECT jisaname( gr.jisacode1 , gr.jisacode2 , SYSDATE ) FROM DUAL) jisaname2,
(SELECT groupname( gr.parent_groupno ) FROM DUAL) parent_groupname,
(SELECT groupname( gr.groupno ) FROM DUAL) groupname,
---> FUNCTIONをスカラーサブクエリに変更終わり
gr.groupno,
gr.parent_groupno,
gr.jisacode1,
gr.jisacode2,
mem.memberno
FROM t_member mem INNER JOIN t_group gr ON gr.groupno = mem.groupno
WHERE... 以下省略......
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.03 0.07 0 0 0 0
Fetch 315 1.44 1.58 0 5353 0 4708
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 317 1.47 1.66 0 5353 0 4708
Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
498 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
1715 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
......以下省略......
それでは、Functionの実行回数が減ったことを確認してみましょう。
[JISANAME()実行内訳]
SELECT MAX(jisaname)
FROM T_JISACODE
WHERE jisacode1 = :b3 AND jisacode2 = :b2
AND :b1 BETWEEN startday AND endday
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 4 0 2
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=0 us)
1 INDEX UNIQUE SCAN PK_T_JISACODE (cr=2 pr=0 pw=0 time=0 us …)
[GROUPNAME()実行内訳]
SELECT MAX(groupname)
FROM T_GROUP WHERE groupno = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2213 0.09 0.08 0 0 0 0
Fetch 2213 0.14 0.06 0 6639 0 2213
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4427 0.23 0.14 0 6639 0 2213
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
1 TABLE ACCESS BY INDEX ROWID T_GROUP (cr=3 pr=0 pw=0 time=0 us …)
1 INDEX UNIQUE SCAN PK_T_GROUP (cr=2 pr=0 pw=0 time=0 us …)
なりました。 特に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値を持って十分に予測可能です。
したがって、テーブルの統計情報を調べてから使用する必要があります。
[テーブル統計情報]
AVG
TABLE NAME ROW BLOCKS
TABLESPACE NAME NUM_ROWS LEN EMP.B LAST_ANALYED
------------------------ ---------- ----- -------- ----------
FUNCTION_TABLE(SCOTT) 100000 12 253 2012-02-17
USERS
[カラム統計情報]
COLUMN_NAME DATA_TYPE DATA_LEN DENSIT NULLABLE NUM_NULLS NUM_DISTINCT
------------- ----------- -------- ---------- -------- ---------- ------------
C1 NUMBER 22 0.000010 Y 0 100000
C2 NUMBER 22 0.500000 Y 0 2
C3 VARCHAR2 4 0.038462 Y 0 26
C4 NUMBER 22 0.333333 Y 0 3
テーブル統計情報の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万回実行されると予想できます。 実際にそのようなことを確認してみましょう。
SELECT (SELECT fn_c1_codenm(c1) FROM DUAL), c1
FROM FUNCTION_TABLE;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 4.52 4.52 0 6898 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 4.52 4.55 0 6898 0 100000
Rows Row Source Operation
------- ---------------------------------------------------
100000 FAST DUAL (cr=0 pr=0 pw=0 time=46297 us)
100000 TABLE ACCESS FULL FUNCTION_TABLE (cr=6898 pr=0 pw=0 time=3112 us)
[FUNCTION実行内訳]
SELECT c2
FROM C1_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 5.38 5.68 0 0 0 0
Fetch 100000 0.74 0.72 0 300000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200001 6.13 6.41 0 300000 0 100000
予想通り10万回Functionが実行されました。
CASE [2]：入力値として使用されるC4はNUM_DISTINCTが3で、値の種類が3つで構成されています。 つまり、Functionを実行するときにほとんど同じ入力値で実行されると予想できます。 実際に3回だけ行われることを確認してみましょう。
SELECT (SELECT fn_c1_codenm(c4) FROM DUAL), c4
FROM FUNCTION_TABLE;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 0.20 0.20 0 6898 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 0.20 0.20 0 6898 0 100000
Rows Row Source Operation
------- ---------------------------------------------------
3 FAST DUAL (cr=0 pr=0 pw=0 time=3 us)
100000 TABLE ACCESS FULL FUNCTION_TABLE (cr=6898 pr=0 pw=0 time=1405 us)
[FUNCTION実行内訳]
SELECT c2
FROM C1_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 9 0 3
予想通り3回だけ行われました。 このように CASE[2] のように Main SQL の結果のうち Function の入力値として使用されるカラムの NUM_DISTINCT が少ないとき、Function の実行をスカラーサブクエリに変更すると大きな効果が得られ、逆の場合は利点が得られない結果が見られました。
今後は、Functionをスカラーサブクエリで行うべきか否かを決定する際に、入力値に対するデータ分布を分析してから決めることにしましょう。
６.３.４ SELECT節に使われたFUNCTIONを結合に変更しよう
CASE [1]で使用されている関数を削除して結合に変更しましょう。
SELECT b.c2, a.c1
FROM function_table a,
c1_code_nm b
WHERE a.c1 = b.c1(+)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 6668 0.31 0.28 0 7350 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 0.32 0.31 0 7350 0 100000
Rows Row Source Operation
------- ---------------------------------------------------
100000 HASH JOIN OUTER (cr=7350 pr=0 pw=0 time=165798 us)
100000 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=104 us)
100000 TABLE ACCESS FULL C1_CODE_NM (cr=7105 pr=0 pw=0 time=1188 us)
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節に移そう
SELECT /*+ first_rows */
……省略……
FROM t_regmember r INNER JOIN t_member m
ON r.memberno = m.memberno
……省略……
WHERE 1=1
AND r.joinday BETWEEN '20100219' AND '20100301'
AND r.regjoincode IN ('01','02','03','04','05','09','10')
AND rp.sunabclscode1 IN ('04','05','06','07','08')
AND rp.regcyclecode IN ('01','02')
AND get_birthday_by_juminno(m.jumin1,jumin2_decrypt(m.jumin2enc)) BETWEEN
to_date('19000101','yyyymmdd') AND to_date('19911231','yyyymmdd')
（注）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を実行するからなのです。
FROM (
SELECT /*+ first_rows */
……省略……
,get_birthday_by_juminno(m.jumin1,jumin2_decrypt(m.jumin2enc))
AS function_call --> WHERE節でスカラーサブクエリでFunction実行位置変更
FROM t_regmember r INNER JOIN t_member m
ON r.memberno = m.memberno
……省略……
WHERE 1=1
AND r.joinday BETWEEN '20100219' AND '20100301'
AND r.regjoincode IN ('01','02','03','04','05','09','10')
AND rp.sunabclscode1 IN ('04','05','06','07','08')
AND rp.regcyclecode IN ('01','02')
) A
WHERE A.function_call BETWEEN to_date('19000101', 'yyyymmdd')
AND to_date('19911231', 'yyyymmdd')
(注２）スカラーサブクエリから抽出した後の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の実行履歴を確認してみましょう。
SELECT /*+ LEADING(T1 T2) USE_HASH(T1 T2) */
t1.c3, t2.c4
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c4 = 2
AND t1.c1 = t2.c1
AND t2.c3 IN ( 'A' )
AND fn_c2_codenm(T2.C4) BETWEEN 'A' AND 'B'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 113 0.45 0.41 0 838 0 1667
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 115 0.45 0.41 0 838 0 1667
Rows Row Source Operation
------- ---------------------------------------------------
1667 HASH JOIN (cr=20826 pr=0 pw=0 time=995069 us)
16667 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=52 us)
5000 TABLE ACCESS FULL C1_CODE_NM (cr=20581 pr=0 pw=0 time=973594 us)
[FUNCTION実行内訳]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.54 0.53 0 0 0 0
Fetch 10000 0.01 0.05 0 20000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.56 0.58 0 20000 0 10000
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を実行しました。
SELECT /*+ NO_MERGE(A) */
*
FROM (
SELECT /*+ LEADING(T1 T2) USE_HASH(T1 T2) */
t1.c3 ,
t2.c4 ,
t2.c4 ,
fn_c2_codenm(t2.c4) AS ft2c4
FROM FUNCTION_TABLE T1 t1 ,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c4 = 2
AND t1.c1 = t2.c1
AND t2.c3 IN ( 'A' )
) A
--WHERE ft2c4 BETWEEN 'A' AND 'B' --->注釈処理する。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 113 0.09 0.08 0 606 0 1667
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 115 0.09 0.08 0 606 0 1667
Rows Row Source Operation
------- ---------------------------------------------------
1891 VIEW (cr=4384 pr=0 pw=0 time=201025 us)
1891 HASH JOIN (cr=826 pr=0 pw=0 time=20593 us)
16667 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=52 us)
5000 TABLE ACCESS FULL C1_CODE_NM (cr=581 pr=0 pw=0 time=5079 us)
[FUNCTION実行内訳]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1891 0.14 0.10 0 0 0 0
Fetch 1891 0.03 0.01 0 3782 0 1891
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3783 0.17 0.11 0 3782 0 1891
上記のようにSQLを変更すると、Functionは結合に成功したデータに対してのみ行われるため、実行回数は10,000回から1,891回に確実に減少しました。
次に、Functionを実行して抽出されたデータをFT2C4 BETWEEN ‘A’ AND ‘B’条件でチェックするように条件を追加してSQLを実行しました。 以下のトレース結果を確認してみましょう。
SELECT /*+ NO_MERGE(A) */ *
FROM (
SELECT /*+ LEADING(T1 T2) USE_HASH(T1 T2) */
t1.c3,
t1.c4,
fn_c2_codenm(t2.c4) AS ft2c4
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c4 = 2
AND t1.c1 = t2.c1
AND t2.c3 IN ( 'A' )
) A
WHERE ft2c4 BETWEEN 'A' AND 'B' ---> FUNCTION実行結果を比較する条件追加
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 113 0.51 0.59 0 618 0 1667
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 115 0.51 0.59 0 618 0 1667
Rows Row Source Operation
------- ---------------------------------------------------
1667 VIEW (cr=24384 pr=0 pw=0 time=1448413 us)
1667 HASH JOIN (cr=20826 pr=0 pw=0 time=1234042 us)
16667 TABLE ACCESS FULL FUNCTION_TABLE (cr=245 pr=0 pw=0 time=58 us)
5000 TABLE ACCESS FULL C1_CODE_NM (cr=20581 pr=0 pw=0 time=1213581 us)
[FUNCTION実行内訳]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 11891 0.90 0.79 0 0 0 0
Fetch 11891 0.06 0.07 0 23782 0 11891
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23783 0.96 0.86 0 23782 0 11891
最終抽出件数は1,667件で、データは正常に抽出されました。 しかし、Functionの実行回数が大幅に増加しました。 さらに、原文SQLでは、10,000回実行されたFunctionの実行回数がさらに増え、実行時間も増加しました。 このような現象がなぜ発生したのでしょうか?
その理由を調べるために、10053トレースを使用して、Optimizerが内部で何をしたかを分析してみましょう。
10053トレースでFPDが発生した部分を確認してみましょう。
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
PM: Passed validity checks.
FPD: Considering simple filter push in SEL$1 (#0)
FPD: Current where clause predicates in SEL$1 (#0) :
"A"."FT2C4">='A' AND "A"."FT2C4"<='B'?> simple filterをviewに反映させてみる。
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "A"."FT2C4">='A' AND "A"."FT2C4"<='B' AND 'B'>='A'
after transitive predicate generation: "A"."FT2C4">='A' AND "A"."FT2C4"<='B' AND 'A'<='B'
finally: "A"."FT2C4">='A' AND "A"."FT2C4"<='B' AND 'A'<='B'
FPD: Following transitive predicates are generated in SEL$1 (#0) : 'A'<='B'
JPPD: JPPD bypassed: View not on right-side of outer join
FPD: Following are pushed to where clause of SEL$2 (#0) :
"SCOTT"."FN_C2_CODENM"("T2"."C4")>='A' AND "SCOTT"."FN_C2_CODENM"("T2"."C4")
<='B' AND 'A'<='B'
FPD: Considering simple filter push in SEL$2 (#0)
FPD: Current where clause predicates in SEL$2 (#0) :
"T1"."C2"=0 AND "T1"."C4"=2 AND "T1"."C1"="T2"."C1" AND "T2"."C3"='A' AND
"SCOTT"."FN_C2_CODENM"("T2"."C4")>='A' AND "SCOTT"."FN_C2_CODENM"("T2"."C4")
<='B' AND 'A'<='B'
kkogcp: try to generate transitive predicate from check constraints for SEL$2 (#0)
predicates with check contraints: "T1"."C2"=0 AND "T1"."C4"=2 AND "T1"."C1"="T2"."C1" AND "T2"."C3"='A' AND "SCOTT"."FN_C2_CODENM"("T2"."C4")>='A' AND "SCOTT"."FN_C2_CODENM"("T2"."C4")<='B' AND 'B'>='A'
after transitive predicate generation: "T1"."C2"=0 AND "T1"."C4"=2 AND "T1"."C1"="T2"."C1" AND "T2"."C3"='A' AND "SCOTT"."FN_C2_CODENM"("T2"."C4")>='A' AND "SCOTT"."FN_C2_CODENM"("T2"."C4")<='B' AND 'A'<='B'
finally: "T1"."C2"=0 AND "T1"."C4"=2 AND "T1"."C1"="T2"."C1" AND "T2"."C3"='A' AND "SCOTT"."FN_C2_CODENM"("T2"."C4")>='A' AND "SCOTT"."FN_C2_CODENM"("T2"."C4")<='B' AND 'A'<='B'
apadrv-start: call(in-use=2912, alloc=280224), compile(in-use=58800, alloc=59840)
kkoqbc-start
: call(in-use=2920, alloc=280224), compile(in-use=60792, alloc=63984)
kkoqbc-subheap (create addr=000000000C27C708)
トレースの結果を見ると、OptimizerがFPD（Filter Push Down）に成功し、インラインビューの外側の条件をビュー内に反映させたことがわかります。
FunctionはSelect句で実行され、Functionから抽出された値を使用してビューの外側で条件を処理するためにSQLを書き換えました。 ところで、QUERY TRANSFORMATIONによってFPDが発生し、Where句とSelect句のFunctionがそれぞれ実行され、実行回数が異常に増えたのです。
実行計画でPredicate InformationとColumn Projection Informationを確認してみましょう。
---------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 170 | |
| 1 | VIEW | | 12 | 24K | 170 | 00:00:03 |
| 2 | HASH JOIN | | 12 | 264 | 170 | 00:00:03 |
| 3 | TABLE ACCESS FULL | FUNCTION_TABLE| 16K | 195K | 59 | 00:00:01 |
| 4 | TABLE ACCESS FULL | C1_CODE_NM | 12 | 120 | 110 | 00:00:02 |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T1"."C1"="T2"."C1")
3 - filter(("T1"."C4"=2 AND "T1"."C2"=0))
4 - filter(("T2"."C3"='A' AND "FN_C2_CODENM"("T2"."C4")>='A' AND "FN_C2_CODENM"("T2"."C4")<='B'))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"."C3"[VARCHAR2,4], "A"."C4"[NUMBER,22], "FT2C4"[VARCHAR2,4000]
2 - (#keys=1) "T1"."C3"[VARCHAR2,4], "T2"."C4"[NUMBER,22]
3 - "T1"."C1"[NUMBER,22], "T1"."C3"[VARCHAR2,4]
4 - "T2"."C1"[NUMBER,22], "T2"."C4"[NUMBER,22]
Predicate Informationを見ると、PLANにID 4に該当するFilter条件がFPDによりSQLが変更され、これによりFunctionが2回行われました。 そして、Column Projection Informationを見ると、ビューに対応するID 1にFT2C4がVARCHAR2（4000）タイプであるとみなされ、FunctionがSelect句でもう一度行われたことが分かります。
上記の情報に基づいて、Oracleが変更したSQLの様子を見てみると、次のようになります。
SELECT /*+ NO_MERGE(A)) */
*
FROM (
SELECT /*+ LEADING ("T1" "T2") USE_HASH ("T2") USE_HASH ("T1") */
t1.c3,
t2.c4,
FN_2_CODENM(t2.c4) FT2C4 -->不必要に羅列されたSELECT節の
FUNCTION
FROM FUNCTION_TABLE T1 ,
C1_CODE_NM T2
WHERE t1.c4 =2
AND t1.c2 =0
AND t1.c1 =t2.C1
AND t2.c3 ='A'
AND FN_C2_CODENM(t2.c4) >='A' ---> FPDによって条件が中に浸透
AND FN_C2_CODENM(t2.c4) <='B' ---> FPDによって条件が中に浸透
) A
上記のような性能問題を解決するためには、意図しなかったFPDが発生しないようにすればよいのです。 Functionをスカラーサブクエリで実行するようにSQLを作成すると、Functionを実行する動作方式が異なるため、FPDが発生せずSQLのパフォーマンスを向上させることができます。
Select句のFunctionの実行をスカラーサブクエリに変更し、実行結果を確認してみましょう。
SELECT /*+ NO_MERGE(A) */
*
FROM ( SELECT /*+ LEADING(T1 T2) USE_HASH(T1 T2) */
t1.c3, ,
t2.c4
(SELECT fn_c2_codenm(T2.C4) FROM DUAL) AS ft2c4
FROM FUNCTION_TABLE t1 ,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c4 = 2
AND t1.c1 = t2.c1
AND t2.c3 IN ( 'A' )
) A
WHERE ft2c4 between 'A' AND 'B'
-----------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------
| 1 | FAST DUAL | | 1 |00:00:00.01| 0 |
| 2 | VIEW | | 1667 |00:00:00.03| 828 |
|* 3 | FILTER | | 1667 |00:00:00.03| 828 |
|* 4 | HASH JOIN | | 1667 |00:00:00.03| 826 |
|* 5 | TABLE ACCESS FULL| FUNCTION_TABLE | 1667 |00:00:00.01| 245 |
|* 6 | TABLE ACCESS FULL| C1_CODE_NM | 5000 |00:00:00.01| 581 |
| 7 | FAST DUAL | | 1 |00:00:00.01| 0 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((>='A' AND <='B'))
4 - access("T1"."C1"="T2"."C1")
5 - filter(("T1"."C4"=2 AND "T1"."C2"=0))
6 - filter("T2"."C3"='A')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - "A"."C3"[VARCHAR2,4], "A"."C4"[NUMBER,22], "FT2C4"[VARCHAR2,4000]
3 - "T1"."C3"[VARCHAR2,4], "T2"."C4"[NUMBER,22]
4 - (#keys=1) "T1"."C3"[VARCHAR2,4], "T2"."C4"[NUMBER,22]
5 - "T1"."C1"[NUMBER,22], "T1"."C3"[VARCHAR2,4]
6 - "T2"."C1"[NUMBER,22], "T2"."C4"[NUMBER,22]
[FUNCTION実行内訳]
SELECT C2
FROM C2_CODE_NM
WHERE C1 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Predicate Informationを確認すると、Functionの実行後に比較する条件が消えてFPDが発生しませんでした。
Oracleのバージョンによっては、FunctionをSelect句に移動してインラインビューで作成し、ビューの外側からフィルタリングする場合は、FPDによってパフォーマンスが悪くなる可能性があるため、SQLを書き換えた後にパフォーマンスの問題が発生しないことを確認する必要があります。
これまで、Functionの動作方式とSQLのパフォーマンス問題を改善した事例について一緒に調べてきました。 Functionの使用はプログラムの開発やメンテナンスなど様々な面で効率的ですが、逆に非効率的に使用されたFunctionはDBサーバの性能に大きな悪影響を及ぼす可能性があります。
したがって、この章では、Functionの正確な動作方法を理解し、Functionを使用するときに常に効率的に実行されるようにSQLを作成してください。
これで、三回に渡ってお送りしました「FUNCTION実行とSQL性能問題の理解」に関しましての情報提供は終了です。
いかがでしたでしょうか？次回からは、「DECODE & CASE WHENの理解および条件ステートメントの処理」に関します情報を、二回に渡ってお送りする予定にしております。ご期待ください。では See you^^
