Oracle Database Tips by Donald Burleson
Many Oracle professionals discount the server environment because they have not
been trained to understand how the Oracle database interacts with the server. As
a practicing Oracle tuning consultant, I have learned that the external
environment is the very first thing an Oracle DBA should take a look at when a
performance problem has been reported. When checking the external server, the
Oracle administrator must carefully check the settings for the operating system
kernel parameters on the database server and carefully monitor the usage of RAM
memory and CPU on the database server (see Figure 1-6). If the database server
is experiencing disk or network bottlenecks or shortages of hardware resources,
no amount of Oracle tuning can alleviate the problem.
Figure 6: Detailed Oracle server tuning
Once we understand the basic nature of CPU and memory
consumption on the server, we will turn our attention to extending the STATSPACK
utility in order to capture server information. We will show how the UNIX
vmstat utility can be extended in order to capture information regarding the
usage on each CPU and memory within the computer, and to show how this
information can be stored inside a STATSPACK table. We will also show how alert
reports can then be generated from the STATSPACK table, such that the Oracle
administrator becomes aware of all server-related problems that will impact the
Oracle database performance.
We will also show how this extension of STATSPACK can be
used in order to monitor other servers in an Oracle enterprise environment. We
will show how this remote vmstat collection utility can be used to monitor the
behaviors of Web servers and application servers in an Oracle database
With many Oracle databases shared across geographical
areas, it is very important for the Oracle professional to recognize the
importance of network communications on the performance of their databases. As
you may know, Oracle provides for distributed communications between databases
by using its Transparent Network Substrate (TNS). The TNS is a
distributed protocol that allows for transparent database communications between
remote systems. The TNS acts as an insulator between Oracle's logical request
for data and the physical communications between the distributed servers.
Because of this insulation between the Oracle logical data request and the
internal workings of the network, much of the network performance tuning is in
the hands of the network administrator. In other words, the Oracle administrator
has very little direct control over the network configuration settings that can
affect the overall performance of their database (see Figure 1-7).
Figure 7: Tuning the
However, there are some important settings that can be
used in order to improve the performance of distributed transactions. This
chapter will take a careful look at the init.ora parameters that relate
to distributed communications, and also take a look at some of the TCP
parameters such as tcp.nodelay, which can be used in order to change the
basic packet-shipping mechanisms within the Oracle database.
We will also take a look at different parameters within
the sqlnet.ora, tnsnames.ora, and protocol.ora files, which
can be used to change the size and configuration of the TCP packets. These tools
can often have a profound impact on the behavior of the underlying network
transport layer and improve throughput of all Oracle transactions.
The largest single component of Oracle response time is
disk I/O (input/output). Anything that the Oracle DBA can do to reduce disk I/O
will have a positive benefit on the performance of the database. The reduction
of disk I/O is a goal that will be mentioned in each of the chapters. For
example, changing Oracle initialization parameters can reduce disk I/O and
tuning SQL can also greatly reduce disk I/O.
Once we recognize that disk I/O is the single most
important factor in tuning any Oracle database, it is understandable that the
Oracle DBA needs to fully understand the internal operations of the disk I/O
subsystem. In this day and age of using disk cache storage devises such as EMC,
many Oracle DBAs are not always paying careful attention to the interaction
between the Oracle database and the disk I/O subsystem (see Figure 1-8). Another
confounding issue is that the disk arrays often have a separate RAM cache, and
an I/O request from Oracle does not always translate into a physical disk I/O.
Figure 8: Tuning Oracle
Because there are many dozens of ways to configure a disk
I/O subsystem, Chapter 8 will focus on all of the common disk placement
techniques, the use of RAID, and how the Oracle DBAs can ?map? their disks, from
the logical mount points to the physical disks. Once the mapping between the
Oracle datafiles in the physical disks is clearly understood, the Oracle
professional can then move on to create tools that will monitor the behavior of
the disk I/O subsystem, and relocate files to overcome disk I/O bottlenecks.
In keeping with the theme of using STATSPACK, Chapter 8
will show how the Oracle DBA can extend the STATSPACK tables in order to capture
I/O statistics using the generic UNIX iostat utility. Once the iostat utility is
in place and constantly monitoring the behavior of the disk, the Oracle DBAs can
create automated exception reports that will alert them to times when the disk
I/O subsystem is undergoing contention.
Chapter 8 also goes into detail on what the Oracle DBA
can do to remedy disk I/O bottlenecks and includes discussions on file striping
and other RAID techniques that are used by the Oracle professional in order to
ensure that the load is evenly balanced among all of the physical disk spindles.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.