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

hour), it may be necessary to know how pessimistic the QUBE is for the cheaper
alternative.
We will now discuss the three most important occasions when the actual
response time can be much shorter than the QUBE .

Leaf Pages and Table Pages Remain in the Buffer Pool

The cost of a random touch is 10 ms, according to the QUBE. This would be
close to the truth if all leaf and table pages were accessed randomly. With 100
million leaf and table pages in a database, 400 GB if the page size is 4K, and
an access rate to these pages of 10,000 per second, the average time between
accesses to each page would be
100 3h

In real applications, of course, some pages will be accessed much more often
than the average page.
To simplify the discussion, let us call the combination of the database buffer
pool in memory and the read cache in the disk server, the pool . A randomly read
page may stay in the pool, say, for 10 min if it is not accessed. This means that
a page that is accessed at least once every 10 min stays in the pool.
When is a leaf page or a table page accessed this often?

, 000 , 000 / 10 , 000 = 10 , 000 s =

SmallTables

If a table has 100 equally popular pages and the table is accessed once a second,
the average time between accesses per page is 100 s. If the pool residency time
is 10 min, it is reasonable to assume that the table pages, as well as the leaf
pages of the active indexes, will stay in the pool.

HotSpots

A train seat reservation system held data for a period of 90 days. The main table
was clustered by departure date. As most people reserved a seat on the day of
departure, the last 1% of the table pages, as well as the last 1% of the clustering
index, tended to stay in the pool. This eliminated many random reads.
When is a hot spot hot enough? If the pool residency time is 10 min ,the
average time between accesses to a page should be no more than 5 min to take
any variation into account. Thus, if there are 10 page accesses per second to the
train seat table, the hot spot area , trains leaving today, should not be greater than
300 s × 10 pages/s = 3000 pages.

Comebacks
A program may do a random touch to a page several times. Is it likely that the
page will still be in the pool? The answer will be yes if the time between accesses