15 Database Tuning

How do I…

15.1 Run the UTLBSTAT and UTLESTAT scripts?

15.2 Identify unbalanced I/O and I/O contention?

15.3 Stripe a tablespace across multiple devices?

15.4 Determine shared pool performance?

15.5 Determine the buffer cache hit ratio?

15.6 Assess the impact of adding database buffers?

15.7 Record and save performance statistics over time?

15.8 Identify rollback segment contention?

15.9 Determine the best size for the redo log buffer?

15.10 Measure and improve checkpoint performance?

15.11 Measure and improve overall sort performance?

15.12 Detect contention for the database buffer cache?

15.13 Detect and resolve lock contention?

Acceptable application performance begins with an optimized database design and with tuned component SQL statements. These measures are well documented and are certainly more science than art. Ensuring that an application operates at peak performance, though, requires review of the multitude of configurable parameters that Oracle8 offers and this is where tuning begins to be more art than science. This chapter addresses some of the most common tuning issues and provides general guidelines for healthy database operation. But this is not a tuning book and the treatment presented here is not exhaustive. After addressing database design and application SQL statements, most tuning methodologies focus on tuning memory, input/output, and contention. The How-To’s in this chapter adhere to this time-tested approach. The files used in this chapter are found on the accompanying CD under the \SAMPLES\CHAP15 directory. Copy these files into your SQL*Plus working directory, or specify the complete path when executing the START command within SQL*Plus.

Note - All of the How-To’s in this chapter are designated as “Advanced” in complexity. Database tuning requires DBA privilege levels, and only those users with an in-depth knowledge of database operation should attempt to implement the guidelines in this chapter.

The market provides many tools for Oracle performance monitoring; some of these, like the Performance Pack in Enterprise Manager, are offered by Oracle Corporation. The appearance of the command line interface shown in this chapter may not compare favorably to the graphical interfaces featured in other products but if you really want to understand Oracle performance issues, the data dictionary is the place to start. The graphical tools isolate users from a thorough understanding of the data dictionary. The irony is this: you should only use the graphical tools if you don’t really need them. Otherwise, check out the dynamic performance tables and the statistics report to nurture an in-depth understanding of what’s happening behind the scenes.

15.1 Run the UTLBSTAT and UTLESTAT Scripts

Oracle supplies two scripts, often referred to as the statistics scripts, which provide a fairly comprehensive overview of database performance. Experienced database tuners often start their analysis with these scripts. This How-To describes the scripts and their use, presents some tips for running them efficiently, and introduces the scripts’ output. Many of the other How-To’s in this chapter will refer to the report produced by the UTLESTAT script.

15.2 Identify Unbalanced I/O and I/O Contention

Most database servers can simultaneously access multiple disks and most database architects configure a database’s physical files to reside on multiple disks. If one disk performs too much of the work while other disks are idle, then system performance is not maximized. This How-To covers the process for identifying I/O imbalance and mentions some guidelines for identifying I/O contention.

15.3 Stripe a Tablespace Across Multiple Devices

A tablespace can consist of multiple datafiles, which do not have to be located on the same device. If users and applications heavily access a tablespace, you may be able to improve performance by spreading (striping) the tablespace over multiple I/O devices to reduce contention. This How-To presents a method for creating a new tablespace spanning multiple devices and a method for modifying an existing tablespace by adding striped datafiles.

15.4 Determine Shared Pool Performance

The shared pool contains the data dictionary cache and the library (or SQL) cache. If the shared pool is too small, Oracle8 may need to go to the disk for information that should be in memory. Information about users, tables, indexes, privileges, and other data dictionary objects is part of the data dictionary cache. The most recently parsed and executed SQL statements are located in the library cache. Oracle8 needs much more time to read this information from disk than from memory and database performance can suffer greatly if the shared pool is not large enough. This How-To presents a method to determine the hit ratio for objects in the shared pool.

15.5 Identify the Database Buffer Cache Hit Ratio

The database buffer cache contains the database blocks most recently read. When a user or application requests data from the Oracle server, Oracle looks for the requested data block in the buffer cache. If Oracle is less than 70% successful when it tries to find data in the buffer cache, then you should consider increasing the size of the buffer cache if system memory permits. This How-To presents a method to determine the buffer cache hit ratio.

15.6 Assess the Impact of Adding Database Buffers

You have used the analysis in the preceding How-To and discovered that increasing the size of the database buffer cache is justified. In this case, it would be useful to know what kind of improvement in the buffer cache hit rate we could expect if we add some number of additional buffers to the cache. Oracle has provided a method to do just that and this How-To guides you through the process.

15.7 Record and Save Performance Statistics Over Time

It is fair to say that DBAs have a service contract with the users who depend on the database. One way to be sure that the terms of that contract are upheld is to refer to the historical performance of the database. Many tools, and indeed the techniques in this chapter so far, can summarize database performance right now, but do not provide a way of populating performance history. This How-To provides a method of tracking a few of the most important performance indicators, the cache hit rates, over time.

15.8 Identify Rollback Segment Contention

The Oracle8 server uses rollback segments primarily to allow users and applications to undo uncommitted database changes. Rollback segments also serve to provide a read-consistent view of the database in a multi-user environment. Oracle8 writes changed records to the rollback segments for all uncommitted transactions. If the database does not contain enough rollback segments for the system load, contention for rollback segments can reduce system performance. This How-To presents a method to identify and correct rollback segment contention within the database. This section also briefly considers some other common rollback segment problems.

15.9 Determine the Best Size for the Redo Log Buffer

Oracle redo log files are crucial to recovery operations. The redo log buffer is an area in the SGA where all Oracle redo data resides before the LGWR (Log Writer) process writes it to the physical redo log files. Insufficient space in the redo log buffer can degrade performance. This How-To discusses methods for determining if user processes are waiting for redo log buffer space.

15.10 Measure and Improve Checkpoint Performance

The Oracle server heavily utilizes memory as a temporary storage medium. Because memory is volatile, the Oracle background processes that comprise an Oracle instance copy data from memory to disk on a regular basis. Checkpoints are a particularly thorough example of this behavior and can degrade performance if ill-behaved. This How-To focuses on analyzing checkpoint performance and suggests ways to improve it.

15.11 Measure and Improve Overall Sort Performance

Sorting is a resource intensive database operation. The Oracle server provides many parameters that database users can change in an attempt to tune sorts, but the most important sort performance goal is to maximize the number of sorts Oracle performs in memory versus the sorts Oracle performs on disk. This How-To explains a method to compare memory sorts and disk sorts and provides some suggestions about improving this ratio if possible.

15.12 Detect Contention for the Database Buffer Cache

The best indicator of database buffer cache performance is the hit ratio discussed in How-To 15.5. It is possible, however, that contention for the database buffer cache could degrade database performance. This How-To focuses on determining the presence and cause of database buffer cache contention.

15.13 Detect and Resolve Lock Contention

Oracle provides safe data concurrency through locking mechanisms. There is no question that locks will cause contention; that is, after all, what they are designed to do. The kind of contention that DBAs and developers want to avoid is unnecessary lock contention. In almost all situations, Oracle’s default locking mechanisms are satisfactory. When Oracle suffers from locking problems, it is often because a user or application has either requested an unnecessary lock or has failed to manage transactions properly. This How-To will not review all possible DML and DDL lock situations. It goes right to the bottom line and shows you how to determine who holds a lock that is blocking some other user and what you can do to resolve the situation.