Databases Reference
In-Depth Information
plan. If your application isn't using bind variables (discussed in
Chapter 9
), the
Oracle Database will have to parse every statement it receives. This excessive and
unnecessary parsing is one of the leading causes of performance degradation. An‐
other common cause is a shared pool that's too small, as discussed previously in the
section “The shared pool.” Keep in mind that you can avoid the creation of execution
plans by using stored outlines, as described in
Chapter 4
. And, since Oracle9
i
, you
also have the ability to edit the hints that make up a stored outline. As described
earlier, Oracle Database 11
g
and newer Oracle Database releases include the ability
to cache complete result sets, which can minimize the impact of repeated execution
of identical queries.
Database workload
If your application is well designed and your database is operating at optimal effi‐
ciency, you may experience a shortage of CPU resources for the simple reason that
your server doesn't have enough CPU power to perform all the work it's being asked
to do. This shortage may be due to the workload for one database (if the machine
is a dedicated database server) or to the combined workload of multiple databases
running on the server. Underestimating the amount of CPU resources required is
a chronic problem in capacity planning. Unfortunately, accurate estimates of the
CPU resources required for a certain level of activity demands detailed insight into
the amount of CPU power each transaction will consume and how many transac‐
tions per minute or second the system will process, both at peak and average work‐
loads. Most organizations don't have the time or resources for the system analysis
and prototyping required to answer these questions. The common solution is to
simply add more CPU resources to the machine until the problem goes away.
Nondatabase workload
Not all organizations dedicate an entire machine to an Oracle Database to ensure
that all CPU resources are available for that database. Use operating system utilities
to identify the top CPU consumers on the machine. You may find that non-Oracle
processes are consuming the bulk of the CPU resources and adversely impacting
Oracle Database performance.
Performance Tuning Basics
There are three basic steps to understanding how to address performance issues with
your Oracle Database:
1. Define performance and performance problems.
2. Check the performance of the Oracle Database software.
3. Check the overall performance of the server and storage.
