Server Tuning 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 environment.

Network Tuning

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 Oracle network

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.

Disk Tuning

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 disk I/O

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.