Nested Loop Join 성능향상과 관련된 2가지 원리

  한 선지자에 의하여 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 에서 테스트 되었음을 다시한번 밝혀둡니다.>