Database Reference
In-Depth Information
LRT
SQL calls
1003
Pages read with
list prefetch
31,836
104 s
102 s / 31,836
= 3 ms
SQL
Non
-SQL
104 s
0 s
2 s
0 s
102 s
0 s
0 s
Wait for
prefetch
CPU time
Sync
read
Lock
waits
Other
waits
Figure 7.6
Promising culprit—skip-sequential reads.
LRT
SQL calls
1003
Pages read with
sequential prefetch
112,721
16 s
16 s / 112,721
= 0.14 ms
SQL
Non
-SQL
16 s
0 s
14 s
0 s
0 s
0 s
2 s
Wait for
prefetch
CPU time
Sync
read
Lock
waits
Other
waits
Figure 7.7
Promising culprit—sequential reads.
SequentialReads
The spike report shown in Figure 7.7 shows an application that could be scanning
an entire table (112,721 pages read using sequential prefetch). Because there is
no I/O wait time, the program is CPU bound. The 2-s Other Waits figure might
possibly be due to CPU queuing.
At first sight, the spike in Figure 7.7 may look like an unpromising culprit.
It is easy to think of cases in which 1000 SQL calls take 16 s, even with good
access paths; for instance, 1000 singleton SELECTs that cause, on average, two
random reads. However, the biggest component is now CPU time, not I/O time.
Search WWH ::
Custom Search