이어서 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 에서 테스트 되었음을 다시한번 밝혀둡니다.>
출처 : http://scidb.tistory.com/entry/Nested-Loop-Join-성능향상과-관련된-2가지-원리