Additional Estimation Considerations - Relational Database Index Design and the Optimizers - page 272

TR = 1
TS = 1M

TR = 1
TS = 5M

P,(C)

P, C

CNO PNO CNO,...

TR = 1
TS = 1M

TR = 5M

CUST POLICY

1,000,000 rows 5,000,000 rows

LRT = 5M × 10 ms + 7M × 0.01 ms + 5M × 0.1 ms = 14h

Figure 15.3 Batch program— comebacks.

is less than the pool residency time. This is why the pool residency time is very
critical for massive batch jobs with many random touches.
Let's consider the batch job we discussed in Chapter 11. If the clustering
index of table POLICY is index PNO as shown in Figure 15.3, there could
be five million random touches to the table. Five million random touches take
5 , 000 , 000 × 10 ms = 50 , 000 s; 14 h, if every touch results in a random read
from a disk drive. Let us assume that the size of the POLICY table is 1 GB.
If the pool is significantly larger than 2 GB, each page in table POLICY is
read from disk only once: 200 , 000 × 10 ms = 2000 s; 34 min. The remainder
of the random touches, which now find the row in the pool, may each take
0.1 ms: 4 , 800 , 000 × 0 . 1ms = 480 s; 8 min. Consequently, the time taken by
the 5 million random touches is reduced from 14 to 42 minutes!
As the policy table has 250,000 pages, an average page is accessed 20 times.
Thus, the average time between accesses to a page will be 2 min if the elapsed
time of the program is about 40 min. The pool residency time must be at least
2 × 2min = 4 min to ensure that it is unlikely that a page will be read more
than once from disk.

ResidentTablesandIndexes
Each page is read only once from disk after a system restart. The pages in the
special pools are never overlaid if the pool is sized correctly; they remain in
memory as long as the system stays up.