Database Reference
In-Depth Information
skip-sequential touches to an index with 20,000 leaf pages took only 13 s while
the formula above predicted
20 , 000 × 10 ms + ( 100 , 000 20 , 000 ) × 0 . 01 ms = 201 s
As the CUST table rows, as well as the index rows, are in CNO sequence, the
table touches are skip-sequential; they access on average every fifth page ; 20,000
touches from 100,000 pages, or 0.2 touches per page. Now the benefit is much
smaller and becomes dependent on how consecutive pages are stored on disk,
and how the disk server moves data from the disk drive into the read cache.
If the stripe size is 32K (eight 4K pages) and the disk server decides to
read the whole stripe to the read cache (but not the next stripes), 1.6 (0
8)
qualifying table rows are moved on average from drive to cache in one operation.
The first touch per stripe takes about 10 ms, while the next touches per stripe
take about 1 ms. The average time per touch is
1 × 10 ms + 0 . 6 × 1ms
1 . 6
.
2
×
= 7ms
The refined estimate for 20,000 skip-sequential touches is now
20
140 s
compared to the QUBE estimate of 20 , 000 × 10 ms = 200 s.
The benefit of skip-sequential would be greater in this case if the disk server
decided to read a few stripes ahead (as with sequential processing).
As this example shows, skip-sequential read actually covers the whole spec-
trum between random read and sequential read. If random read is black and
sequential read is white, skip-sequential is all shades of gray. This is particularly
useful to remember when evaluating nested-loop joins, for instance. One thou-
sand nonsequential touches, considered random in the QUBE, can be quite fast
if the touches are skip-sequential.
The principles discussed above would apply equally well to List Prefetch (and
to hybrid joins in DB2 for z/OS); the table rows are accessed skip-sequentially.
,
000
×
7ms
=
DataBlockPrefetching
As Oracle reads an index slice, it may initiate multiple synchronous reads, as we
saw in Chapter 2. Assuming data striping is in effect (as Oracle recommends),
some of these reads will take place in parallel. The actual elapsed time for
n
random touches will now be less than 10 ms. If we assume a table is striped
over three drives, the most optimistic estimate we could make for
n
random
touches would be (
n ×
10 ms)/3. The actual value would be somewhere between
this and the QUBE.
Comment
As we have seen, the contribution of
random touches to the response time can
be reduced significantly by the use of assisted random reads. Despite this, we
n
Search WWH ::




Custom Search