태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

Advanced Nested Loop Join? Table Prefetching? Buffer Pinning?

오라클 2008.09.29 14:12
(이 글에서 사용된 Nested Loop, Table Prefetching, Buffer Pinning 등의 용어에 대한 토론을 Comment를 통해서 반드시 확인하세요. 그리고 Table (Lookup) Prefetching에 대한 자세한 설명은 Metalink 406966.1를 참조하세요)

얼마전 다음과 같은 내용의 문의를 받았다.

"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은 내가 좀 알지"라고 생각했던 것이 매우 좁은 시각이었던 것을 인정해야 할 것이다.

PS) 대용량데이터베이스 솔루션에서 밝힌 Advanced Nested Loop Join이라는 용어가 어디서 근거했는지 모르겠다. 있다면 누군가가 알려주었으면 좋겠다. 잘못된 용어의 사용은 우리가 가장 조심해야할 일이다.





신고
Trackback 0 : Comments 15
  1. 백면서생 2008.09.30 11:38 신고 Modify/Delete Reply

    2001년도인가 DSS관련 9i New Feature오라클 기술백서 문서 보면서 data block prefetching에 관한 내용( data blcok parallel random i/o )을 본적이 있는데 그때 위의 내용을 같이 봤던 기억이 나네요.
    11g는 한단계 더 업그레이드 된 모양이네요.^^
    매번 좋은 자료 감사합니다.
    새로쓴 대용량 데이터베이스 솔류션은 서브쿼리 세미조인 부분도 그렇고 개념적으로 정립이 안된 부분이 조금 있었던 것 같네요(subquery unnesting과 semi join의 불명확한 개념정도로 기억.^^;;).

  2. 욱짜 2008.09.30 16:07 신고 Modify/Delete Reply

    그런 자료가 있었군요. 시간날 때 검색해봐야겠습니다.

  3. oraking 2008.10.02 09:31 신고 Modify/Delete Reply

    안녕하세요... 비투엔 컨설팅 조시형입니다. 이렇게 온라인 상에서 만나 뵙기는 처음인 것 같군요. 회사 일도 바쁘실 텐데 이처럼 온라인 상에서도 활발히 활동하시는 것을 보면 참 부럽습니다.
    "Advanced"라는 용어는 저에게 원죄가 있어 이렇게 몇 자 적어 봅니다. 조동욱 선생께서 여기서 설명한 내용을 제가 2004년도 엔코아 근무시절에 내부 세미나에서 발표한 적이 있는데, 그 때 제가 "Advanced"라는 용어를 사용했었습니다. Inner Table Access가 NL 위쪽으로 올라가는 새로운 NL 조인 메커니즘 뿐 아니라 Clustering Factor가 좋을 때 Logical read가 적게 발생하는 원리도 함께 설명하는 자리였죠. 그 때는 PGA Buffer Cache라는 개념을 이용해 설명했었구요. 즉 한번 읽은 블럭을 PGA에 캐싱함으로써 반복적인 Logical Read를 발생시키지 않는 것이라고 말이죠. 이 개념이 buffer pinning 개념으로 설명돼야 한다는 것을 안 것은 엔코아를 나온 이후 시점이었는데, 엔코아에서 그 자료를 가지고 그대로 책을 기술했더군요. 아차 싶었죠. 하지만 PGA에 Buffer Block을 통째로 Cloning해서 캐싱한다기 보다는 그 블럭 주소(Address)를 캐싱하는 것이라고 이해한다면 크게 잘못된 설명도 아닐 것입니다. Buffer Pinning이라고 하는 것이 바로 그런 것이니까요.
    어쨌든 "Advanced"라는 표현은 저로부터 비롯된 것이라고 볼 수 있는데, NL Join이 이전보다 "진보된" 방식으로 처리된다는 것을 표현하고자 했던 것이므로 그다지 잘못 붙인 이름은 아니라고 생각하는데요...
    오히려 Buffer Pinning에 의한 새로운 처리방식을 "Prefetch"라는 용어를 사용해 설명하는 것은 부적절하지 않나 싶습니다. 물론 _table_lookup_prefetch_size와 _table_lookup_prefetch_thresh 이 두 파라미터에 의해 실행계획이 바뀐다는 사실은 알고 있었지만(참고로, _multi_join_key_table_lookup 파라미터에 의해서도 바뀜), 이들 파라미터의 값들이 의미하는 바를 정확히 밝혀내지 못하는 이상 Buffer Pinning에 의한 CR감소 효과를 table prefetch라고 명명하는 것은 좀 무리가 아닐까요? (이들 파라미터 의미에 대해 저도 나름대로 심증가는 부분이 있지만 증명하기가 어렵더군요.)
    오라클은 Buffer Pinning을 적용하는 지점을 점차 확대시켜 나가고 있고, NL 조인 메커니즘에서의 개선도 그 중 하나일 뿐이라고 생각합니다.
    - range scan에서의 Index Leaf Block(7 이전)
    - 인덱스로부터 액세스되는 테이블 블럭(8i)
    - NL 조인시 Inner Index Root Block(9i)
    - NL 조인시 Inner Table Block(9i)
    - Index Skip Scan에서의 Branch Block(9i)
    - NL 조인시 Inner Index Block(11g)
    - DML문이 수행되는 동안의 Undo Block(어느 버전부터인지 모르겠지만) 등

    제가 아는 한 Prefetch라는 용어는 Disk I/O와 관련 있습니다. Index Prefetch도 오래 전부터 사용돼 오고 있지만 여기서는 9i부터 사용되기 시작한 Table Prefetch에 대해서만 논하고자 합니다.
    인덱스를 경유해 rowid를 얻고 테이블을 액세스하려고 하는데 Buffer Cache에서 찾지 못하면 물리적인 I/O request가 필요해지죠. 이 때, 하나의 테이블 블럭을 읽기 위해 하나의 I/O request를 발생시키고 Waiting하는 것은 소모적이므로 테이블 액세스를 잠시 미루고 인덱스로부터 몇 개의 row를 더 읽어 앞으로 Access하게 될 테이블 블럭 주소 목록을 미리 얻습니다. 그런 후에 병렬방식으로(또는 batch 방식이라고도 함) multiple single block read requests를 합니다. 결국 multiblock read를 하는 것인데, 우리가 일반적으로 알고 있는 db file multiblock read는 "contiguous" multiblock read인 반면 prefetch에서의 multiblock read는 "noncontiguous" multiblock read 인 것입니다. 한번의 Waiting으로 여러 블럭을 미리 퍼 올리므로 전반적으로 I/O관련 Wait이 감소하겠죠. 그리고 prefetch에 의한 multiblock read는 비동기(asynch I/O) 방식으로 이루어집니다.
    prefetch는 어차피 곧이어 액세스하게 될 블럭들을 미리 Buffer Cache에 Load 해 두는 기능이라고 말할 수 있습니다. 앞서 잠시 언급한 index prefetch도 마찬가지죠(브랜치 블럭에서 리프 블럭 주소 목록을 얻어 한번에 I/O Call). 또한 CKPT 프로세스가 prefetch된 block들을 모니터링하다가 실제 Buffer Block Access로 연결되지 못한 채 메모리에서 Flushing(또는 Age-out)되는 비율이 높다면 더이상 prefetch 기능이 작동되지 못하도록 Disable 시켜 버립니다.

    앞서 백면서생이라는 분이 언급한 그 문서를 저도 오래 전에 봤는데 거기서도 Disk I/O 관점에서 설명하고 있는 것을 알 수 있습니다.(Oracle delays data blocks reads until multiple rows specified by the underlying index are ready to be accessed and then retrieves multiple data blocks at once, rather than reading a single data block at a time. Block prefetching allows better utilization of the I/O capacity, and provides a further reduction in response time by issuing I/O operations in parallel whenever possible.)

    Jonathan Lewis가 [CBO Fundamentals]에서 "Table Prefetching"이라는 용어를 사용했다고 하셨는데, 제가 볼 때는 이 책에서도 새로운 NL Join 메커니즘을 잘못 설명하고 있다고 여겨집니다(번역서에서 마침 그 부분을 제가 번역했는데, 제가 알고 있는 내용과 달라 많은 고민을 했었죠.) 거기서는 Inner Table 액세스를 위해 사용되는 인덱스 로우만으로 조인을 완성하고 인덱스 rowid 순으로 정렬한 후 Inner 테이블을 액세스하기 때문에 Logical Read를 줄일 수 있다, 그래서 결과적으로 Physical I/O까지 감소하는 효과가 있다고 설명하고 있죠(AskTom에서도 같은 방식으로 설명한 것을 본 적이 있습니다). 만약 그게 사실이라면 NL 조인 시 부분범위 처리가 가능할까요? 부분범위 처리를 떠나서 NL 조인 결과가 Inner Table의 Rowid 순으로 정렬돼서 나오는 경우를 본 적이 없습니다. 다행히 Jonathan Lewis도 확실히 그렇다기 보다는 그렇게 이해하고 있다고 표현하고 있습니다. 이 책에서 뿐 아니라 Google에서 찾아지는 Jonathan Lewis의 다른 설명에서도 조금씩 다르게 설명하고 있는데, 거기서도 확실치는 않다고 말하고 있습니다.(that’s my assumption at present, thought not proved.)
    어차피 "table prefetch"도 정확한 용어라고 보기 어렵다면 "Advanced"라고 표현하는 것도 나쁘지 않다고 봅니다.

    마지막으로, 오라클이 공식적으로 설명한 적이 없기 때문에 Jonathan Lewis처럼 저 역시도 위 내용이 제가 연구하고 이해한 바를 설명한 것에 불과하다는 점을 밝힙니다.

  4. 욱짜 2008.10.02 12:43 신고 Modify/Delete Reply

    반갑습니다.
    Advanced Nested Loop Join의 명칭에 그런 History가 있었군요. 미리 알았더라면 좋았었겠습니다.

    용어가 상당히 혼란스런운 것은 사실인거 같습니다. Oracle이 공식적으로 용어를 지정해주면 좋았었겠다는 생각을 하곤 합니다. 아마 Jonathan Lewis의 설명에다가 Prefetch 과정이 Query Level이 아닌 Fetch Level이다라는 첨언을 붙이면 어느 정도 정확하지 않을까 합니다. Nested Loop Join을 통해 여러 번에 나누어 Fetch(Fetch Array Size에 따라)하는 경우 각 *Fetch Level*로 Prefetch 및 Buffer Pinning이 이루어지게 될겁니다. 따라서 NL Join의 부분 범위 처리도 가능하고, 모든 Row가 Rowid 순으로 정렬될 필요도 없을겁니다.

    상세한 답변 감사드리구요. 나중에 공동으로 세미나같은 걸 할 수 있으면 좋겠습니다. ^^

  5. 욱짜 2008.10.02 12:51 신고 Modify/Delete Reply

    그리고, Jonathan Lewis의 [CBO Fundamentals]에서 10gR2로 인해 변화된 내용과 일부 보완해야할 규칙같은 것들을 몇 개 발견했습니다. 나중에 기회가 되면 블로그나 세미나를 통해 공유하도록 하겠습니다.

  6. oraking 2008.10.02 15:15 신고 Modify/Delete Reply

    Jonathan Lewis의 설명도 맞고 Buffer Pinning에 의한 효과도 맞다고 설명하시는 것 같은데, 제 의견은 후자가 맞다입니다. 조동욱 선생께서 이 Article 본문에서 설명하신 그대로입니다. 서로 배치되는 두 가지 원리를 같이 놓고 설명하는 것은 좀 이해하기 힘들군요.
    1)
    Jonathan Lewis는 NL 조인할 때 Inner 테이블 액세스 단계에서 Block I/O가 적게 발생하는 원인을 설명하는 데 있어 테이블 액세스 이전에 일정량을 모아 Rowid순(결과적으로 Block Address 순)으로 정렬하기 때문이라고 설명하고 있습니다. 저도 "Jonathan Lewis의 이론이 맞다면"(또한 Tom 아저씨도 그렇게 설명하고 있으며, 두 사람 다 무시할 수 없는 guru임) 말씀하신 대로 Fetch Level로 정렬해서 액세스할 것이라고 추측하고 있었습니다. 그 방법 말고는 부분범위 처리를 설명할 방법이 없으니까요. 하지만, 테스트 해 보면 아시겠지만 전체 Row가 Rowid순으로 정렬되지 않는 것은 물론이거니와 하나의 Fetch Call 내에서도 결코 정렬되지 않습니다. 인덱스 엔트리가 가리키는 순서 그대로 출력되죠.
    2)
    반면, 여기 본문에서 조동욱 선생께서 밝히고 계신 원리는 Buffer Pinning에 의한 것이고, 이것은 저의 오랜 테스트 결과와 정확히 일치합니다. 저는 Stat 정보를 조회해 본 것에 그치지 않고 실제 Dump를 떠서 액세스하는 블럭주소를 따라 가 보는 실험을 여러 번 했었습니다. Jonathan Lewis의 설명대로라면 Inner Table 인덱스 블럭들을 일정량 스캔하기 전까지 테이블 블럭 액세스가 나타나지 않아야 하는데, Dump를 떠보면 인덱스블럭 액세스에 이어 곧바로 테이블 블럭 액세스가 나타납니다. 그런 후 인덱스 엔트리가 다른 테이블 블럭을 가리킬 때까지 인덱스 블럭 액세스만 계속 나타납니다. 앞서 읽은 테이블 블럭 주소를 계속 기억하고 있기 때문에 CR Request 없이 곧바로 Pointer에 의한 액세스를 하고 있다는 뜻이죠. Rowid순 정렬 없이도 CR이 적게 발생하는 이유가 충분히 설명됩니다. 그리고 Buffer Pinning을 유지하는 단위가 Fetch Call 단위인 것은 분명 맞습니다. 어차피 Block Address를 기억하는 저장소는 CGA(Call Global Area)일텐데, 이 공간은 하나의 Call 내에서만 유효한 데이터만을 저장하는 곳이니까요. 실제 테스트 결과에서도 그렇게 나타납니다.
    결론)
    어쨌든 두 방법 모두 "prefetch"라는 용어와는 그다지 어울리지 않습니다. 제 설명대로 I/O Call 시점에 블럭들을 미리 퍼 올린다는 개념이 더 근접하다고 믿고 있습니다. 제 의견을 한마디로 요약하면, "논리적인 Memory Buffer 액세스 단계에서는 'Buffer Pinning'이라는 기술을 사용하고, 물리적인 I/O Call 시점에는 'Prefetch'라는 기술을 사용한다"입니다.
    PS)
    저도 이런 기술적인 토론을 상당히 즐기는 편인데, 프로젝트에만 몰두하다 보니 최근 몇 년 동안 이런 활동에 소홀했던 것 같습니다. 앞으로 자주 방문하고 토론에도 참여하도록 하겠습니다. 가끔 반대 의견을 개진하더라도 화내지 않으시리라 믿습니다. ^^

    반가웠습니다.

  7. 욱짜 2008.10.02 16:45 신고 Modify/Delete Reply

    역시 용어의 문제네요. 평소에도 항상 정확한 용어를 사용해야 한다고 생각하고 있는데 이번에도 제가 엄밀함을 잃었습니다.

    일단 9i에서의 실행 계획상의 변화는 Table (Lookup) Prefetch 메커니즘에 의한게 맞습니다. Metalink 406966.1에 명확하게 설명이 되어 있습니다.

    문제는 제가 테스트한 결과에서 볼 수 있는 성능 개선이 Prefetch에 의한게 아니라는 것입니다. Buffer Pinning에 의한 것이죠? 이 부분을 지적하신걸로 이해됩니다.

    즉, 실행 계획의 변화는 Table Lookup Prefetch 메커니즘을 반영한 것이고(하지만 실제로 Prefetch가 이루어지는 가와는 무관), 9i에서의 개선된 Logcial Reads는 Buffer Pinning에 의한 것이다. 이렇게 정리하면 정확할거 같습니다.

    참고로, Prefetch라는 용어 자체가 Oracle 내에서 상당히 혼용되어 있어서 오해의 소지가 많은 거 같습니다. Fetch Array Size도 Prefetch Size라고 불리고, 기타 문서에서 보면 Prefetch라는 용어가 자주 등장합니다. 위의 글에서의 Prefetch는 Table Lookup Prefetch라고 정확하게 부르는 것이 좋을 거 같습니다.

  8. oraking 2008.10.03 20:50 신고 Modify/Delete Reply

    많이 부분이 명확해 진 것 같습니다.
    제가 첫 번째 답변에서 "오라클은 Buffer Pinning을 적용하는 지점을 점차 확대시켜 나가고 있고, NL 조인 메커니즘에서의 개선도 그 중 하나일 뿐"이라고 했던 부분과도 잘 일치하고, prefetch는 오히려 Disk I/O와 관련 있다고 지적한 부분도 언급하신 메타링크 문서를 통해 확인이 된 것 같습니다. 그리고 메타링크 문서를 읽어 보니 NL 실행계획의 변화를 오라클이 "Table (Lookup) Prefetch"라고 명명하고 있는 것도 사실이군요.
    따라서 마지막으로 정리하신 아래 명제가 틀림없어 보입니다.
    "실행 계획의 변화는 Table Lookup Prefetch 메커니즘을 반영한 것이고, 9i에서의 개선된 Logcial Reads는 Buffer Pinning에 의한 것이다."
    함께 토론하는 과정에서 저 역시도 아주 명쾌해졌습니다. 감사합니다.
    PS) 애초에 설명하려고 했던 Buffer Pinning에 의한 NL 조인 개선 효과에 대해서는 아직 Naming이 이루어지지 않았네요. "Advanced"? ㅋㅋ 농담입니다. 여기 방문하신 모든 분들께 공모해 보는 건 어떨까요? ㅎㅎ

  9. extremedb 2008.10.07 17:09 신고 Modify/Delete Reply

    음 제가 낄틈이 없이 두분이서 결론을 내셨네요.
    테스트 하실때 buffer_cache 를 flush 한후에 테스트 해보시기 바랍니다.
    왜냐하면 physical read를 할때만 prefetch 의 효과를 누릴수 있기 때문입니다.
    아래는 테스트 결과 입니다.

    NAME VALUE
    ---------------------------------------- ----------
    physical reads cache prefetch 17 --> prefetch
    physical read total multi block requests 3 --> multi block IO 발생

  10. 욱짜 2008.10.07 17:32 신고 Modify/Delete Reply

    좋은 정보 감사합니다.

  11. 시연아카데미 2008.10.08 01:38 신고 Modify/Delete Reply

    국내에서도 이런 심도깊은 토론의 장이 열릴수 있다니... 아주 들뜬 마음으로 재미있게 글/답글을 읽었습니다. 동욱차장이 온라인으로 고수들을 하나둘 끌어모으시는군요. 좋은 정보들 감사합니다~!

  12. KT 2008.12.26 19:10 신고 Modify/Delete Reply

    "Direct path Reference Notes"
    ...
    Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache). If asynchronous IO is supported (and in use) then Oracle can submit IO requests and continue processing. It can then pick up the results of the IO request later and will wait on "direct path read" until the required IO completes.

    ....

    This style of read request is typically used for:

    - Sort IO (when a sort does not fit in memory)
    - Parallel Query slaves
    - Readahead (where a process may issue an IO request for a block it expects to need in the near future)

    여기서,
    "Readahead"가 "Table Lookup Prefetch" 과정을 얘기하는 걸까요?

  13. 욱짜 2008.12.26 20:37 신고 Modify/Delete Reply

    Metalink 문서 406966.1에 의하면 Table Lookup Prefetch가 Disk I/O를 유발하는 경우에는 db file parallel read 이벤트를 대기하게 됩니다.

  14. ktlee67@show.co.kr 2010.01.15 11:37 신고 Modify/Delete Reply

    좋은 글 잘 읽었습니다. 결론적으로 "실행계획의 변화는 Table (lookup) prefetching에 의한 것이고, logical read의 개선은 buffer pinning에 의한 것이다."로 귀결된 것 같습니다.
    헌데, 그렇다면 prefetching의 히든파라미터 "_table_lookup_prefetch_size"와 "_table_lookup_prefetch_thres"의 수정으로 이전 8i와 같은 plan으로 동작하게 한다면, buffer pinning 효과는 살아있어야 하는 것 아닐까요? table prefetch를 diable한다면, buffer pinning 효과도 사라지는 건가요? 위의 본문내용중에서 10g에서 파라미터 변경으로 8i plan으로 바뀐 경우 블록을 많이 읽는 것으로 나오는데(물론 실제 8.1.7 버전에서 테스트한 것 보다 적게 나오는 것으로 보이지만) 이 부분이 명확치 않습니다. 수고하세요 ^^

  15. makeityourrings 2011.11.17 17:33 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바