A high CPU-consuming SQL statement can occasionally use
significant amounts of user mode CPU time for several reasons. High
usage can result in a processor bottleneck and cause a SQL query performance
slow down.
About this task
- Diagnosis
- On occasion, an individual SQL statement can use vast amounts
of user mode CPU time, which can result from a number of circumstances.
You cannot always reduce the amount of CPU time that an SQL statement
uses, but there are some cases where you can have an impact.
- Indicative signs
- A frequently run, in-buffer pool table scan can use a surprising
amount of CPU time when a small, hot table is queried or participates
in a join, but has no suitable index. Signs include the following:
- A relatively short statement execution time
- User CPU consumption equal to the execution time
- A relation scan in the explain plan
- A rising number of scans in db2pd -tcbstats
- A low number of buffer pool physical data reads for the statement
- A
rapidly increasing "Rows read" in the database snapshot.
Even though this type of statement is not usually considered
a bottleneck, the frequent execution and high CPU consumption can
make it a problem.
- Not using OPTIMIZE FOR n ROWS or FETCH FIRST n ROWS clauses in
your SQL statement can result in high CPU time consumption if the
SQL statement uses only a small portion of the rows produced.
- The use of a culturally correct collating sequence with a Unicode
code page can introduce a significant amount of overhead, particularly
in CPU consumption.
- Locking issues are often thought of only in terms of conflicts
and wait time. However, even when there are few or no conflicts, the
process of acquiring and releasing locks can use a significant amount
of CPU time. Consider an application or statement that examines many
rows in the table, but that produces few lock conflicts because it
runs on its own, either because it has exclusive access to the tables
it references, or because all concurrent applications use the table
only in read-only mode.
Note: If individual SQL statements do not seem to use the bulk
of the CPU cycles, broader potential issues can cause an overall increase
in CPU usage.
- What to monitor
- Check the information that is gathered from the database and operating
systems for the indicative signs that are mentioned earlier.
- Check these monitoring elements through an event monitor or by
way of a table function:
- total_cpu_time
- cputime_threshold_violated
If you observe one or more of the indicative signs
that are listed, then you are likely experiencing a problem with a
high CPU-consuming SQL statement. Follow the link in the "What to
do next" section to resolve this issue.
Before you begin
To
be able to objectively assess that your system is demonstrating abnormal
behavior, you must have information that describes the typical behavior
(baseline) of your system. A comparison can then be made between your
observations of suspected abnormal behavior and the baseline. Collecting
baseline data, by scheduling periodic operational monitoring tasks,
is a key component of the troubleshooting process. For more detailed
information about establishing the baseline operation of your system,
see: "Operational monitoring of system performance".
What to do next
After
you diagnose that a high CPU-consuming SQL statement is likely causing
the problem that you are experiencing, follow the link to obtain information
about the steps that can be taken to resolve the issue: Resolving high CPU-consuming SQL statement problems
DB2 Version 9.7 for Linux, UNIX, and Windows