Oracle does not run in a vacuum. The
performance of Oracle databases depends heavily on external
considerations, namely the Oracle server, disk, and network. The
first tasks when tuning a database are to identify the external
bottleneck conditions, which may include:
: A
shortage of CPU cycles can slow down SQL and whenever the run queue
exceeds the number of CPUs on the Oracle server (in the absence of
high idle times), the system is said to be CPU-boundCPU consumption
can be reduced by a variety of methods (tuning SQL, reducing library
cache contention), but a CPU shortage may indicate a need to add
more, or faster, processors to the Oracle server.
The
amount of available RAM memory for Oracle can affect the performance
of SQL, especially in the regions that control the data buffers and
in-memory sorts and hash joins.
:
Large amounts of Oracle*Net traffic contribute to slow SQL
performance.
Disk
bottlenecks can be identified by the fact that updates are slow due
to channel contention, such as using RAID5 for high update systems.
There are several simple items that can be
monitored when checking the external Oracle environment:
:
When the number of run queue waits exceeds the number of CPUs on the
server, the server is experiencing a CPU shortage. The remedy is
the addition of CPUs on the server or the disabling of high CPU
consumers such as Oracle Parallel Query.
When RAM
page-in operations are noted along with a prior increase in
scan-rate, the non-virtual RAM memory has been exceeded and memory
pages are moving into RAM from the swap disk. The remedy for
excessive wrapping is to add more RAM, reduce the size of the Oracle
SGAs, or turn-on Oracle's multi-threaded server.
Enqueues on a disk device may indicate that the channels are
saturated or that the read-write heads cannot move fast enough to
keep up with data access requirements.
:
Volume-related network latency may indicate either the need to tune
the application so that it will make fewer requests. High latency
may also indicate a need for faster network hardware.
and include the URL for the page.
