Database Reference
In-Depth Information
A spike like this was found to be a database integrity check run once a day by
a user. According to the programmer, the number of SQL calls could have been
reduced by adding predicates to a WHERE clause; this was not considered to be
worth the effort because the long response time was not really a problem for the
only user of the transaction. To avoid unnecessary queuing for other concurrent
transactions, it was decided that the transaction should run early in the morning!
In general, transactions with a high number of SQL calls, say more than
10,000, are hard to improve if the CPU time (SQL CPU and non-SQL time) is
the largest component of the local response time.
The tuning potential is the number of SQL calls eliminated multiplied by the
base CPU time per SQL call (e.g., 0.1 ms).
Victims
A small table once caused spikes like the one shown in Figure 7.10. The table
contained a few counters that were used to collect statistics. The rows were
updated by many transactions. These updates were often made early in the pro-
gram, usually unnecessarily early; that is to say, they were not done close to
the commit point. Sometimes the transactions updating the counters were some-
what slow, perhaps because of disk drive queuing. Consequently, a row could be
locked for a few seconds, causing other transactions needing to update the row
to wait.
Lock implementation varies considerably from one DBMS to another, but
lock waits tend to be application design problems. The first thing to be done is
to find the hot resource—the row or page that causes lock waits. If the DBMS
provides a lock wait trace, it is quite easy to identify the resource by running
the trace for the program suffering from lock waits. An alternative approach
LRT
SQL calls
62
9 s
SQL
Non-SQL
9 s
0 s
9 s
0 s
0 s
0 s
0 s
Wait for
prefetch
CPU time
Sync
read
Lock
waits
Other
waits
Figure 7.10 Victim.
 
Search WWH ::




Custom Search