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

due to changes in the workload, or sometimes (fortunately not very often) because
of the index changes.
How far down the list, sorted by ET, would it be necessary to go? In an
operational system response times exceeding 1 s should be rare. Ideally then,
we should check any SQL call that sometimes takes more than 200 ms. Unfor-
tunately, in real life, we may never be able to do this. So many SQL calls, so
little time!
The terms and criteria we have discussed above are summarized in Figure 7.11.
In order to illustrate how they may be used, two examples of call-level monitoring
will be provided shortly, one each for Oracle and SQL Server.
This approach will reveal many serious access path problems with only
minimal effort. Why then, do we claim that LRT-level exception monitoring
may be preferable to call level?
First, a report showing the worst SQL calls does not reveal problems with
fairly fast calls that are executed many times per transaction. Consider, for
instance, a singleton SELECT executed 500 times in a loop. If the access path
is not index only, the average time per call may be 20 ms with two random
reads. Few analysts would be concerned about an SQL call taking 20 ms, but
most would be very concerned about a transaction taking 10 s (500 20 ms)!

Adding any missing columns to the index would cause the contribution of the
SELECT to the overall LRT to be reduced from 10 to 5 s.
Second, LRT-level exception monitoring reveals all response time problems,
not just those related to slow access paths. It also gives an early warning of
problems that are beginning to be significant as databases grow or transaction
rates increase.

×

Oracle Example

In the Oracle 9i exception report, the numbers shown below are reported for each
slow SQL call—the N slowest calls in a measurement period, for instance.

Statistics
------------------------
0 db block gets
1021 consistent gets
1017 physical reads
CPU time

0.031250 s

Elapsed time 6.985000 s

Consistent gets represent the number of blocks requested in consistent mode
and db block gets is the number of blocks requested in current mode. Blocks
must be requested in current mode in order to be changed. Together these two
values comprise the total page requests (PR) for the SELECT.
Physical reads represent the number of database blocks that the database
instance has requested the operating system to read from disk.