Advanced Nested Loop Join? Table Prefetching? Buffer Pinning?
오라클 2008.09.29 14:12얼마전 다음과 같은 내용의 문의를 받았다.
"Oracle 10g(10.2.0.4)에서 Nested Loop Join의 순서를 Oracle 8i와 동일하게 할 수 없는가?"
무슨 말인고 하면...
Oracle 8i에서는 Nested Loop Join이 다음과 같은 순서로 실행 계획상에 나타난다.
select /*+ use_nl(t1 t2) full(t1) index(t2) */
count(t2.c2)
from t1, t2
where t1.c1 = t2.c1
;
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
10000 NESTED LOOPS
10001 TABLE ACCESS FULL T1
10000 TABLE ACCESS BY INDEX ROWID T2
20000 INDEX RANGE SCAN (object id 14645)
하지만 9i 부터는 순서가 다음과 같이 바뀐다. TABLE ACCESS BY INDEX ROWID T2 단계가 NESTED LOOP의 바깥에 존재한다!!!
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
10000 TABLE ACCESS BY INDEX ROWID T2
20001 NESTED LOOPS
10000 TABLE ACCESS FULL T1
10000 INDEX RANGE SCAN T2_N1 (object id 31030)
무슨 일이 생긴 것인가?
이 현상을 [새로쓴 대용량 데이터베이스 솔루션 I]에서는 "Advanced Nested Loop Join"이라고 부르고 있다. 하지만 이는 불행히도 공식적인 용어가 아니다. 적어도 내가 확인한 바로는 그렇다.
반면에 Jonathan Lewis의 [CBO Fundamentals]라는 책을 보면 이를 "Table Prefetching"이라고 부르고 있다. 비록 Oracle이 이를 공식적으로 설명하고 있지는 않지만 Jonathan Lewis의 용어가 정확하다. 그 이유는 이런 변화가 다음 두 개의 Parameter로 제어하기 때문이다.
(Parameter의 정확한 의미는 추가적인 조사가 필요함)
UKJA@ukja102> @para table_lookup_prefetch
old 9: and i.ksppinm like '%&1%'
new 9: and i.ksppinm like '%table_lookup_prefetch%'
NAME VALUE SES_MODIFI SYS_MODIFI
------------------------------ -------------------- ---------- ----------
DESCRIPTION
--------------------------------------------------------------------------
--------------------------------------------------------------------------
_table_lookup_prefetch_size 40 false false
table lookup prefetch vector size
_table_lookup_prefetch_thresh 2 false false
table lookup prefetch threshold
Parameter 이름에서 알 수 있듯이 Oracle은 9i부터 Nested Loop Join에 의해 후행 Table을 Lookup할 때 Prefetch, 즉 미리 읽기를 수행하게끔 코드를 수정하였고 그 결과 Nested Loop Join의 순서가 바뀐 것이다.
이것은 순전히 성능을 위해서이다. 실제로 9i와 10g에서 동일한 Nested Loop Join을 수행하면 8i에 비해서 일량이 크게 개선된다.
아래에 간단한 테테스트 결과가 있다.
@capture_on
@version
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);
create index t2_n1 on t2(c1);
insert into t1
select rownum, rownum
from all_objects, all_objects
where rownum <= 10000
;
insert into t2
select rownum, rownum
from all_objects, all_objects
where rownum <= 10000
;
@trace_on 10046 1
@mysid
@mon_on &v_sid
select /*+ use_nl(t1 t2) full(t1) index(t2) */
count(t2.c2)
from t1, t2
where t1.c1 = t2.c1
;
@mon_off
@mon_show
@trace_off
@capture_off
8.1.0.7에서의 결과는 다음과 같다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.31 0.32 55 40020 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.31 0.33 55 40020 4 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
10000 NESTED LOOPS
10001 TABLE ACCESS FULL T1
10000 TABLE ACCESS BY INDEX ROWID T2
20000 INDEX RANGE SCAN (object id 14645)
NAME VALUE1 VALUE2 DIFF
---------------------------------------- ------------ ------------ ------------
session logical reads 138,414 178,618 40,204
consistent gets 136,126 176,270 40,144
buffer is not pinned count 81,131 111,200 30,069
no work - consistent read gets 50,817 80,868 30,051
no buffer to keep pinned count 124,595 144,596 20,001
table fetch by rowid 29,540 39,571 10,031
table scan rows gotten 186 10,186 10,000
redo size 1,785,720 1,791,048 5,328
bytes received via SQL*Net from client 8,654 10,284 1,630
bytes sent via SQL*Net to client 6,285 7,165 880
recursive calls 4,899 5,560 661
sorts (rows) 17 307 290
...
buffer is pinned count 14 38 24
9.2.0.1에서의 결과는 다음과 같다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.05 0 10062 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.06 0 10062 0 1
일량이 40,000 블록에서 10,000 블록으로 현격하게 감소했다! Table Prefetch에 의한 성능 개선 결과이다.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
10000 TABLE ACCESS BY INDEX ROWID T2
20001 NESTED LOOPS
10000 TABLE ACCESS FULL T1
10000 INDEX RANGE SCAN T2_N1 (object id 31030)
********************************************************************************
그 차이는 어디서 온 것인가?
buffer is pinned count 값의 변화에 주목하자. Table Prefetching에 의해 Buffer Pinning이 이루어지고 그 만큼 Logical Reads는 감소한다.
NAME VALUE1 VALUE2 DIFF
---------------------------------------- ------------ ------------ ------------
redo size 5,056,820 5,117,476 60,656
buffer is pinned count 315,121 345,100 29,979
session logical reads 421,465 432,127 10,662
consistent gets 416,214 426,355 10,141
no work - consistent read gets 173,058 183,136 10,078
shared hash latch upgrades - no wait 254,328 264,344 10,016
index scans kdiixs1 254,282 264,297 10,015
table fetch by rowid 132,582 142,597 10,015
table scan rows gotten 20,497 30,497 10,000
10.2.0.1 또한 9i와 (거의) 비슷한 결과를 보여준다.
select /*+ use_nl(t1 t2) full(t1) index(t2) */
count(t2.c2)
from t1, t2
where t1.c1 = t2.c1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.73 0.72 0 10062 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.73 0.73 0 10066 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=10062 pr=0 pw=0 time=726161 us)
10000 TABLE ACCESS BY INDEX ROWID T2 (cr=10062 pr=0 pw=0 time=640128 us)
20001 NESTED LOOPS (cr=10044 pr=0 pw=0 time=460735 us)
10000 TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=40053 us)
10000 INDEX RANGE SCAN T2_N1 (cr=10021 pr=0 pw=0 time=200364 us)(object id 68407)
NAME VALUE1 VALUE2 DIFF
---------------------------------------- ------------ ------------ ------------
physical read total bytes 20,545,536 20,717,568 172,032
physical read bytes 20,545,536 20,717,568 172,032
buffer is pinned count 113,181 153,148 39,967
table scan rows gotten 218,640 248,640 30,000
sorts (rows) 110,074 132,467 22,393
session logical reads 248,435 259,065 10,630
consistent gets from cache 246,319 256,891 10,572
consistent gets 246,319 256,891 10,572
no work - consistent read gets 100,752 110,982 10,230
shared hash latch upgrades - no wait 83,155 93,258 10,103
table fetch by rowid 46,258 56,358 10,100
index scans kdiixs1 82,872 92,968 10,096
다음과 같은 문의 내용을 만족하려면?
"Oracle 10g(10.2.0.4)에서 Nested Loop Join의 순서를 Oracle 8i와 동일하게 할 수 없는가?"
두 가지 정도의 옵션이 있다.
1) optimizer_features_enable = '8.1.7'로 변경한다.
2) Hidden Parameter인 _table_lookup_prefetch_size, _table_lookup_prefetch_thresh의 값을 변경(가령 0)한다.
이 경우 10g에서 다음과 같이 8i와 비슷한 형태로 변경된다.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=20044 pr=0 pw=0 time=825897 us)
10000 NESTED LOOPS (cr=20044 pr=0 pw=0 time=800122 us)
10000 TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=40057 us)
10000 TABLE ACCESS BY INDEX ROWID T2 (cr=20021 pr=0 pw=0 time=637141 us)
10000 INDEX RANGE SCAN T2_N1 (cr=10021 pr=0 pw=0 time=202725 us)(object id 68389)
NAME VALUE1 VALUE2 DIFF
---------------------------------------- ------------ ------------ ------------
physical read total bytes 5,914,624 6,086,656 172,032
physical read bytes 5,914,624 6,086,656 172,032
session pga memory 2,595,792 2,661,328 65,536
table scan rows gotten 218,528 248,528 30,000
sorts (rows) 101,901 124,294 22,393
session logical reads 214,015 234,628 20,613
consistent gets from cache 211,916 232,471 20,555
consistent gets 211,916 232,471 20,555
buffer is not pinned count 151,561 171,791 20,230
no work - consistent read gets 94,773 114,985 20,212
buffer is pinned count 109,172 129,158 19,986
shared hash latch upgrades - no wait 80,301 90,403 10,102
table fetch by rowid 35,529 45,629 10,100
index scans kdiixs1 80,325 90,421 10,096
buffer is pinned count 값이 감소한 만큼 Logical Reads가 증가하게 된다. 물론 성능에는 매우 해롭다고 할 수 있다.
Oracle 11g에서의 변화는 더욱 놀랍다. 아래 결과(11.1.0.6)를 보면 일량이 무려 263으로 줄어들었다.
select /*+ use_nl(t1 t2) full(t1) index(t2) */
count(t2.c2)
from t1, t2
where t1.c1 = t2.c1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.05 0 50 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.26 0.26 0 213 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.28 0.32 0 263 0 1
더불어 실행 계획은 더 마법같이 변했다. Nested Loop Join이 두 번 발생하는 것으로 관찰된다.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=213 pr=0 pw=0 time=0 us)
10000 NESTED LOOPS (cr=213 pr=0 pw=0 time=2804 us)
10000 NESTED LOOPS (cr=195 pr=0 pw=0 time=1520 us cost=20026 size=390000 card=10000)
10000 TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=154 us cost=7 size=130000 card=10000)
10000 INDEX RANGE SCAN T2_N1 (cr=172 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79713)
10000 TABLE ACCESS BY INDEX ROWID T2 (cr=18 pr=0 pw=0 time=0 us cost=2 size=26 card=1)
이런 큰 변화는 어디서 발생한 것인가?
정답은 Row Source Operation에 있다. 10g와 11g의 Row Source Operation을 비교해보자.
10g의 경우 Table Prefetching에 의해 Table T2에 대한 Logical Reads가 10062-10021 = 41 블록만 발생한다. (Buffer Pinning) 하지만 Index T2_N1에 대해서는 10021의 높은 Logical Reads를 보인다.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=10062 pr=0 pw=0 time=726161 us)
10000 TABLE ACCESS BY INDEX ROWID T2 (cr=10062 pr=0 pw=0 time=640128 us)
20001 NESTED LOOPS (cr=10044 pr=0 pw=0 time=460735 us)
10000 TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=40053 us)
10000 INDEX RANGE SCAN T2_N1 (cr=10021 pr=0 pw=0 time=200364 us)(object id 68407)
11g의 경우 Table Prefetch뿐만 아니라 Index T2_N1에 대한 Prefetch까지 이루어진다. Index T2_N1에 대한 Logical Reads가 10021에서 172로 줄어든 것이 그 증거이다.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=213 pr=0 pw=0 time=0 us)
10000 NESTED LOOPS (cr=213 pr=0 pw=0 time=2804 us)
10000 NESTED LOOPS (cr=195 pr=0 pw=0 time=1520 us cost=20026 size=390000 card=10000)
10000 TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=154 us cost=7 size=130000 card=10000)
10000 INDEX RANGE SCAN T2_N1 (cr=172 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79713)
10000 TABLE ACCESS BY INDEX ROWID T2 (cr=18 pr=0 pw=0 time=0 us cost=2 size=26 card=1)
이쯤되면 "다른 건 몰라도 Nested Loop Join은 내가 좀 알지"라고 생각했던 것이 매우 좁은 시각이었던 것을 인정해야 할 것이다.
'오라클' 카테고리의 다른 글
세로 데이터를 가로로 출력하기 - 또 다른 아이디어 (4) | 2008.10.04 |
---|---|
Bitmap Index 크기 문제 - Scatterness (1) | 2008.09.30 |
Advanced Nested Loop Join? Table Prefetching? Buffer Pinning? (15) | 2008.09.29 |
Oracle이 거짓말을 할 때 - Elapsed Time과 Wait Time의 역전 (2) | 2008.09.19 |
Hint에 대해 당신이 모르는 것 - Oracle은 Hint를 무시하지 않는다. (2) | 2008.09.17 |