Databases Reference
In-Depth Information
high CPU use, drill down to individual processes to determine if any one
application is using more than its fair share of CPU cycles. If so, look more
closely at how that application is designed and coded as described in “Tuning
Your Application or Database Driver to Minimize CPU Use,” page 114.
•
How many processes or threads are waiting to be serviced in the CPU's
run queue?
A single queue is used for CPU requests, even on computers with
multiple processors. If all processors are busy, threads must wait until CPU
cycles are free to perform work. Processes waiting in the queue for sustained
periods indicate a CPU bottleneck.
•
What is the rate that processes or threads are switched by the operating
system to perform work for other waiting threads?
A
context switch
is the
process of storing and restoring the state (context) of a CPU so that multiple
processes can share a single CPU resource. Each time the CPU stops running
one process and starts running another, a context switch occurs. For exam-
ple, if your application is waiting for a row lock to release so that it can
update data, the operating system may switch the context so that the CPU
can perform work on behalf of another application while your application is
waiting for the lock to release. Context switching requires significant proces-
sor time, so excessive context switches and high CPU use tend to go hand in
hand.
For information about tools that can help you troubleshoot CPU use, see
Chapter 10, “Troubleshooting Performance Issues.”
Tuning Your Application or Database Driver to Minimize CPU Use
Here are some general guidelines to help your application or database driver to
minimize CPU use:
•
Maximize query plan reuse
—When a new SQL statement is sent to the
database, the database compiles a query plan for that statement and stores it
for future reference. Each time a SQL statement is submitted to the database,
the database looks for a matching SQL statement and query plan. If a query
plan isn't found, the database creates a new query plan for the statement.
Each time the database creates a new query plan, it uses CPU cycles. To max-
imize query plan reuse, consider using statement pooling. For more infor-
mation about statement pooling, see “Using Statement Pooling,” page 236.
•
Ensure connection pools and statement pools are tuned correctly
—
Pooling can conserve CPU if tuned correctly, but if not, your pooling envi-
