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. D.노마드 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

: name

: password

: homepage secret mode