Database Reference
In-Depth Information
Because the hash table activities (building the table followed by 8000 ran-
dom touches) do not contribute nearly as much to the CPU time as the 4 million
sequential touches, we cannot estimate the hash table CPU time from this mea-
surement. We can only speculate, based on the above discussion about the CPU
cache, that it could be as low as a few microseconds per random touch.
With a merge scan, the 8000 random touches to the hash table would be
replaced by a sort of 13,000 rows (CPU time estimate 13
,
×
=
.
1s)
followed by a merge of 5000 rows and 8000 rows (CPU time estimate 0.1 s).
Merge scan would use more CPU time than hash join in this case if the random
touches to the hash table were to take less than 25 µ s(X < 25).
The situation is different when the hash table is large and when many con-
current programs are sharing the high-speed CPU cache. Let us assume a 10%
filter factor for both local predicates, CUST and INVOICE. Now the size of the
hash table could be 100 , 000 × 100 bytes = 10 MB. With a 1-MB CPU cache
shared by many concurrent users, cache hits would be rare. The CPU time per
random touch could be as high as 50 µ s (as a random table touch without I/O)
at peak times. Then 400,000 random touches to the hash table could consume
400 , 000 × 50 µ s = 20 s. Now merge scan would be a better choice because
the CPU time estimate for sorting and merging 100 , 000 + 400 , 000 rows is
2 × 500 , 000 × 10 µ s = 10 s. The difference increases if the hash table becomes
so large that the optimizer decides not to place it all in memory at one time;
it will then break it into partitions; with 10 partitions, the qualifying INVOICE
rows would be scanned 10 times.
The choice between hash join (if available) and merge scan is a problem that
the optimizer must try to resolve; the high-speed CPU cache makes it difficult
for the optimizer to predict the CPU time of a hash join.
This is why the products that provide both alternatives make hints available
that enable us to make the decision. From the index design point of view, as
we mentioned in Chapter 8, the only difference between HJ and MS is the sort
avoidance consideration available with MS.
On the other hand, the choice between indexes designed for NLJ vs. MS/HJ
is a different consideration. Especially with non-BJQ queries, MS/HJ often has
a lower elapsed time but a higher CPU time .
000
10
µ
s
0
Skip-Sequential
We suggested earlier that one way to easily determine the CPU time per random
touch is simply to compare the CPU time before and after unproductive random
touches are eliminated with semifat or fat indexes.
We also discussed the issue of skip-sequential reads earlier in this chapter,
noting that:
If random read is black and sequential read is white, skip-sequential is all
shades of gray.
Search WWH ::




Custom Search