태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'Batching NLJ'에 해당되는 글 2건

  1. 2010.10.11 Batching NLJ에 대한 오라클 매뉴얼의 설명
  2. 2010.08.17 Oracle 11g의 Batching NLJ와 정렬 문제 (5)

Batching NLJ에 대한 오라클 매뉴얼의 설명

오라클/SQL 튜닝 2010.10.11 18:57
Oracle 11g의 Batching NLJ에 의해 물리 I/O(Physical I/O)가 발생할 때 정렬이 깨어지는 것처럼 보이는 현상을 이 포스트에서 소개한 바 있습니다.

오늘 우연히 오라클 매뉴얼(Performance Tuning Guide)에서 Oracle 11g의 Batching NLJ에 대해 설명하고 있는 부분을 발견했습니다.

단순하면서도 명확하게 설명되어 있습니다. 이 설명과 더불어 제가 설명한 힌트와 히든 파라미터를 같이 이해하면 운영 시스템에서 문제가 발생했을 때 효과적으로 대처하실 수 있을 거 같습니다.
저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Batching NLJ에 대한 오라클 매뉴얼의 설명  (0) 2010.10.11
PLAN_HASH_VALUE  (0) 2010.10.06
tags : Batching NLJ
Trackback 0 : Comment 0

Write a comment


Oracle 11g의 Batching NLJ와 정렬 문제

오라클 2010.08.17 13:48
Oracle 11g에서 Nested Loops Join을 Batch로 처리하는 최적화(이것을 Batching NLJ라고 부르겠습니다)가 추가되었습니다. 아래 실행 계획을 보시면...
-- 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)의 위치가 10g에서 11g에서 전혀 다른 것을 알 수 있습니다. Oracle 11g에 추가된 Batching NLJ때문입니다. 앞으로 Nested Loops Join의 성능이 더 좋아질 것으로 기대할 수 있습니다.

그런데, 며칠 전 Oracle 11g에서 정렬이 깨어진다는 문제를 보고 받았습니다. 이 문제를 간단한 테스트 케이스로 만들어 보았습니다. 인덱스 T1_N1과 Nested Loops Join을 이용해서 ORDER BY 없이 Pagination Query를 구현하고 있는 것에 주의하시면 됩니다.
(이 테스트 케이스는 Windows 버전 오라클 11.1.0.6과 11.2.0.1에서 재현됩니다. 테이블스페이스는 USERS입니다. 다른 버전과 다른 OS, 다른 테이블스페이스에서는 재현되지 않을수도 있습니다)

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
;
테스트 시간이 아까우신 분들을 위해 결과를 보겠습니다. Batching NLJ를 사용할 경우 디스크에서 데이터를 읽는 경우와 버퍼 캐시에서 읽는 경우 정렬 결과가 다르다는 것에 주목하시기 바랍니다.
-- 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 - 정확하게 말하면 Disk에서 읽을 때의 예외를 사용하지 않으면
-- 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의 새로운 최적화 코드는 외부 테이블(Outer Table, 선행 테이블)에서 데이터가 나오는 순서대로 조인 결과가 나온다는 것을 보장하지 않는다. 특히 디스크에서 데이터를 읽을 때는"라는 결론을 내릴 수 있겠습니다.

이러한 제약은(만일 위의 결론이 맞다면) ORDER BY의 부하없이 인덱스와 Nested Loops Join을 이용해서 Pagination Query를 구현하고자 하는 경우에 문제가 될 수 있겠습니다. 하지만 이것이 버그라고 하기는 힘들겠습니다. 오라클에서 정렬 결과를 보장하는 유일한 방법은 ORDER BY 구문을 사용하는 것입니다. 그 외에는 어떤 이유로도 오라클은 정렬 순서를 보장하지 않습니다.

위의 테스트 케이스에서 다음과 같은 세가지 방법 중 하나로 정렬 순서의 일관성을 맞출 수 있습니다.

  • _NLJ_BATCHING_MISSES_ENABLED 파라미터의 값으로 0으로 변경 (아마 가장 좋은 방법)
  • _NLJ_BATCHING_ENABLED 파라미터의 값을 0으로 변경
  • NO_NLJ_BATCHING(t2) 힌트를 적용
히든 파라미터의 변경은 반드시 오라클 지원의 검증 하에 수행해야 함을 다시 한번 주의시켜 드립니다. 또는, MOS의 공식 Workaround로 지정되어 있어야 합니다.

Batching NLJ에 대해서는 좀 더 연구와 테스트가 필요해보입니다. 특히 정확한 작동 방식과 함정에 대한 연구가 필요해보이네요.

저작자 표시
신고
Trackback 0 : Comments 5
  1. 아삽 2010.08.18 10:33 신고 Modify/Delete Reply

    좋은 정보 잘 배우고 갑니다. 감사합니다.^^

  2. 행인 2010.08.19 12:51 신고 Modify/Delete Reply

    항상 좋은 글 잘보고 있습니다.

    그런데 읽고 나서 생각해보니 이상한 점이 있었습니다.
    hash group by 가 10g에서 처음 나타났을 때에는 전세계가 시끄러웠습니다.
    Batching NLJ도 비슷한 현상을 유발하는데, 11g가 출시된지 2년이 넘은 현재까지 전세계적으로 문제삼는 경우는 거의 찾아보기가 힘들었습니다.

    왜 그랬을까요? 정말 이상했습니다.
    제가 생각해본 이유는 다음과 같습니다.

    1. 실제로 문제되는 경우가 매우 드믈다.
    group by는 매우 포괄적으로 모든 업무에서 사용됩니다.
    반면에 Batching NLJ 의 정렬순서가 문제가 되는 경우는 아래와 같은 상황일 때 뿐입니다.
    - NL join에서 inner table에 액세스하는 인덱스를 이용해 order by를 제거

    페이징처리를 위해 사용되는 경우가 대부분이겠지요.
    튜닝해보신분들은 아시겠지만 대부분의 페이징처리를 위한 SQL은 전체 검색결과를 얻고나서 필터링하는 방식으로 작성됩니다. 부분범위처리를 고려해서 정교하게 작성된 것은 넉넉히 잡아도 전체 페이징처리의 10% 이내일 것입니다.

    또한 인덱스를 이용해 부분범위처리로 튜닝된 경우라도 outer table의 인덱스에서 정렬순서가 결정되는 SQL이라면 문제가 되지않습니다.
    1 (Master정보) : M (Detail정보) 관계에서 M쪽의 테이블이 outer table이 된 경우겠죠. 제 경험으로는 이런 경우가 많습니다.
    왜냐하면 주요 검색조건이 가장 많이 포함되어 있기때문입니다.


    2. 전체 결과에 대한 영향범위가 좁다.
    hash group by가 적용되면 전체 겁색결과의 정렬순서가 영향을 받게됩니다.
    하지만 Batching NLJ의 경우에는 Fetch Size 범위내에서만 영향을 받는 것으로 보입니다.
    때문에 실제로 발생하는 경우에도 사용자들이 모르거나 무시하고 사용할 수도 있어 보입니다.

    물론 상기의 내용이 Batching NLJ 의 정렬순서 문제가 별거 아니라는 얘기는 아닙니다.
    단지 너무 겁먹고 히든 파라메더로 유용한 신기능을 막아버리는 사태가 사방에서 발생하지 않을까하는 노파심에서 몇자 적어봅니다.

    일반적인 가이드라인은 다음과 같이 정리하면 될 것 같습니다.

    1. 정렬이 필요한 SQL은 order by를 적용하는 것을 원칙으로 하며, Batching NLJ 을 사용한다.
    2. 부분범위처리를 위해 order by를 제거하는 튜닝은 오라클의 정렬 메카니즘을 정확히 이해하고 있는 전문가만 한다.
    사실 Batching NLJ 뿐만 아니라 Partition Table에서도 Local Index를 이용한 order by 대치 시에도
    2개 이상의 partition에서 데이터를 가져오게 되면 정렬문제가 발생합니다.

    * 이전 버전에서 upgrade하는 경우에는 부분범위로 튜닝된 SQL이 파악되지 않을 때는 결정하기가 쉽지 않겠네요. ㅎㅎ

    • 욱짜 2010.08.19 13:35 신고 Modify/Delete

      Batching NLJ에 의해서 정렬이 바뀌는 문제는 Disk I/O가 수반될 때만 생기는 것으로 생각됩니다. 위의 테스트 케이스를 보시면 알겠지만 Outer Table에서 인덱스를 이용해서 정렬을 유도하더라도 Dis k I/O가 발생하면 정렬 결과가 바뀌어 버립니다.

      이와 비슷한 버그로 MOS Note #6841549가 있는데, 이것도 참조하시면 도움 되겠습니다.

  3. hidden 2010.08.19 23:03 신고 Modify/Delete Reply

    히든을 Batching NLJ 를 막으려는 글은 매우 안좋아 보이는 군요..
    행인님의 글에 동감 합니다.

    • 욱짜 2010.08.20 12:49 신고 Modify/Delete

      오해의 소지가 있는거 같습니다.

      히든 파라미터의 변경은 오라클 서포트의 확인을 거친 후에 수행해야 하는 것은 주지의 사실이죠. 이 문제를 논의해오신 분도 오라클 서포트에 공식적으로 문의해둔 상태입니다.

      위의 사실을 포스트에 확실히 명기하지 않은 것은 확실히 문제의 소지가 있군요. 주의 사항을 확실히 명기할 필요가 있겠습니다.

Write a comment

티스토리 툴바