Oracle Data Block Prefetching

다음은 oracle korea에서 만든(?) oracle 9i의 new feature인 data block prefetching에 대한 내용을 발췌했습니다..

데이타 블럭 프리페칭(Data Block prefetching)은 Oracle9i에서 새롭게 선보이는 내부 최적화 기능으로서 특정한 경우에 질의 응답 시간을 크게 개선할 수 있다. 데이타 블럭 프리페칭은 테이블 조회(lookup)에 의해 사용된다.

색인 액세스 경로가 선택되고 질의가 색인 만으로는 충족될 수 없을 경우, rowid가 지시하는 데이타 행 역시 페치되어야 한다. 이 데이타 행 액세스(테이블 조회)에 대한 rowid는 올바른 rowid의 어레이가 지시히는 블럭 어레이를 읽는 것을 수반하는 데이타 블럭 프리페칭을 사용해 개선됐다.

블럭 프리페칭은 I/O 처리 성능에 대한 보다 효과적인 활용과 가능할 경우 항상 병렬 읽기를 발행해 응답시간을 단축시킬 수 있도록 지원한다.

데이타 블럭 프리페칭은 대개 색인이 빈약하게 클러스터링되고 테이블 액세스가 전반적으로 낮은 버퍼 캐시 적중률 의 랜덤 디스크 액세스를 수반하는 경우 유용하다.

이 경우 질의는 시스템 상에 가용 I/O 대역폭이 있더라도 이들 단일 블록 들이 캐시로 동시에 읽혀지기를 기다림으로써 손쉽게 I/O 최대값에 도달할 수 있다. 데이타 블럭 프리페칭 주요 색인에 만족스러운 rowid의 수가 축적될 때까지 테이블 블럭의 읽기를 지연시킨다. I/O 완료 시 훨씬 짧게 기다림으로써 데이타베이스 인스턴스가 CPU 자원을 보다 효과적으로  활용할 수 있도록 지 원하게 된다.

샘플 문제:
TPC-H 스펙의 질의 17은 데이타 블럭 프리페치가 사용될 수 있는 대표적인 예제이다.
해당 브랜드 및 컨테이너 유형의 경우, 평균 보다 20% 적은 수량의 주문이 접수된다면, 연평균 매출액 중 총손실액은 어느 정도인가?

데이타베이스는 7년 간의 데이타를 포함하고 있다.

구현:

다음 질의는(DOP 16) Oracle8i and Oracle9i 모두에서 위의 문제를 구현하는데 사용된다

select  sum(l_extendedprice) / 7.0 as avg_yearly parts,
from   parts,  lineitem l1
where  p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container = 'MED BOX'
and l_quantity <(
                           select 0.2 * avg(l_quantity)
                           from lineitem l2
                           where l_partkey = p_partkey);

Oracle8i에서 질의 수행시간은 264초였다
Oracle9i에서 질의 수행시간은 180초였으며 이는 31.82%의 향상을 나타낸다

다음은 위의 white-paper의 원문입니다.
영어는 잼병이지만, 아래의 원문을 보기전까지는 위의 말이 뭔소린지 이해가 안됬다능..
(한글 공부를 다시해야 하나 .. 고민)

Data block prefetching is an example of an 'under the covers' performance feature provided in Oracle9i. This feature is entirely transparent to both the end-users and database administrator, yet it can improve performance by 30% or more for individual queries.

Data block prefetching can significantly improve the performance of queries which retrieve large numbers of rows via a b-tree index.Data block prefetching provides the largest performance gains for queries which use an index that is 'poorly clustered'. (An index is said to be 'clustered' if the data in the underlying table is in the same order as the index keys. A poorly clustered index is one in which the rows in the underlying table are in an entirely different order than the index keys).

Poorly clustered indexes are quite common, since most tables can only have at most one well-clustered index. Previously, accessing a large number of rows using a poorly clustered b-tree index could be expensive. Each row accessed by the index would likely be in a separate data block and thus would require a separate I/O operation.

The buffer cache is ineffective for these queries because the I/Os are spread throughout the table, and the buffer cache hit ratio is low.

In such cases, a query can easily become I/O bound, waiting for single data blocks to be read into the cache one at a time, even though there may be available I/O bandwidth on the system. With data block prefetching, 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.

요약하자면 index scan 사용할때 내부적으로 수행되는 기능으로 요구되는 rowid에 의한 access를 일정 block이 될때까지 기다려 한꺼번에 수행하는 기능 입니다.

With data block prefetching, 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.