2022.02.25
NLJ BATCHと部分範囲処理
NLJ BATCHと部分範囲処理
(株)エクセンコンサルティング本部/DBコンサルティングチーム
概要
Oracleには、新しいバージョンがリリースされるたびにさらにアップグレードされた機能が追加されます。 これらの機能はユーザーに便利さを提供することはもちろん、既存の機能がパフォーマンス的にアップグレードされ、より強力になることもあります。
しかし、時には新しく追加された機能のために、ユーザーが大きな混乱を経験することもあります。 その代表的な例としては、GROUP BY が SORT GROUP BY 処理方式により HASH GROUP BY で処理が可能になった事例ではないかと考えられます。
HASH GROUP BYはソート作業が消え、従来のSORT GROUP BYに比べて優れた性能を示すことで当時の画期的な技術となりました。 しかし、開発時にSORT GROUP BYが自動的にソートされるという点を利用し、別途ORDER BY句を指定しなかったSQLはGROUP BY句に指定された列順にソートされず、意図した結果と他のデータを抽出することで多くの開発者を混乱させました。
この悪夢のような問題は、Oracle 11gでNLJ BATCHによって再び再現されることになりました。
NLJ BATCHという用語は正式な名称ではなく、ヒントがNLJ_BATCHINGという点を考慮して筆者が命したものであることを予めお知らせしておきます。 この文書は、NLJ_BATCHは何なのか、どのような利点があるのか、そして11g以前のバージョンで部分範囲処理をしたSQLの結果が誤って抽出されて生じる混乱をどのように解決できるかを扱うために作成されました。
NLJ BATCHとは何ですか?
内容を説明する前に、11gで新しくリリースされたNLJ BATCH機能が何であるかを見てみましょう。
11g以下で、Nested Loop JoinはDisk I / Oが発生したときに毎回Sinlge Block I / Oを実行しました。 これは、Nested Loop Joinを実行する際にDisk I/Oを多く伴う場合、SQLの実行速度を著しく低下させる原因となっていました。
したがって 11g ではこれらの欠点を補うため、DISK I/O 発生時のたびに Single Blokc I/O をせず、DISK I/O が必要なブロックを集めて、一度の I/O CALL 時に複数の Block を読み込む NLJ_BATCH 機能を 追加しました。 これにより、Nested Loop Join時に発生するRandom I/Oへの負荷を軽減し、応答速度は改善されましたが、以前のOracle Versionに比べてデータがうまく抽出されないクリティカルな問題も伴うことになったのです。
11G以前には、NESTED LOOP JOINが行われた場合、先行テーブルから抽出した順序を絶対的に保証することができ、これを利用して部分範囲処理を実行していました。 しかし、NLJ BATCHの登場により、もはや順序が保証されなくなり、これによりOracle 11g以前に使用していた部分範囲処理を実施するSQLが正確なデータを抽出できなくなったのです。 この問題は、データ整合性を損なう可能性があるため、非常に深刻な問題でした。
私は、11gで誤った結果を抽出する問題を解決するために、多くのサイトが11gのインストール後にオンラインプログラムがNL BATCHを実行できないようにパラメータ “_NLJ_BATCHING_MISSES_ENABLED”を0に変更するか、部分範囲処理SQL文のソート順序 を保証するためにNO_NLJ_BATCHINGヒントを使用するケースを見ました。 当時筆者もやはり上の二つの方法が最善だと思いました。
しかし、パラメータを変えるという意味は、新しく出てきたNLJ BATCH機能を全く使わないという話なのです。 NLJ BATCHはNested Loop Joinの性能を最大化させる方法ですが、これをまったく使用しないというのは、「小さいことに気にして実行できないのか」という言葉と変わらない。
したがって、全体としてNLJ_BATCH機能を使用しないよりも、必ずしも使用してはならない対象を選択的に指定することが望ましいのです。 NLJ BATCHとは何か、またどのような状況で動作するのかを、
よく理解すれば、問題は思ったよりも簡単に解決できます。 したがって、今からNLJ BATCHの特性と特徴の詳細を学びましょう。
NLJ BATCHの動作原理
結論から言えば、11gでNLJ BATCHのため、常に部分範囲処理結果が誤って抽出されるわけではないのです。
すべてのデータがbuffer cacheに常駐している場合は、実際の物理I / Oが発生せず、意図したデータを抽出できます。 百聞は一にしかずなので、テストを通じて調べてみましょう。
テストスクリプト
drop table t1
drop table t2
create table t1 as
select 1 as c1, mod(level, 4) as c2, level as c3, level as c4, rpad(‘x’,1000) as dummy from dual
connect by level <= 1000000;
create table t2 as
select mod(1000000-level, 1000000) as c1, level as c2, rpad(‘x’,1000) as dummy from dual
connect by level <= 1000000;
create index t1_n1 on t1(c1, c2, c3);
create index t2_n1 on t2(c1);
exec dbms_stats.gather_table_stats(user, ‘t1’);
exec dbms_stats.gather_table_stats(user, ‘t2’);
CASE1 Buffer Cacheにすべてのブロックが存在する場合
まず、buffer cache にすべてのブロックが常駐する場合について調べてみましょう。
まず、ブロック2つ常駐するように、同じSQL文を一度事前に実行した後、再実行した結果をしまします。
SELECT * FROM (
SELECT /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ ROWNUM AS rnum ,
t2.c1 ,
t1.c4 , t2.c2
FROM t1 ,
t2
WHERE t1.c3 = t2.c1 AND t1.c1 = 1
AND t1.c2 = 0
AND ROWNUM <= 30000
)
WHERE rnum >= 28000
AND rnum <28010
RNUM | C1 | C4 | C2 |
———- | ———- | ———- | ———- |
28000 | 112000 | 112000 | 888000 |
28001 | 112004 | 112004 | 887996 |
28002 | 112008 | 112008 | 887992 |
28003 | 112012 | 112012 | 887988 |
28004 | 112016 | 112016 | 887984 |
28005 | 112020 | 112020 | 887980 |
28006 | 112024 | 112024 | 887976 |
28007 | 112028 | 112028 | 887972 |
28008 | 112032 | 112032 | 887968 |
28009 | 112036 | 112036 | 887964 |
結果を確認してみると、すべての Block が buffer Cache に常駐する場合、 NLJ_BATCH で実行されます。
C1値が順に並べて抽出されていることが分かる。
CASE2 Buffer Cache をフラッシュして Disk I/O を引き起こす場合
2番目のテストはBUFFER CACHEをFLUSHし、Physical Readsを発生させた場合です。
alter system flush buffer_cache;
SELECT * FROM (
SELECT /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ ROWNUM AS rnum ,
t2.c1 ,
t1.c4 , t2.c2
FROM t1 ,
t2
WHERE t1.c3 = t2.c1 AND t1.c1 = 1
AND t1.c2 = 0
AND ROWNUM <= 30000
)
WHERE rnum >= 28000
AND rnum <28010
RNUM | C1 | C4 | C2 |
———- | ———- | ———- | ———- |
28000 | 111996 | 111996 | 888004 |
28001 | 112000 | 112000 | 888000 |
28002 | 112004 | 112004 | 887996 |
28003 | 112008 | 112008 | 887992 |
28004 | 112012 | 112012 | 887988 |
28005 | 112020 | 112020 | 887980 |
28006 | 112096 | 112096 | 887904 |
28007 | 112024 | 112024 | 887976 |
28008 | 112028 | 112028 | 887972 |
28009 | 112032 | 112032 | 887968 |
<先に Buffer Cache に Block が常駐した場合の結果>
RNUM C1 C4 C2
———- ———- ———- ———-
28000 | 112000 | 112000 | 888000 |
28001 | 112004 | 112004 | 887996 |
28002 | 112008 | 112008 | 887992 |
28003 | 112012 | 112012 | 887988 |
28004 | 112016 | 112016 | 887984 |
28005 | 112020 | 112020 | 887980 |
28006 | 112024 | 112024 | 887976 |
28007 | 112028 | 112028 | 887972 |
28008 | 112032 | 112032 | 887968 |
28009 | 112036 | 112036 | 887964 |
まずRNUM 28005~28007までのデータを確認してみましょう。 Buffer Cache を Flush して、DISKI / Oが伴うと、C1の値は112020から112096に急激に増加し、再び112024に落ちたことがわかります。 つまり、C1値のソートはもはや保証されていません。 さらに、以前にBuffer CacheにすべてのBlockが常駐したときと比較すると、データ抽出結果は非常に異なることがわかります。
これまでの結果を総合してみると、NLJ_BATCHはDISK I/Oが発生するかどうかによって敏感に反応することがわかります。 実際、NLJ BATCH は先行テーブルに DISK I/O が発生するとすぐに DISK I/O を実行せず、一定量の I/O 操作が集まると、一度の I/O CALL で複数のブロックを読み込みます。
つまり、NLJ BATCHはNested Loop Join時のSingle Block I/Oによる速度低下を改善することができるのですが、I/Oを順次処理しないため、Indexを利用したアライメントは常に保証されないのです。
このようにNLJ BATCHを使用する場合、既存の部分範囲処理を行っていたSQLにはデータが不正確になる可能性があるという問題があるので、NLJ BATCHを使用する場合、実行速度の面で大きな利点がなければなりません。 もしそうなら、NLJ BATCHを使用するときにどれだけパフォーマンスが向上するかをテストしてみましょう。
これからはデータの抽出結果とは無関係にNLJ_BATCHの効率性についてテストをしてみましょう。
CASE3 NLJ_BATCH を使用していない場合
alter system flush buffer_cache
select * from (
select /*+ no_nlj_batching (T2) leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
rownum as rnum, t2.c1,
t1.c4, t2.c2
from t1, t2 where
t1.c3 = t2.c1
and t1.c1 = 1 and t1.c2 = 0
and rownum <= 30000
) where rnum >= 28000 and rnum <28010
平均実行時間 : 3.06秒
CASE4 NLJ_BATCHを使用した場合
alter system flush buffer_cache
select * from (
select /*+ nlj_batching (T2) leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
rownum as rnum, t2.c1,
t1.c4, t2.c2
from t1, t2 where
t1.c3 = t2.c1
and t1.c1 = 1 and t1.c2 = 0
and rownum <= 30000
) where rnum >= 28000 and rnum <28010
平均実行時間 : 1.93秒
以下の表は、NLJ BATCHを使用する場合と使用しない場合を10回ずつ行った結果です。
試行回数 | NLJ BATCH(SEC) | NO NLJ BATCH(SEC) |
1回目 | 1.9 | 2.75 |
2回目 | 1.97 | 2.7 |
3回目 | 1.89 | 2.87 |
4回目 | 1.98 | 3.14 |
5回目 | 1.89 | 3.12 |
6回目 | 1.9 | 3.14 |
7回目 | 1.9 | 3.21 |
8回目 | 1.93 | 3.18 |
9回目 | 1.92 | 3.21 |
10回目 | 1.97 | 3.31 |
平均 | 1.925 | 3.063 |
上の表を見ると、Buffer CacheをFlushした状態で約30,000件のデータをNested Loop Joinを行う場合、NLJ BATCHを使用した場合がそうでない場合より約1秒ほどの速度が改善された。 大容量のデータをNested Loop Joinで行う場合、その効果はさらに大きいことが予想されます。 これがNLJ BATCHが持つ魅力であり、筆者がこの文を書くことになった理由です。 部分範囲処理時に誤ったデータが出ることを懸念して、全面的にに使用を禁止しないでないでください。
NLJ BATCHモニタリング方法
一つ注意することがあります。 NLJ_BATCHを監視しようとしてTRACEを実行するか、XPLANで監視するためにGATHER_STATISTICSヒントを実行すると、NLJ_BATCH機能は使用されないという点です。
では、どのようなモニタリングが可能なのでしょうか。 v $ sesstat、v $ session_eventビューを通じてモニタリングが可能です。
NLJ_BATCH が実行される場合、V$SESSTAT の Batched IO (bound) vector count 指標が増加し、バッチ I/O が起こる場合、db file parallel read 待機イベントが発生するため、V$SESSION_EVENT を監視すればよいのです。
それでは、直接監視を行ってみましょう。
CASE1 NLJ_BATCHを使用した場合
alter system flush buffer_cache
select * from (
select /*+ nlj_batching (T2) leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
rownum as rnum, t2.c1,
t1.c4, t2.c2
from t1, t2 where
t1.c3 = t2.c1
and t1.c1 = 1 and t1.c2 = 0
and rownum <= 30000
) where rnum >= 28000 and rnum <28010
select * from v$sesstat a, v$statname b where sid =133 and a.STATISTIC#=b.STATISTIC# and a.STATISTIC#=249
order by 3 desc
SID STATISTIC# VALUE STATISTIC# NAME STAT_ID
—- ———- ———- ———- ————————————- ———–
70 249 1217 249 Batched IO (bound) vector count 2669900039
モニタリング結果を見ると、Batched IO(bound)Vector count指標が増加したことから、NL BATCHが使用されたことがわかります。 このときV $ SESSION_EVENTを照会してみると、以下のようにdb file parallel readが発生しています。
select sid, event, total_waits, time_waited_micro, wait_class from v$session_event where sid =’133′
SID EVENT TOTAL_WAITS TIME_WAITED_MICRO WAIT_CLASS
———- ————————— ———– —————– ——————-
70 Disk file operations I/O | 3 | 1641 | User I/O |
70 log file sync | 1 | 330 | Commit |
70 db file sequential read | 2246 | 348896 | User I/O |
70 db file scattered read | 3424 | 1427445 | User I/O |
70 db file parallel read | 6 | 3383 | User I/O |
70 SQL*Net message to client | 39 | 33 | Network |
70 SQL*Net message from client | 38 | 32065340 | Idle |
70 events in waitclass Other | 6 | 24545 | Other |
CASE2 NLJ_BATCH を使用していない場合
alter system flush buffer_cache
select * from (
select /*+ no_nlj_batching (T2) leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
rownum as rnum, t2.c1,
t1.c4, t2.c2
from t1, t2 where
t1.c3 = t2.c1
and t1.c1 = 1 and t1.c2 = 0
and rownum <= 30000
) where rnum >= 28000 and rnum <28010
select * from v$sesstat a, v$statname b where sid =133 and a.STATISTIC#=b.STATISTIC# and a.STATISTIC#=249
order by 3 desc
SID STATISTIC# VALUE STATISTIC# NAME STAT_ID
——– ——— ——– ———- ———————————– ———-
191 249 0 249 Batched IO (bound) vector count 2669900039
Value値が0なので、NLJ BATCHが使用されていないことがわかります。
NLJ BATCHと部分範囲処理SQLとソリューション
ここで、11g以降のNESTED LOOP JOINの特性を利用して、部分範囲処理をしたSQLに対して正常にデータをインポートするための解決策について議論してみましょう。
前述のように、_NLJ_BATCHING_MISSES_ENABLEDパラメータを調整したり、
NO_NLJ_BATCHINGヒントを使用すると、データが誤って抽出される問題を解決できます。
2つの方法のうち、パラメータを変更することはNLJ_BATCH機能を完全に使用しないため、多くのスレッドがある可能性があります。 したがって、部分範囲処理を実行するSQLを見つけてNO_NLJ_BATCHINGヒントを追加することが最善の解決策であると考えられます。
しかし、NO_NLJ_BATCHINGヒントを使用しても、INDEXがUNUSEBLE状態になるか、条件が変わってもはやINDEXを使用したソートが不可能な場合、データが誤って抽出される現象は依然として発生するはずです。
最良の方法はSQLの作成が複雑になりますが、ORDER BYを指定することです。 INLINE VIEW内にORDER BY句がありますが、INDEX列の順序とORER BY順序のみが一致する場合、Oracleは部分範囲を処理します。 しかし、INDEX が UNUSEBLE になかったり、SQL が変更されて ORDER BY 句と INDEX が一致したら、そのときにソートを実行します。 したがって、どのような状況が変更されても、常に正確なデータを取得できます。 さらに、ORDER BYが指定されている場合は、その順序を保証するためにNLJ_BATCH機能が有効になっていないため、正確なデータを取得するできるのです。
11gで部分範囲を処理するときに守る必要があるSQLの書き方
SELECT * FROM (
SELECT ROWNUM rnum ,
a.*
FROM (
SELECT /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */ t2.c1 ,
t1.c4 , t2.c2
FROM t1 ,
t2
WHERE t1.c3 = t2.c1 AND t1.c1 = 1
AND t1.c2 = 0 ORDER BY c3 ASC
) A WHERE ROWNUM < 30000
)
WHERE rnum >= 28000
AND rnum <28010
RNUM | C1 | C4 | C2 |
———- | ———- | ———- | ———- |
28000 | 112000 | 112000 | 888000 |
28001 | 112004 | 112004 | 887996 |
28002 | 112008 | 112008 | 887992 |
28003 | 112012 | 112012 | 887988 |
28004 | 112016 | 112016 | 887984 |
28005 | 112020 | 112020 | 887980 |
28006 | 112024 | 112024 | 887976 |
28007 | 112028 | 112028 | 887972 |
28008 | 112032 | 112032 | 887968 |
28009 | 112036 | 112036 | 887964 |
実行計画
SELECT STATEMENT ALL_ROWS-Cost : 77372
VIEW SCOTT.(1) (“RNUM”<28010 AND “RNUM”>=28000) COUNT STOPKEY (ROWNUM<30000)
VIEW SCOTT.(2)
NESTED LOOPS NESTED LOOPS
TABLE ACCESS BY INDEX ROWID SCOTT.T1(3) Analyzed : 20130915
INDEX RANGE SCAN SCOTT.T1_N1 (C1,C2,C3) Analyzed : 20130915 (“T1”.”C1″=1 AND “T1”.”C2″=0)
INDEX RANGE SCAN SCOTT.T2_N1 (C1) Analyzed : 20130915 (“T1″.”C3″=”T2”.”C1″)
TABLE ACCESS BY INDEX ROWID SCOTT.T2(4) Analyzed : 20130915
上記のようにORDER BY句まで明示して部分範囲を処理すると、NO_NLJ_BATCHINGヒントはないのですが、正確なデータを抽出できるようになります。 これは、OracleがORDER BY句の存在でNL BATCHを実行した場合に結果の値が異なる可能性があることを認識し、VECTOR I / Oを実行せず、毎回SINGLE BLOCK I / Oを実行したことが期待できます。
11gから、部分範囲を処理するときにORDER BY句を指定することがどれほど重要かがわかります。 多くの書籍が部分範囲処理を記述するとき、ORDER BY句が省略された形で説明しており、またそれをそのまま使用して、IndexがUnusebleになったり、11gにアップグレードされた後にデータが誤って抽出され、大きな混乱を経験しています。 Oracleでのソートを100%保証する方法は、Order by句を使用する以外にはありません。 したがって、SQLが長くなって少し複雑になっても、私が言及したようにSQLを作成すれば、いつでも正確なデータを抽出できることは勿論であり、ORDER BYが省略された全体範囲処理を行うNESTED LOOP JOINらはNLJ_BATCH機能を 積極的に活用することで性能がさらに向上すると考えられます。
結論
これまでNLJ BATCについて学んだ。 11gで部分範囲処理時にデータが誤って抽出されるという問題がありましたが、これはSQL作成が間違っていたために発生した問題です。
部分範囲処理を導きたい場合は、単にINDEX名とROWNUMだけを使用しないでください。 オラクル・オプティマイザは、ORDER BYに出会うと正確にソートされたデータを抽出するためにNLJ BATCHを使用しません。
NLJ BATCHを使用して応答速度を上げます。 つまり、根本的な問題は開発者とOracleのオプティマイザー間の解釈の違いにより、間違った結果がもたらされたと考えられます。 今後は部分範囲処理をする際に必ずOrder by句を明示して使用してください。