Monday, August 16, 2010

Batching NLJ最適化と整列

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

-- 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)ヒントの追加

No comments:

Post a Comment