한 선지자에 의하여 9i/10g 그리고 11g 에서 Nested Loop Join 수행시 buffer pinning 효과에 의한 성능향상이 증명된바 있다.
물론 그글은 명불허전 이다.(해당글 링크:http://ukja.tistory.com/166)
   이미 밝혀진 원리에 대해서 증명이나 검증하는것은 더이상 과학이 아니다. 그리고 재미도 없다. 따라서 오늘은
buffer pinning 에 의한 성능개선이 아닌 또다른 성능개선에 대한 2가지 원리 대해서 연구해보자.

  이제부터 버젼별로 변경된 Nested Loop Join 의 실행계획과 그에 따르는 원리에 대하여 알아보려 한다. 모든 예제는 오라클 설치시 자동으로 설치되는 SH Schema의 customers 테이블과 sales 테이블을 이용한다.
먼저 인덱스를 하나 만든다.

drop index sh.sales_cust_ix;
create index sh.sales_cust_ix on sh.sales(cust_id);


 이어서 buffer cache 를 비우고 SQL 을 실행한다.
고객 테이블을 full scan 하고 sales 테이블과 Nested Loop Join 을 수행한다.

alter system flush buffer_cache;

select /*+ gather_plan_statistics */ count(*)
  from (select /*+ no_merge full(c) use_nl(c s) */
               s.cust_id,
               s.time_id,
               c.cust_year_of_birth
         from  sh.customers c,
               sh.sales s
         where c.cust_id  = s.cust_id  
           and c.cust_year_of_birth between 1960 and 1980
           and s.channel_id  between 2 and 3
           and s.prod_id < 18); 
 
select * from
table(dbms_xplan.display_cursor(null,null, 'allstats last -rows +outline -predicate' ));

Oracle 8i Plan --> just normal
------------------------------------------------
| Id  | Operation                              |
---------------------------------------------- |
|   1 |  SORT AGGREGATE                        |
|   2 |   VIEW                                 |
|   3 |     NESTED LOOPS                       |
|   4 |      TABLE ACCESS FULL                 |
|   5        TABLE ACCESS BY GLOBAL INDEX ROWID|
|   6 |        INDEX RANGE SCAN                |
------------------------------------------------

특별 할것 없는 전통적인 Nested Loop Join 이다.
이제 9i 및 10g 의 plan 을 보자.

9i & 10g Plan --> table prefetch
-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |               |      1 |00:00:05.67 |     245K|   1454 |
|   2 |   VIEW                               |               |   8269 |00:00:05.66 |     245K|   1454 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:05.62 |     245K|   1454 |
|   4 |     NESTED LOOPS                     |               |    327K|00:00:02.83 |   41304 |   1454 |
|   5 |      TABLE ACCESS FULL               | CUSTOMERS     |  20010 |00:00:00.12 |    1457 |   1454 |
|   6 |      INDEX RANGE SCAN                | SALES_CUST_IX |    307K|00:00:00.88 |   39847 |      0 |
-------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_PREFETCH(@"SEL$2" "S"@"SEL$2")
      END_OUTLINE_DATA
  */
 

Oracle 9i 에서 table prefetch 기능이 나오다.
  Inner(후행) 테이블의 위치가 Nested Loop Join 위로 올라가 버렸다. 그리고 오라클이 내부적으로
NLJ_PREFETCH 힌트를 사용하였다. 이것은 어떤 의미를 가지고 있을까?
이러한 현상에 대한 원리는 single block I/O request 에 의한 physical read 시 block 을 prefetch(미리 읽는 작업) 한다는데 있다.
여기서 physical read 란 buffer cache 에 데이터가 없어서 disk 에서 데이터를 read 하는것을 의미한다.
어차피 scan할 data 이므로 미리 엑세스할 물리적 주소를 여러개(운반단위) 모은다음 한번에 read 햐여 buffer cache 에 올리게 되는것이다. 여기서 주의할점은 multi block I/O 를 하는것이 아니라 single block I/O 여러개(운반단위만큼)가 동시에 진행된다는 것이다. 이것을 Vector IO 라고 부른다.(Batch IO 라고도 함)  바로 여기에 성능개선 효과가 있는것이다. 이기능에 의해서 rowid 에 의한 테이블 access 는 8i 에 비해서 상당한 개선 효과가 있는 것이다.(Operation ID 로는 3번이 여기 해당된다.)

이것을 증명하기 위해 v$sesstat 에서 SQL 수행전과 수행후의 value 증가분을 비교해보면 아래와 같다.

NAME                                           DIFF
---------------------------------------- ----------
undo change vector size                        2840
physical read IO requests                      3812
... 중간생략                                      
physical reads cache prefetch                  1344


위에서 보는것과 같이 table prefetch 가 발생하였다. 위의 테스트는 11g 에서 수행된것인데 9i 의 살행계획과 실행통계도 10g 와 대동소이 하다. 11g 에서 이전 버젼(9i/10g) 번젼의 plan 을 나타나게 하려면 NO_NLJ_BATCHING(테이블명) 힌트를 사용하면 된다.  9i 나 10g 에서의 후행 테이블 prefetch에 의한 성능 개선효과는 11g 에 와서야 완벽한 모습을 갖추게 된다.

11g Plan --> Index Vector I/O
------------------------------------------------------------------------------------------------------
|Id  | Operation                             | Name          | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------
|  1 |  SORT AGGREGATE                       |               |      1 |00:00:04.82 |     245K|   1454|
|  2 |   VIEW                                |               |   8269 |00:00:04.81 |     245K|   1454|
|  3 |    NESTED LOOPS                       |               |   8269 |00:00:04.79 |     245K|   1454|
|  4 |     NESTED LOOPS                      |               |    307K|00:00:01.56 |   41304 |   1454|
|  5 |      TABLE ACCESS FULL                | CUSTOMERS     |  20010 |00:00:00.08 |    1457 |   1454|
|  6 |      INDEX RANGE SCAN                 | SALES_CUST_IX |    307K|00:00:00.47 |   39847 |      0|
|  7 |     TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:01.93 |     203K|      0|
------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_BATCHING(@"SEL$2"
"S"@"SEL$2")
      END_OUTLINE_DATA
  */

궁하면 통한다.
이상하지 않은가? 테이블이 2개 인데 Nested Loop Join 이 하나가 아닌 2개가 되어버렸다. 또한 NLJ_PREFETCH 힌트가 사라지고 NLJ_BATCHING 힌트로 대체 되었다.
이러한 현상이 의미하는 바는 무엇일까?
9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
드디어 11g 에서 index scan 시 Vector IO 가 가능해졌다. 궁하면 통한다고 했던가? 오라클이 Nested Loop Join 에 대하여 지속적으로 개선해왔다는것을 알수있다.
참고로 NO_NLJ_BATCHING 힌트를 사용하면 9i/10g 의 Plan 으로 돌아가게 된다.

그러면 11g 의 버젼에서 v$sesstat 통계를 보자.

NAME                                           DIFF
---------------------------------------- ----------
Batched IO vector block count                  3758
Batched IO vector read count                     50
... 이후 생략

위에서 보듯이 Batched IO 란것이 생겼다.
Batched IO (혹은 Vector IO) 기능에 힘입어 table prefetch 에 이어서 11g 에서는 index scan 의 성능까지 향상되었다.

주의사항 : 위에서 수행한 모든 테스트는 Physical read 시에만 해당된다. 위의 예제 스크립트에 buffer cache 를 flush 한 이유도 여기에 있다.

결론:
  오라클 9i, 10g 및 11g 에서 개선된 Nested Loop Join 의 원리는 다음과 같다.
첫번째는 9I/10g 에서 후행 테이블의 TABLE ACCESS BY INDEX ROWID Operation 작업속도가 개선되었다는것과 두번째로 11g 에서 후행 테이블의 인덱스 scan 속도까지 획기적으로 개선되었다는 것이다. 이것은 table prefetch 기능과 Vector I/O 기능에 의해서 각각 구현 되었다. 이기능들과 별도로 이글의 서두에서 이야기한 buffer pinning 기능까지 덤으로 따라오게 되었다.

  앞으로 11g 를 사용시 과거처럼 Batch 용 SQL 에서 무조건 hash 조인을 남발하지 말았으면 한다.
조인건수가 많지 않고 후행 테이블에 적당한 인덱스가 있을 경우에 최소한 Nested Loop Join 과 성능비교를 해보아야 하지않을까?

<편집후기 : 위 테스트는 11g 에서 테스트 되었음을 다시한번 밝혀둡니다.>

Posted by extremedb

댓글을 달아 주세요

  1. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2009.05.09 18:14 신고  댓글주소  수정/삭제  댓글쓰기

    테스트하실 때 10g의 정확한 하위 버전이 어떻게 되나요? 그 버전부터 NLJ_PREFETCH Hint와 NO_NLJ_PREFETCH Hint가 추가되었군요!

    11g에서는 NLJ_BATCHING, NO_NLJ_BATCHING Hint가 추가된 것 같습니다. Nested Loop Join과 관련된 동작 방식을 이제 거의 완벽하게 Hint로 제어할 수 있게 되었군요.

    11g에서 V$SQL_HINT view를 보면 아래와 같이 Hint가 등록되어 있네요.

    NAME INVERSE VERSION
    -------------------- -------------------- --------------------
    NLJ_BATCHING NO_NLJ_BATCHING 11.1.0.6
    NO_NLJ_BATCHING NLJ_BATCHING 11.1.0.6

    NAME INVERSE VERSION
    -------------------- -------------------- --------------------
    NLJ_PREFETCH NO_NLJ_PREFETCH 11.1.0.6
    NO_NLJ_PREFETCH NLJ_PREFETCH 11.1.0.6

  2. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.05.11 09:59 신고  댓글주소  수정/삭제  댓글쓰기

    오타에 의해서 10g 에서 수행되었다고 적었네요.
    수정 하도록 하겟습니다.
    NLJ_BATCHING , NLJ_PREFETCH 힌트등은 11g 에서만 사용하실수 있습니다.
    역 힌트도 마찬 가지 입니다.
    또한 11g 에서도 NO_NLJ_BATCHING, NO_NLJ_PREFETCH 힌트를 같이사용하면 작동을 하지 않는것이 발견 되었습니다.
    따라서 11g 에서도 9i~11g 에서 발전된 모든 기능을 해제 하려면 파라미터를 사용하는수 밖에 없습니다.

  3. 바우 2009.05.11 18:00  댓글주소  수정/삭제  댓글쓰기

    올려주신 글 잘 읽어보고 있습니다. 이번 글에서 의문점이 생겨서 문의를 드립니다.

    9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
    이 부분이 잘 이해가 안됩니다.

    실행계획에서 보면 먼저 테이블 FULL로 읽고 인덱스 RANGE SCAN할때 여러개를 묶어서 하는 것이라고 이해가 되었습니다. 그렇다면 FULL SCAN이 아니라 인덱스 SCAN하고 테이블 갈때 한다는 것인데 INDEX SCAN에 관해서는 그런 기능이 없었다라고 하시니 헷갈립니다. 이것에 대해서 자세히 설명을 해주실 수 있을런지요.
    그리고 여러개라고 하셨는데 그게 몇개인지 알 수 없나요? 그리고 만약 그 몇개를 인위적으로 늘린다면 NL조인으로 부분범위 처리를 하고자 할때 성능 차이가 생길 수 있을 것 같은데 실제로 그렇게 되는지요. 궁금합니다.

    올려주신 글 항상 고맙게 잘 보고 있습니다. 감사합니다.

  4. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.05.11 21:17 신고  댓글주소  수정/삭제  댓글쓰기

    먼저 "9i/10g 에서 table prefetch 기능이 추가되었다" 함은 TABLE ACCESS BY INDEX ROWID 부분을 두고 하는말입니다. 제가 9i/10g plan 에 파랑색으로 표시한 부분입니다. 그부분은 index scan 이 아니지요.
    index scan 에 의한 부분은 11g 에 나옵니다. 내부적인 NLJ_BATCHING 힌트나 Batched IO vector block count 등이 그증거가 되겟습니다.
    또한 table prefetch 시에 운반단위수의 수정을 말씀해주셨는데 아주 위험 합니다.
    물론 그런기능을 하는 히든 파라미터가 존재 합니다.
    하지만 그것을 수정했을경우 나타나는 버그에 대해서는 누구도 알수 없습니다.
    검증된바가 없습니다.
    따라서 오라클사의 직원들조차 그 파라미터들을 건드리지 않습니다.
    건드리는 경우는 버그에 의해서 기능을 못쓰도록 막을때나 사용합니다.
    하지만 이또한 위험 하므로 히든파라미터는 그대로 놓고 쓰시는것이 제일 좋습니다.
    http://scidb.tistory.com/entry/히든-파라미터-설정의-위험성 를 참조 하시기 바랍니다.

  5. 바우 2009.05.12 09:11  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다. ^^

  6. 야함나르 2009.05.18 16:14  댓글주소  수정/삭제  댓글쓰기

    NLJ_BATCHING , NLJ_PREFETCH 힌트등은 11g 에서만 사용하실수 있습니다 라고 되어 있는데
    제가 보고 있는 DB의 경우 9.2.0.7 버전에서 8i와 같은 실행 계획이 나타나고 있습니다. 9 버전에는 힌트가 없다면 파라미터 조정을 통한 방법 말고 table prefetch를 유도하는 방법은 없을까요?.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.05.18 20:09 신고  댓글주소  수정/삭제

      파라미터를 조정하기 전에 table prefetch 기능은
      제약사항이 있으므로 먼저 nested loop join 의 형태를 점검 하셔야 합니다.
      1.nested loop join 일때만 가능함.
      2.선행집합이 index range scan 혹은 full table scan 이어야만 가능함.
      3.후행집합이 index range scan 일때만 가능함.
      위의 3가지 조건을 만족하는지 먼저 살펴보시기 바랍니다.

    • 야함나르 2009.05.19 11:44  댓글주소  수정/삭제

      네 위의 조건을 만족합니다.

      --------------------------------------------------------------------------------
      0 SELECT STATEMENT Optimizer=CHOOSE
      1 0 SORT (AGGREGATE)
      2 1 NESTED LOOPS
      3 2 TABLE ACCESS (FULL) OF 'WRITTEN'
      4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT'
      5 4 INDEX (UNIQUE SCAN) OF 'PRODUCT_PK' (UNIQUE)

      이런 플랜이 생성되어서 적용할수 있지 않을까 합니다만.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.05.19 11:48 신고  댓글주소  수정/삭제

      보여주신 plan 은 3번 째 조건을 만족 하지 못합니다.
      후행 테이블이 index unique scan 이므로 동시에 여러건을 가져오지 못합니다. index range scan 일때만 가능 합니다.

    • 야함나르 2009.05.19 13:10  댓글주소  수정/삭제

      아; 그렇군요 ^^.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.08.28 16:10 신고  댓글주소  수정/삭제

      과연 그렇군요.
      간단하지만 매우 유용한 테스트 입니다.
      좋은 정보 감사드립니다.^^

    • table prefetch 제약사항 점검.. 2009.08.28 16:23  댓글주소  수정/삭제

      table prefetch 제약사항 중 "2.선행집합이 index range scan 혹은 full table scan 이어야만 가능함"에 관련하여 테스트를 해 보았습니다.
      결론은 index range scan 뿐만 아니라 index full scan, fsat full scan, skip scan에서도 발생하는 군요..
      (MIX/MAX)에서는 table prefetch가 불가한테 descending + rownum으로 하니 가능하네요...

      --스크립트 (욱짜님 스크립)
      drop table t1 purge;
      --drop table t1;

      drop table t2 purge;
      --drop table t2;

      create table t1(c1 int, c2 int);
      create table t2(c1 int, c2 int);

      --create index t1_n1 on t1(c1);
      alter table T1 add constraint T1_PK primary key(c1);
      create index t1_n2 on t1(c2,c1);
      create index t2_n1 on t2(c1);

      insert into t1
      select rownum, round(rownum,-2)
      from all_objects, all_objects
      where rownum <= 10000
      ;
      commit;

      insert into t2
      select rownum, rownum
      from all_objects, all_objects
      where rownum <= 10000
      ;
      commit;

      EXEC dbms_stats.gather_table_stats(user, 'T1', cascade => true);
      EXEC dbms_stats.gather_table_stats(user, 'T2', cascade => true);


      -- 테스트 결과.
      SELECT /*+ USE_NL(T1 T2) LEADING(T1) INDEX(T2) */
      COUNT(T2.C2)
      FROM
      --CASE 01. INDEX UNIQUE SCAN(X)
      --(SELECT C1 FROM T1 WHERE C1 = 1256) T1 , T2

      --CASE 02. INDEX RANGE SCAN(O)
      --(SELECT /*+ INDEX_RS(T1) */ C1 FROM T1 WHERE C1 <= 1256) T1 , T2

      --CASE 03 INDEX RANGE SCAN DESCENDING(O)
      --(SELECT /*+ INDEX_DESC(T1) */ C1 FROM T1 WHERE C1 <= 1000 ) T1 , T2

      --CASE 04 INDEX RANGE SCAN (MIN/MAX)(X)
      --(SELECT /*+ INDEX_RS(T1) */ MAX(C1) C1 FROM T1 WHERE C1 <= 1000 ) T1 , T2

      --CASE 05 INDEX FULL SCAN(N/A)
      --(SELECT C1 FROM T1 ) T1 , T2

      --CASE 06. INDEX FULL SCAN (MIN/MAX)(X)
      --(SELECT MAX(C1) C1 FROM T1 ) T1 , T2

      --CASE 07 INDEX FULL SCAN DESCENDING(O)
      --(SELECT /*+ INDEX_DESC(T1) */ C1 FROM T1 ) T1 , T2

      --CASE 08 INDEX FAST FULL SCAN(O)
      --(SELECT /*+ INDEX_FFS(T1) */ C1 FROM T1 ) T1 , T2

      --CASE 09 INDEX SKIP SCAN(O)
      (SELECT /*+ INDEX_SS(T1 T1_N2) */ C1 FROM T1 ) T1 , T2
      WHERE T1.C1 = T2.C1 ;

  7. 2009.07.22 11:32  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 좋은 글 항상 감사합니다.
    다시 보다보니 궁금한 점이 있어서 질문드립니다.

    1. 기존 nested loop은 outer테이블에서 출발, inner인덱스에 엑세스하고 inner테이블에 엑세스해서 해당 정보까지 가져오는데 반해 소위 advanced nested loop(기존 NL과 비교해서 이걸 뭐라고 해야할지 모르겠습니다.)는 outer테이블의 스캔이 끝난 이후 inner인덱스과 join한 중간집합의 rowid로 inner테이블에 엑세스하기 때문에 outer테이블의 크기에 의해 결과반환시간(first_rows)에 영향을 줄 것 같고 결과적으로 기존의 부분처리와는 차이가 있을 것 같습니다.
    (빠른 결과를 위해 운반단위가 다차는 순간 테이블블록에 엑세스한다는 것에 생각해봤지만 반복적인 i/o를 줄이는 효과를 감소시키는 결과라서 이건 아닐 것같은 생각이 들고요.)
    만약 제 생각이 맞다면 어떤 식으로 테스트하면 두 차이를 확연히 드러낼 수 있을까요?

    2. outer테이블의 스캔이 끝난 이후 inner인덱스과 join한 중간집합은 inner인덱스의 rowid로 정렬되어 inner테이블 엑세스 시 logical i/o와 physical i/o를 줄일 수 있다고 이해하고 있습니다.
    그런데 중간집합이 inner인덱스의 rowid로 정렬된다는 것은 어떻게 확인 할 수 있을까요?
    실행계획에는 중간집합정렬에 관한 단서는 찾을 수 없는데..제가 잘못 이해하고 있는 부분이 있나요?


    질문이 장황하지만 제가 잘못 이해하고 있는 부분이 있다면 지적해주시고
    합당한 질문이라면 명쾌한 답을 주시면 감사하겠습니 다. ^^

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.07.23 09:58 신고  댓글주소  수정/삭제

      답변이 늦어져서 죄송합니다.
      1번에 대한 답변입니다.
      prefetch 라는 개념은 후행집합이 index range scan 으로 풀리므로 어짜피 scan 해야할 block 이 정해져 있다고 볼수 있습니다. 이때 Range 범위 내에서 Single index Range Scan 을 하는것 보다는 여러개의 Process 혹은 Thread 가 Index range Scan 을 하는 것이 성능상 유리하기 때문에 생긴 개념입니다.
      하지만 이것의 특징은 Hit ratio 가 있다는 점이지요.
      Prefetch 된 Block 들의 데이터를 사용하지 않을수 있기 때문입니다. 이 Hit Ratio 가 떨어지면 Prefetch 의 효과는 감소 됩니다.
      테스트를 수행시 v$sesstat 에서 SQL 수행전과 수행후의 value 의 차이를 모니터링 하시고 이때 나온 값들중에 Hit ratio 로 판단되는 값들이 있었습니다.
      이부분을 중점적으로 테스트 해보시는 것이 좋을거 같습니다.

      두번째 답변입니다.
      inner 테이블의 데이터는 inner 테이블의 인덱스 rowid 로 절렬이 되어 있지 않습니다. 그리고 실제로 정렬이 수행되지 않습니다. 물론 인덱스의 경우 데이터가 같은 값이라면 rowid 로 정렬리 되어 있기는 합니다.

      prefetch 시에 정렬을 하는 기능은 없는것으로 생각 하시면 됩니다.

  8. 2009.07.23 19:07  댓글주소  수정/삭제  댓글쓰기

    답변 감사합니다. (목이 좀 늘어났습니다 ㅎㅎ)
    저는 운반단위로 블럭에 엑세스하면 반복적인 i/o가 발생할 수 있어서 비효율적이고 그러니까 그렇게 처리하지 않을거야라고 생각했었는데 결과적으로 아닐거라고 생각한대로 동작하는군요.

    요즘 cost based oracle fundamentals(비투엔)을 읽고 있습니다. 그중 table prefetching의 동작을 설명하는 부분이 있는데 그중 "결과집합을 rowid순으로 정렬하고 나서 테이블 액세스를 시도하기 때문에 테이블 길이를 따라 블록들을 단 한 번씩만 방문하면 된다."(p386) 라는 글이 있습니다.
    이 부분때문에 여러가지로 궁리하게 되었는데 좀더 궁리해봐야겠습니다.

    답변 감사합니다. ^^

    • 브라이언홍 2009.08.27 15:44  댓글주소  수정/삭제

      http://ukja.tistory.com/166
      에서 댓글 3번에 이런 글이 있네요...
      Jonathan Lewis가 [CBO Fundamentals]에서 "Table Prefetching"이라는 용어를 사용했다고 하셨는데, 제가 볼 때는 이 책에서도 새로운 NL Join 메커니즘을 잘못 설명하고 있다고 여겨집니다(번역서에서 마침 그 부분을 제가 번역했는데, 제가 알고 있는 내용과 달라 많은 고민을 했었죠.)

  9. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.08.27 16:29 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사드립니다.