Databases Reference
In-Depth Information
and an extremely complex topic that can strain the ingenuity of the most proficient
database professional.
Certainly, Oracle provides more and better automated tuning options in current releases
than it did when we wrote earlier editions of this topic. These can help you better manage
contention, identify high-load SQL, tune initialization parameters, and identify prob‐
lems caused by badly designed applications. However, getting optimal performance also
relies on proper configuration of your server and storage platform. There is no substitute
for getting your hardware platform properly configured with appropriate CPUs and
cores, memory, and especially storage and I/O (throughput).
We'll begin this chapter by describing how Oracle uses system resources and how to
efficiently use these resources to gain performance. Then we'll cover some of the basic
features and tools used for performance tuning the Oracle Database.
Oracle and Resource Usage
Chapter 12
covers some of the basics of good server and storage configurations and
explains how Oracle-engineered systems provide a possible means of assuring balanced
configurations. You are more likely to run into performance issues and face difficult
tuning challenges if inadequate server and storage resources are available to the Oracle
Database. Problems such as those caused by inadequate I/O, CPUs and CPU cores, or
memory are still common today where unbalanced configurations are deployed. Where
data is being accessed in a federated fashion across multiple servers, network bandwidth
can also play a part in performance challenges. However, most organizations avoid this
problem by minimizing use of such federated strategies.
Network bandwidth can become a concern when using your Oracle
Database to retrieve very large data sets over the network. Although
you can't typically surmount this type of problem simply by improv‐
ing the performance of your Oracle Database, you can monitor
network and application server bottlenecks with Oracle Enterprise
Manager.
If your Oracle Database is not properly designed and configured, you could also have
more fundamental problems. We will next focus on how Oracle uses the three key
machine resources: CPU, memory, and disk I/O. The slowest access is to disk and, as a
result, the most common database performance issues are I/O-related. The majority of
this section therefore focuses on performance as it relates to physical disk I/O.
A database server may experience bottlenecks caused by contention for multiple re‐
sources at the same time. In fact, where platforms are managed such that one resource
will try to compensate for the lack of another resource, there is sometimes a deficit in
