Reactive Index Design - Relational Database Index Design and the Optimizers - page 113

LRT

Synchronous read
50,342

SQL calls
1003

× 9 ms

455 s

= 453 s

Indexes:
Tables:

11
50,331

SQL Non-SQL

455 s 0 s

2 s 453 s 0 s 0 s 0 s

Wait for
prefetch

CPU time Sync

read

Lock
waits

Other
waits

Figure 7.4 Promising culprit.

ž Synchronous reads are the major cause (453 s) of the excessive local
response time (455 s).
ž The average time for a synchronous read is fairly short (9 ms); disk queu-
ing is probably not significant.
ž Almost all the synchronous reads access table pages (50,331).

The expected average time for a synchronous read depends on the read cache
hit ratio. If the read cache is much larger than the database buffer pool, say 100:1,
the expected value for the average of the cache reads and the drive reads could
be as low as 3 ms. If that had been the case in this example (9 ms synchronous
read time), the drive queuing time would be significant; reducing the number of
synchronous reads would still, however, make a big difference. If a disk drive is
seriously overloaded, perhaps due to a write burst, the average synchronous read
time for a transaction could be hundreds of milliseconds. Then the transaction is
clearly a victim.

AnalyzingPromisingCulprits

A promising culprit reads many table pages. We know that there are three ways
to read table pages:

1. Traditional random read (synchronous read, SR)
2. Skip-sequential read
3. Sequential read (sequential prefetch)

We can deduce from the spike report which of these three methods was
largely used by the promising culprit to read the table pages from disk.