2022.11.30
SQLチューニング（第40回）「照会パターンに合うようにSQL実行計画を分岐すること」（2／2回）
今回は、「照会パターンに合うようにSQL実行計画を分岐すること」の2回目です、それでは、早速、始めましょう。
11.２ SQLの照会パターン別の実行計画を分岐しよう
11.2.１ 色々な条件を持つSQLの実行計画分岐すること
それでは前回調べてみた内容を基に下の性能問題SQLの問題点を把握して、SQLの性能を改善してみることにしよう。
[性能問題SQL]
SELECT/*+ INDEX(A IDX_TB_ADDR_01) */ --> WHERE節照会パターンに関係なく実行計画固定
a.*
FROM imsi.tb_addr a
WHERE userid = :b1
AND ( name LIKE :b2||'%'
OR email LIKE :b3||'%' )
SELECT STATEMENT CHOOSE-Cost : 3
TABLE ACCESS BY INDEX ROWID IMSI.TB_ADDR
INDEX RANGE SCAN IMSI.IDX_TB_ADDR_01(NU) (USERID,NAME)
[インデックス情報]
INDEX_NAME COLUMN LIST
--------------- -------------
IDX_TB_ADDR_01 USERID, NAME
IDX_TB_ADDR_02 USERID, EMAIL
IDX_TB_ADDR_03 UKEY
PK_TB_ADDR USERID, UKEY
[性能問題SQL]は特定使用者(USERID)のデータを抽出する時、検索条件にNAMEまたはEMAIL条件が追加で照会される。 [性能問題SQL]は多くの使用者が同時に使うSQLで、特定使用者(USERID)で照会される時データ件数が多く、USERID条件だけ利用してインデックスを照会される場合I/O処理量がとてもたくさん発生します。
したがって状況によってUSERIDと追加条件で構成された結合インデックスを利用して照会になるようにしてこそ効率的でしょう。 しかし[性能問題SQL]はNAMEとEMAILに入力される値により照会パターンが違わなければならないにも関わらず、一つのSQLで作成されています。 その上ヒントによって実行計画まで固定されているので特定パターンの場合、非効率的に実行されています。 例えば、画面でNAME (:B2 IS NULL)で検索しないでEMAILで問い合わせる場合、該当SQLのインデックスはIDX_TB_ADDR_01インデックスでないIDX_TB_ADDR_02インデックスを利用して処理してこそ効率的なのですが、IDX_TB_ADDR_01インデックスを選択するようにヒントが適用されているので非効率的な実行になります。
結局照会条件であるEMAIL条件はテーブル アクセス後Filter Predicateで処理されることによって非効率が発生することになります。 その上実際の業務でNAMEよりはEMAIL条件で、たくさん実行されるならば深刻な性能問題を起こすことになります。
したがってNAMEとEMAIL条件に対して照会パターンを分析した後、各パターン別で最適な実行計画で実行できるようにSQLを再作成しなければなりません。
前回調べてみた通り、SQLをパターン別で分離した後に、照会される条件に対する値をチェックした後、最も有利なSQLを選択して実行するようにしなければならないのです。 もし、あえて一つのSQLで作成するべきだとすれば[改善後SQL]のようにUNION ALLを利用して、実行計画を分離させる方法もあります。 UNION ALLを使った方法は分離しなければならない条件が少ないならばなかなか良い方法ですが、条件が多いならばSQLが長くなってパッシング時負担となる恐れがあるという短所があります。
[改善後SQL]
SELECT/*+ INDEX(A IDX_TB_ADDR_01) */ ---> NAMEだけで検索する場合
a.*
FROM imsi.tb_addr a
WHERE :b2 IS NOT NULL AND :b3 IS NULL
AND userid = :b1
AND name LIKE :b2||'%'
UNION ALL
SELECT/*+ INDEX(A IDX_TB_ADDR_02) */ ---> EMAILだけで検索する場合
a.*
FROM imsi.tb_addr a
WHERE :b2 IS NULL AND :b3 IS NOT NULL
AND userid = :b1
AND email LIKE :b3||'%'
UNION ALL
SELECT/*+ INDEX(A IDX_TB_ADDR_01) */ ---> NAME,EMAIL全部検索する場合
a.*
FROM imsi.tb_addr a
WHERE :b2 IS NOT NULL AND :b3 IS NOT NULL
AND userid = :b1
AND name LIKE :b2||'%'
AND email LIKE :b3||'%'
UNION ALL
SELECT/*+ INDEX(A IDX_TB_ADDR_01) */ ---> NAME,EMAIL条件なしで検索する場合
a.*
FROM imsi.tb_addr a
WHERE :b2 IS NULL AND :b3 IS NULL
AND userid = :b1
SELECT STATEMENT CHOOSE-Cost : 12
UNION-ALL
FILTER (:Z IS NOT NULL AND :Z IS NULL)
TABLE ACCESS BY INDEX ROWID IMSI.TB_ADDR
INDEX RANGE SCAN IMSI.IDX_TB_ADDR_01(NU) (USERID,NAME)
FILTER (:Z IS NOT NULL AND :Z IS NULL)
TABLE ACCESS BY INDEX ROWID IMSI.TB_ADDR
INDEX RANGE SCAN IMSI.IDX_TB_ADDR_02(NU) (USERID,EMAIL)
FILTER (:Z IS NOT NULL AND :Z IS NOT NULL)
TABLE ACCESS BY INDEX ROWID IMSI.TB_ADDR
INDEX RANGE SCAN IMSI.IDX_TB_ADDR_01(NU) (USERID,NAME)
FILTER (:Z IS NULL AND :Z IS NULL)
TABLE ACCESS BY INDEX ROWID IMSI.TB_ADDR
INDEX RANGE SCAN IMSI.IDX_TB_ADDR_01(NU) (USERID,NAME)
今までSQLが色々な照会パターンを有しているにも関わらず、一つのSQLで実現する場合、発生しうる性能問題に対して簡略に調べてみました。 最近ではLIKE ‘%’方式のSQLで色々な照会パターンを処理する方法を使うよりは、プログラム(XMLなど)で照会条件をダイナミックに追加して、照会パターン別でそれぞれ他のSQLが実行されるように実現する場合が多くあります。 この方法はLIKE ‘%’よりはるかに改善された方法ですが、Optimizerの誤った解釈で深刻な性能問題が発生したり、誤ったヒント(すべてのケースに適用されるヒント適用)を適用して性能問題が発生する場合は相変らず存在するので、照会パターン別で実行計画を分離することができるようにSQLを作成するのを検討しなければなりません。 特にプログラムにヒントを付与する場合、それぞれの条件に適合するように適用しなければならないという点を必ず覚えておいてください。
それでは前回調べてみた内容のように色々な照会パターンを持つSQLだけ実行計画分離が必要なのでしょうか？ そうではありません。 LIKE,NVLなどを使わないで、条件が固定されたSQLも条件値の範囲や照会値のLENGTHによりインデックスを実行するべきか、でなければFull Table Scanをするべきかを決めなければならない場合があるためです。
実行計画分離が必要な場合を整理して見るならば、以下の二種類が代表的なケースとなります。
・ 色々な照会パターン処理を一つのSQLで作成した場合(LIKE,NVL,DECODEなど)
・ 照会変数値の範囲により他の実行計画が必要な場合
これから、条件は固定されるが、照会変数値の範囲により実行計画分離が必要な場合に対して調べてみることにしましょう。
11.2.1.１ 変数値の範囲によりSQLを分離しよう
“変数値の範囲にともなう実行計画分離”はSQLを問い合わせる変数値によって抽出されるデータ件数の差が大きい場合に使うことができます。 例えば、抽出されるデータ件数が少ない場合はインデックス スキャンで実行されるようにして、データが多い場合はFull Table Scanで実行されるように誘導することを言います。 もし、変数値の範囲により最小限二種類の実行計画に分かれなければならない場合ですが、これを一つのSQLで作成したとすれば、値の範囲に関係なく一つの実行計画と解釈されるので、特定変数値の範囲に対しては非効率が発生することになります。 理解を助けるために例をあげるとすると、以下のような照会パターンになります。
SELECT *
FROM cust
WHERE reg_date between :B1 and :B2 ;
REG_DATEが顧客の加入日カラムならば、:B1 ～:B2間に加入したすべての顧客を抽出しようとするSQLです。 もし、CUST (顧客テーブル)テーブルに顧客が一日平均500人が加入すると仮定すると、加入日者の範囲によりデータ抽出件数は多くの差が発生することで、インデックスを使うかFull Table ScanをするかによりSQL実行時性能偏差も大きく発生するでしょう。
したがってREG_DATEに対する照会パターンが平常時には1日照会が大部分だが、最低1ヶ月に一度は最近一年間のデータを抽出するべきだとすれば、抽出されるデータ件数は下記のように大きい差が発生します。
1日照会 ： 500件抽出
1年照会 ： 500件* 365日= 182,500件抽出
平常時照会されるSQLの実行は1日データを問い合わせるのでデータ量が少ないのでインデックス スキャンで実行されることが効率的です。 しかし、1ヶ月に一度照会される1年分データを問い合わせる時にはデータ量が多いので、インデックス スキャンで実行される場合、急激な性能低下が発生することになるでしょう。 このように変数値の範囲により、SQL性能の偏差が大きい場合にも効率的なSQL実行になるべく実行計画を分離させる改善案が必要となります。
照会変数値によりSQLを分離しなければならない場合の大部分は照会条件が日データの範囲検索である場合が多いですが、日データの範囲検索ではなくても、照会パターン別で分けなければならない場合も存在するのでWhere節の照会パターンを綿密に検討しなければなりません。
以下の事例を通じていWhere節照会変数値の範囲により実行計画を分離しなければならないSQLなのかを確認して、改善案を導き出して適用する方法に対して調べてみることにしましょう。
[性能問題SQL]
SELECT /*+ LEADING(B) USE_HASH(A) USE_NL(C) INDEX(B IX_TB_G10_03) */
a.*,
b.*
FROM tb_m02 a ,
tb_g10 b ,
tb_g30 c
WHERE a.mem_no = b.mem_no
AND a.goji_yyyymm = :b65
AND b.brch_cd = :b66
AND c.mem_no = a.mem_no
AND c.sn_no BETWEEN :b67 AND :b68 ;
[性能問題SQL]で抽出しようと思うデータは特定月(GOJI_YYYYMM)に特定支社(BRCH_CD)のデータのうちにSN_NO照会値の範囲にあるデータです。 SQLは先に支社条件に満足するデータを抽出した後(TB_G10テーブル),特定月に生成された(GOJI_YYYYMM条件)データ(TB_M02テーブル)とHash Joinを実行します。 そしてこの対象をTB_G30テーブルとNested Loops Joinを実行した後SN_NOに範囲に該当するかをチェックして、最終データを抽出するようにヒントが適用されています。 ところで[性能問題SQL]に追加されているヒントは非効率を有しています。 どんな非効率を有しているのでしょうか？ 次の照会パターン[1],[2]を通じて確認してみましょう。
（１）照会パターン[1]. SN_NO照会条件が特定値である場合
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 1 1.80 10.90 8691 12931 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.81 10.91 8691 12931 0 0
Rows Row Source Operation
---------- ---------------------------------------------------
0 FILTER (cr=12931 pr=8691 pw=0 time=10900067 us) ---> SN_NOでFILETER処理された以後0件
0 TABLE ACCESS BY INDEX ROWID TB_G30 (cr=12931 pr=8691 pw=0 time=10900063 us …)
3339 NESTED LOOPS (cr=12931 pr=8691 pw=0 time=12132159 us cost=3110 size=99 card=1)
3338 HASH JOIN (cr=6461 pr=6393 pw=0 time=5700163 us cost=2982 size=2961 card=47)
6516 INDEX RANGE SCAN IX_TB_G10_03 (cr=55 pr=0 pw=0 time=4626 us …)
1013506 PARTITION RANGE ALL PARTITION:1 8 (cr=6406 pr=6393 pw=0 time=7224800 us …)
1013506 PARTITION RANGE SINGLE PARTITION:KEY KEY (cr=6406 pr=6393 …)
1013506 INDEX RANGE SCAN IX_TB_M02_01 PARTITION:(cr=6406 pr=6393 …)
0 INDEX RANGE SCAN IX_TB_G30_03 (cr=6470 pr=2298 pw=0 time=3806275 us …)
（２） 照会パターン[2]. SN_NO照会条件が全体範囲である場合
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 224 2.35 16.71 10901 16562 0 3338
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 226 2.36 16.73 10901 16562 0 3338
Rows Row Source Operation
---------- ---------------------------------------------------
3338 FILTER (cr=16562 pr=10901 pw=0 time=13378740 us) ---> SN_NOでほとんどFILTERならない
3338 TABLE ACCESS BY INDEX ROWID TB_G30 (cr=16562 pr=10901 pw=0 time=13374882 us …)
6740 NESTED LOOPS (cr=13619 pr=8443 pw=0 time=27531751 us cost=3110 size=99 card=1)
3338 HASH JOIN (cr=6683 pr=6383 pw=0 time=7488265 us cost=2982 size=2961 card=47)
6516 INDEX RANGE SCAN IX_TB_G10_03 (cr=55 pr=0 pw=0 time=5524 us …)
1013506 PARTITION RANGE ALL PARTITION:1 8 (cr=6628 pr=6383 pw=0 time=6954552 us …)
1013506 PARTITION RANGE SINGLE PARTITION:KEY KEY (cr=6628 pr=6383 …)
1013506 INDEX RANGE SCAN IX_TB_M02_01 PARTITION:(cr=6628 pr=6383 …)
3401 INDEX RANGE SCAN IX_TB_G30_03 (cr=6936 pr=2060 pw=0 time=4350319 us …)
照会パターン[1]と照会パターン[2]のトレース結果を見れば照会パターン[1]は抽出結果が0件、照会パターン[2]は3,338件です。 照会パターン[1]の場合TB_G30テーブルと結合処理した以降で0件になるのを確認することができます。 一方、照会パターン[2]の場合は0件でない3,338件のデータが抽出されますが、TB_G30テーブル結合時IX_TB_G30_03インデックスをアクセスする時データ差が発生しています。
TB_G30テーブルはTB_M02テーブルやTB_G10テーブルと結合条件だけ持って結合をする場合、全部結合に成功しなければならないという特徴を有していました。 これはTB_G30テーブルは結合条件だけでは先行テーブルで抽出されたデータ件数が減らないという意味です。
すなわち、IX_TB_G30_03インデックス構成カラムのうち定数条件で入力されるカラムであるSN_NOの照会値により結果件数が大きく変わるということです。
したがって照会パターン[1]は先行テーブルで抽出された3,338件中SN_NO照会値に該当するデータが存在しなくて最終抽出件数が0件になったのです。 一方、照会パターン[2]は先行テーブルで抽出されたすべてのデータがSN_NO照会範囲に該当するので3,338件が抽出されました。
ここでSN_NOカラムはNUM_DISTINCT値が高くてEQUAL照会時平均2～3件を抽出するので、SN_NO条件がEQUALで照会されるならばTB_G30テーブルを先に読んでSN_NOカラムで構成されたインデックスを活用すればより効率的なSQL実行が可能でしょう。
テーブル 名:tb_g30
DATA NUMBER
COLUMN_NAME DATA_TYPE LEN N DISTINCT DENSITY NUM_NULLS
------------- ---------- ----- --- --------- ----------- ---------
SN_NO VARCHAR2 11 N 4161132 0.000000240 0 --->照会条件
…省略
AVG
TABLE NAME ROW BLOCKS DEG AVG CHAIN
TABLESPACE NAME NUM_ROWS LEN EMP.B REE SPACE CNT
----------------- ---------- ----- ------- --- ----- ------
TB_G30 10590156 138 212300 1 877 0
INDEX_NAME COLUMN LIST
------------- -----------------------------
IX_TB_G30_01 MAIN_MEM_SN_NO, INSU_CD
IX_TB_G30_02 STO_SN_NO, STO_INSU_CD
IX_TB_G30_03 MEM_NO,INSU_CD,SN_NO --->結合時読むインデックス
PK_TB_G30 SN_NO, INSU_CD
トレース結果を見れば、照会パターン[1]と照会パターン[2]のSQLはSN_NO照会条件の範囲により実行計画が違うように実行されなければならないと判断されます。 それなら先にSN_NOカラムに対する照会値が持つパターンを確認する必要があります。 Oracle 10g以上ではV$SQL_BIND _CAPTUREとDBA_HIST_SQLBINDビューを利用してBind値を問い合わせることができます
DBA_HIST_SQLBINDビューは過去にSQL実行時使ったBind値の履歴を有しているので、SN_NOカラムの照会パターン(定数値)を確認するためにDBA_HIST_SQLBINDビューを問い合わせてSN_NOの照会値を確認してみました。
SELECT name,
TO_CHAR(last_captured,'yyyymmdd hh24:mi:ss') last_captured,
datatype,
value_string
FROM DBA_HIST_SQLBIND
WHERE sql_id = :sql_id ;
NAME LAST_CAPTURED DATATYPE VALUE_STRING
---- ----------------- ------- -----------
:B65 20101213 14:46:06 1 201011
:B66 20101213 14:46:06 1 0403
:B67 20101213 14:46:06 1 00000000000 ---> SN_NO照会開始値
:B68 20101213 14:46:06 1 99999999999 ---> SN_NO照会終わり値
:B65 20101227 16:06:59 1 201012
:B66 20101227 16:06:59 1 0331
:B67 20101227 16:06:59 1 57011155797 ---> SN_NO照会開始値
:B68 20101227 16:06:59 1 57011155797 ---> SN_NO照会終わり値
…(省略)
DBA_HIST_SQLBINDビューを問い合わせた結果を見れば、SN_NO照会値のパターンは大きく二種類に分かれるのを確認することができます。 全体データを問い合わせたり、特定値で問い合わせることです。 それなら[性能問題SQL]に対する改善案はSN_NO照会値のパターンにより実行計画分離が最も適合するとみられます。 なぜなら、SN_NOに対する条件が特定値で照会される場合には平均2～3件だけ抽出されるので、TB_G30テーブルを先にアクセスすることが効率的なためです。
結論的にSN_NO値を問い合わせる変数値は:B67 (開始値)と:B68 (終わり値)ですが、二つの変数値が同じ場合には特定値で照会されて、二つの変数値がそれぞれ違うのは特定値でない全体を問い合わせる場合です。 したがって二種類の照会パターンにより、適切に実行計画が分離できるようにUNION ALLでSQLを再作成してみましょう
[改善案導き出し]
SELECT *
FROM (
SELECT /*+ LEADING(C) USE_NL(C A B)index(C PK_TB_G30) */
a.*,b.*
FROM tb_m02 a,
tb_g10 b,
tb_g30 c
WHERE a.mem_no = b.mem_no
AND a.goji_yyyymm = :b65
AND b.brch_cd = :b66
AND c.mem_no = a.mem_no
AND c.sn_no BETWEEN :b67 AND :b68
) X
WHERE:b67 =:b68 --->変数値が同じ場合? SN_NOは特定値照会
UNION ALL
SELECT *
FROM (
SELECT /*+ LEADING(B A) USE_HASH(A) USE_NL(C) INDEX(B IX_TB_G10_03) */
a.*, b.*
FROM tb_m02 a ,
tb_g10 b ,
tb_g30 c
WHERE a.mem_no = b.mem_no
AND a.goji_yyyymm = :b65
AND b.brch_cd = :b66
AND c.mem_no = a.mem_no
AND c.sn_no BETWEEN :b67 AND :b68
) X
WHERE:b67 <>:b68;--->変数値が違う場合? SN_NOは全体照会
[照会パターン[1]の改善後トレース結果]
var b65 varchar2(100)
var b66 varchar2(100)
var b67 varchar2(100)
var b68 varchar2(100)
exec :b65 := '201011'
exec :b66 := '0128'
exec :b67 := '57013408375'
exec :b68 := '57013408375'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.07 0.08 0 0 0 0
Fetch 1 0.00 0.00 3 7 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.07 0.09 3 7 0 0
Rows Row Source Operation
---------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID TB_G10 (cr=7 pr=3 pw=0 time=3753 us …)
1 NESTED LOOPS (cr=7 pr=3 pw=0 time=3740 us cost=125 size=47 card=1)
0 VIEW VW_JF_SET$5DA70CA4 (cr=7 pr=3 pw=0 time=3739 us …)
0 UNION-ALL (cr=7 pr=3 pw=0 time=3738 us)
0 FILTER (cr=7 pr=3 pw=0 time=3726 us)
0 NESTED LOOPS (cr=7 pr=3 pw=0 time=3722 us cost=9 size=73 card=1)
1 TABLE ACCESS BY INDEX ROWID TB_G30 (cr=4 pr=2 …)
1 INDEX RANGE SCAN PK_TB_G30 (cr=3 pr=1 pw=0 time=627 us …)
0 PARTITION RANGE ITERATOR PARTITION:KEY KEY (cr=3 pr=1 …)
0 PARTITION RANGE SINGLE PARTITION:KEY KEY (cr=3 pr=1 …)
0 INDEX UNIQUE SCAN PK_TB_M02 PARTITION:(cr=3 pr=1 …)
0 FILTER (cr=0 pr=0 pw=0 time=3 us)
0 TABLE ACCESS BY INDEX ROWID TB_G30 (cr=0 pr=0 …)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=112 size=73 card=1)
0 PARTITION RANGE ALL PARTITION:1 8 (cr=0 pr=0 …)
0 PARTITION RANGE SINGLE PARTITION:KEY KEY (cr=0 pr=0 …)
0 INDEX RANGE SCAN IX_TB_M02_01 PARTITION:(cr=0 pr=0 …)
0 INDEX RANGE SCAN IX_TB_G30_03 (cr=0 pr=0 pw=0 time=0 us …)
0 INDEX UNIQUE SCAN PK_TB_G10 (cr=0 pr=0 pw=0 time=0 us …)
[照会パターン[2]の改善後トレース結果]
var b65 varchar2(100)
var b66 varchar2(100)
var b67 varchar2(100)
var b68 varchar2(100)
exec :b65 := '201011'
exec :b66 := '0128'
exec :b67 := '00000000000'
exec :b68 := '99999999999'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 224 0.20 0.77 0 16558 0 3338
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 226 0.21 0.79 0 16558 0 3338
Rows Row Source Operation
---------- ---------------------------------------------------
3338 UNION-ALL (cr=16558 pr=0 pw=0 time=564043 us)
0 VIEW (cr=0 pr=0 pw=0 time=3 us cost=31901 size=158856 card=6619)
0 FILTER (cr=0 pr=0 pw=0 time=1 us)
0 TABLE ACCESS BY INDEX ROWID TB_G10 (cr=0 pr=0 …)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us …)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us …)
0 TABLE ACCESS BY INDEX ROWID TB_G30 (cr=0 pr=0 …)
0 INDEX RANGE SCAN PK_TB_G30 (cr=0 pr=0 pw=0 time=0 us …)
0 PARTITION RANGE ITERATOR PARTITION:KEY KEY (cr=0 pr=0 …)
0 PARTITION RANGE SINGLE PARTITION:KEY KEY (cr=0 pr=0 …)
0 INDEX UNIQUE SCAN PK_TB_M02 PARTITION:(cr=0 pr=0 …)
0 INDEX UNIQUE SCAN PK_TB_G10 (cr=0 pr=0 …)
3338 VIEW (cr=16558 pr=0 pw=0 time=559922 us cost=3110 size=24 card=1)
3338 FILTER (cr=16558 pr=0 pw=0 time=558637 us)
3338 TABLE ACCESS BY INDEX ROWID TB_G30 (cr=16558 pr=0 …)
6740 NESTED LOOPS (cr=13616 pr=0 pw=0 time=2503993 us …)
3338 HASH JOIN (cr=6680 pr=0 pw=0 time=542982 us …)
6516 INDEX RANGE SCAN IX_TB_G10_03 (cr=55 pr=0 …)
1013506 PARTITION RANGE ALL PARTITION:1 8 (cr=6625 pr=0 …)
1013506 PARTITION RANGE SINGLE PARTITION:KEY KEY (cr=6625 pr=0 …)
1013506 INDEX RANGE SCAN IX_TB_M02_01 PARTITION:(cr=6625 pr=0 …)
3401 INDEX RANGE SCAN IX_TB_G30_03 (cr=6936 pr=0 …)
[改善案導き出し]でUNION-ALL構文で再作成したSQLを見れば、分離したSQLをもう一度インライン ビューで包んだ後インライン ビュー外で変数値をチェックする方法でSQLを作成しました。 その理由は以下のような方式でSQLを作成する場合、11gで該当SQLに対して実行計画を分離する時JOIN FACTORIZATIONによって予期できない性能問題が発生する場合があるためです。
SELECT /*+ LEADING(C) USE_NL(C A B)index(C PK_TB_G30) */
a.*,
b.*
FROM tb_m02 a,
tb_g10 b,
tb_g30 c
WHERE :b67 = :b68
…(省略)
AND c.sn_no BETWEEN :b67 AND :b68
UNION ALL
SELECT /*+ LEADING(B A) USE_HASH(A) USE_NL(C) INDEX(B IX_TB_G10_03) */
a.*, b.*
FROM tb_m02 a,
tb_g10 b,
tb_g30 c
WHERE :b67 <> :b68
…(省略)
AND c.sn_no BETWEEN :b67 AND :b68
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.07 0.08 0 0 0 0
Fetch 224 36.22 173.54 79445 1365647 0 3338
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 226 36.29 173.62 79445 1365647 0 3338
Rows Row Source Operation
---------- ---------------------------------------------------
3338 TABLE ACCESS BY INDEX ROWID TB_G10 (cr=1365647 pr=79445 …)
2027013 NESTED LOOPS (cr=352128 pr=59243 pw=0 time=2067498 us …)
1013506 VIEW VW_JF_SET$5DA70CA4 (cr=289227 pr=59205 pw=0 time=91154717 us …)
1013506 UNION-ALL (cr=289227 pr=59205 pw=0 time=90640603 us)
0 FILTER (cr=0 pr=0 pw=0 time=2 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=9 size=73 card=1)
0 TABLE ACCESS BY INDEX ROWID TB_G30 (cr=0 pr=0 pw=0 time=0 us …)
0 INDEX RANGE SCAN PK_TB_G30 (cr=0 pr=0 pw=0 time=0 us …)
0 PARTITION RANGE ITERATOR PARTITION:KEY KEY (cr=0 pr=0 …)
0 PARTITION RANGE SINGLE PARTITION:KEY KEY (cr=0 pr=0 pw=0 time=0 …)
0 INDEX UNIQUE SCAN PK_TB_M02 PARTITION:(cr=0 pr=0 …)
1013506 FILTER (cr=289227 pr=59205 pw=0 time=89716685 us)
1013506 TABLE ACCESS BY INDEX ROWID TB_G30 (cr=289227 pr=59205 …)
2039659 NESTED LOOPS (cr=191923 pr=19014 pw=0 time=15989900 us …)
1013506 PARTITION RANGE ALL PARTITION:1 8 (cr=6633 pr=5392 …)
1013506 PARTITION RANGE SINGLE PARTITION:KEY KEY (cr=6633 pr=5392 …)
1013506 INDEX RANGE SCAN IX_TB_M02_01 PARTITION:(cr=6633 pr=5392 …)
1026152 INDEX RANGE SCAN IX_TB_G30_03 (cr=185290 pr=13622 …)
1013506 INDEX UNIQUE SCAN PK_TB_G10 (cr=62901 pr=38 pw=0 time=2445978 …)
UNION ALLで実行計画を分離した後、照会パターン[1]の値に対しては、効率的な実行になりました。 しかし照会パターン[2]でUNION ALLの下の部分を実行する時、TB_G10,TB_M02頭テーブルをHash Joinで処理した後、TB_G30テーブルにアクセスしてこそ効率的な実行になるのですが、意図した通りにはなりませんでした。
すなわち、TB_G10テーブルが持つ支社コード(BRCH_CD)条件で先にデータを減らすべきなのに、これをSQLの一番最後に実行することによって非効率が発生したのです。 このような現象はOptimizerが実行計画を樹立する時、性能を最適化するためにUNIONまたはUNION ALLを使うSQLの内容のうち共通で使うテーブルをビューの外で分離させて重複使用を避けるJOIN FACTORIZATIONを実行するためです。 意図はよかったのですが、かえって既存SQLより性能が低下してしまいました。 したがってJOIN FACTORIZATIONが実行されないように[改善案導き出し]と一緒にもう一度インライン ビューで包み込む方式でSQLを再作成しました
また他の方法で以下のSQLのように11gで提供するJOIN FACTORIZATIONを制御するヒントを使ってSQLを実行したのです。
SELECT /*+ LEADING(C) USE_NL(C A B)index(C PK_TB_G30) */
OPT_PARAM('_optimizer_join_factorization','FALSE') */
a.*,
b.*
FROM tb_m02 a ,
tb_g10 b ,
tb_g30 c
WHERE :b67 = :b68
…(省略)
AND c.sn_no BETWEEN :b67 AND :b68
UNION ALL
SELECT /*+ LEADING(B A) USE_HASH(A) USE_NL(C) INDEX(B IX_TB_G10_03) */
a.*,
b.*
FROM tb_m02 a ,
tb_g10 b ,
tb_g30 c
WHERE :b67 <> :b68
…(省略)
AND c.sn_no BETWEEN :b67 AND :b68 ;
今回は色々な照会パターンを持つSQLが一つの実行計画で固定される場合、特定照会パターンで実行時発生する性能問題に対して調べてみました。 これと関連した性能問題に対してLIKE ‘%’やNVLを含むSQLが各照会パターンに合うように効率的な実行になるべくSQLを再作成して解決する事例と、Where節条件のうち変数値の範囲により実行計画を分離した事例を調べてみました。
実務では前で説明したSQLとはまた他の方式で性能問題を起こルはずです。 しかしSQLの業務的な性格を把握してSQLが持つ照会パターンを明確に分析するならば、性能問題に対する改善方法を導き出すのは難しくないでしょう。
今回は、ここまでになります。いかがでしたでしょうか？直からは、「ROWNUMの理解と関連SQL性能問題の理解」と題しまして、６回シリーズでお送りいたします。ご期待ください。では、See you ^^
