Batching NLJ最適化と整列

Batching NLJ最適化って聞いたこたあります？Batching NLJとはOracle 11gで紹介されたNested Loops Joinの最適化技法であります。例えば、次の２つの実行計画を見てください。

-- Oracle 10g
------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|*  1 |  COUNT STOPKEY                 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T2    |  <-- Here
|   3 |    NESTED LOOPS                |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |
|*  5 |      INDEX RANGE SCAN          | T1_N1 |
|*  6 |     INDEX RANGE SCAN           | T2_N1 |
------------------------------------------------

-- Oracle 11g
------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|*  1 |  COUNT STOPKEY                 |       |
|   2 |   NESTED LOOPS                 |       |
|   3 |    NESTED LOOPS                |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |
|*  5 |      INDEX RANGE SCAN          | T1_N1 |
|*  6 |     INDEX RANGE SCAN           | T2_N1 |
|   7 |    TABLE ACCESS BY INDEX ROWID | T2    |   <-- And here
------------------------------------------------

TABLE ACCESS BY INDEX ROWID (T2)のオペレーションの位置の違いに気付いたんでしょうか。これがOracle 11gのNested Loops Join最適化(Batching NLJ)の現れ方です。Batching NLJによって、論理読み込みが減り、性能が効率的になるはずです。

さて、ほんの数日前、Batching NLJ関連の変な整列問題に出会いました。下記は再現のできるテストケースです。ORDER BYのオーバーヘッドなく整列をするために、索引T1_N1を利用していることに注意してください。

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 <= 1000;

create table t2
as
select 1001-level as c1, level as c2, rpad('x',1000) as dummy
from dual
connect by level <= 100;

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');

explain plan for
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 <= 20
;

select * from table(dbms_xplan.display);

-- Read from the disk
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 <= 20
) where rnum >= 15
;

-- Read from the 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 <= 20
) where rnum >= 15
;

-- Disable exceptions for buffer cache misses
alter session set "_nlj_batching_misses_enabled" = 0;

-- Read from the disk
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 <= 20
) where rnum >= 15
;

-- Read from the 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 <= 20
) where rnum >= 15
;

時間の節約のために、コメントが付いた簡単な結果を見ましょう。

-- Case1 : batching NLJ enabled
-- when the query reads from the disk
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        980        980         21  <-- Why 980 here?
        18        968        968         33
        19        972        972         29
        20        976        976         25

-- when the query reads from the buffer cache
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        968        968         33
        18        972        972         29
        19        976        976         25
        20        980        980         21

-- Case 2: batching NLJ disabled
-- when the query reads from the disk
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        968        968         33
        18        972        972         29
        19        976        976         25
        20        980        980         21

-- when the query reads from the buffer cache
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        968        968         33
        18        972        972         29
        19        976        976         25
        20        980        980         21

いかがでしょう。整列の順序に変わったこと

これは次のように解説できます。
「新しいNested Loops Joinの最適化コードは行が整列されたまま返されることを保障しない。特にディスクからデータを読み出している際には」

これはページネーションクエリ(Pagination Query)を索引とNested Loops Joinを利用して作成したい時に制約になる可能性があると思います。では、この制約がバグというのではありません。オラクルで整列順序を保障する雄一な方法はORDER BY句を与えることだけからです。

この制約を抜けていくためには次の方法で1つくらいを進めます。

• 隠しパラメーター_nlj_batching_misses_enabledを0にセット
  
• 隠しパラメーター_nlj_batching_enabledを0にセット
  
• NO_NLJ_BATCHING(T2)ヒントの追加