Oracle High-Performance Tuning with STATSPACK

by Donald Keith Burleson, Don Burleson

Overview

From the official Oracle Press comes a comprehensive guide to using STATSPACK—Oracle's powerful tuning utility. Written by a database management expert,this authoritative resource explains how to collect,interpret,and correct Oracle performance problems using STATSPACK.

Monitor and tune your Oracle database using STATSPACK

Use the Oracle STATSPACK utility as the foundation for a comprehensive Oracle tuning environment. Officially authorized by Oracle Corporation,Oracle High-Performance Tuning with STATSPACK explains how to use this powerful tool to diagnose—and optimize—system performance. You'll get full details on server,network,and disk tuning,as well as instance,object,and SQL tuning. Plus,the book contains ready-to-use STATSPACK scripts throughout. Make informed performance and tuning decisions for your system based on the data you collect with Oracle STATSPACK.

• Install and configure STATSPACK for peak performance
• Extend STATSPACK to capture server statistics
• Tune the server environment,including the CPU and RAM
• Configure your Oracle database for better network performance
• Monitor and tune the disk I/O subsystem
• Detect and correct instance performance problems
• Tune Oracle database tables and indexes
• Locate and tune SQL statements
• Tune with Oracle parallel query and parallel DML
• Perform trend analysis with STATSPACK data

Product Details

ISBN-13:	9780072133783
Publisher:	McGraw-Hill Companies, The
Publication date:	09/01/2003
Series:	Oracle Press Series
Pages:	674
Product dimensions:	7.42(w) x 9.14(h) x 1.60(d)

About the Author

Donald K. Burleson is a top Oracle Database expert, with 20 years of full-time experience. He specializes in creating database architectures for very large online databases, and he has worked with some of the world's most powerful and complex systems. A leading database author, Don has written 12 books, published more than 60 articles in national magazines, and serves as editor-in-chief of Oracle Internals, a leading Oracle database journal.

Read an Excerpt

Chapter 6: Tuning the Server Environment

It should now be clear that the tuning of the server environment is a prerequisite to tuning the Oracle database. As we repeatedly demonstrated in earlier chapters and in our discussion in Chapter 5, no amount of Oracle tuning is going to help a performance problem if the CPU or lit memory on the server is experiencing an overload or a bottleneck.

When tuning an Oracle database server, we must always remember the goal of fully loading the CPUs and RAM on the server. Unused processing and RAM power can never be reclaimed, and with the significant depreciation of the value of most servers, maximizing the utilization is a noble goal. On any server that is dedicated to an Oracle database, we want to dedicate as much hardware resources to Oracle as possible without causing a server-related slowdown. This chapter will look at the details of Oracle database server tuning and will be divided into several sections:

• Online server monitor tools This will look at ancillaries to vmstat and will show the top and sar utilities to see details about CPU and memory activity.

• Tuning CPU consumption This will describe the basic causes of CPU overload and explore remedies for excessive CPU consumption. When you experience a shortage of CPU, you will need to add more processors or reduce the amount of work on the database server.

• Tuning memory consumption This section will explore basic memory management and show some tricks for partitioning memory on the server. When an Oracle server runs short of real memory, segments of RAM are swapped out to a swap disk. Such page-out operations happen frequently, but a page-in indicates that the Oracle server is exceeding the amount of RAM memory. The usual remedies for swapping are to reduce the size of the SGA and/or to buy more RAM for the database server.

• Reporting on server statistics This section will look at some handy scripts that will alert you to server exceptions and show you how to create trend and usage reports for your server.

Let's begin with a review of some online tools for server monitoring. We already, covered vmstat in the last chapter, but we also need to understand the glance, top, and sar utilities.

The Relationship Between the Database Administrator and the Systems Administrator

In many Oracle shops, computer professionals called systems administrators are responsible for the setup, configuration, and tuning of the Oracle database server. The abilities of system administrators vary widely, from excellent support and cooperation to neophyte support and complete noncooperation.

Because of the tight coupling between database performance and server performance, it is imperative that the Oracle DBA have access to the tools that we describe here. By themselves, the systems administrators will not have enough information about what is happening inside the database to properly tune the server. Conversely, the DBAs cannot get the information they need to properly configure Oracle if they cannot get access to the server monitor utilities.

Many Oracle shops give root access to the DBA so they will have full control over their database server. The system administrator continues to be responsible for the configuration and system software on the server, but the DBA accepts responsibility for setting the kernel parameters and managing the interface layer between Oracle and the operating system environment.

A small minority of shops will restrict access to the root account, but these shops will provide the Oracle DBA with access to all of the server monitor tools and system utilities. In either case, it is imperative that the Oracle DBA have access to the system monitor tools.

Online Server Monitor Tools

Before we address the specific tuning techniques for your database server, let's begin with a brief tour of several online tools that can help us tune the server. There are many companies that provide third-party server performance monitors, but there are several "freeware" server monitors that can help you see what is happening on the server.

Using glance

The glance utility is provided on HP/UX systems to provide a graphical display Of server performance. Glance displays current CPU, memory, and disk and swap consumption, and also reports on the top processes. The glance utility is involved by entering glance from the UNIX prompt. The name for glance is quite appropriate because it gives you a complete glance at the whole server. Figure 6-1 shows an example glance screen.

The histograms at the top of the glance screen show the amount of consumption on CPU, disk, memory, and swap. The output within each histogram shows the high-water mark, and the amount of the resource in system and user mode. For example, from Figure 6-1 we see that this server is only using 4 percent of the CPU capacity, but the RAM memory is at 90-percent capacity.

The second part of the glance utility shows the most intensive tasks on the database server. The glance screen in Figure 6-1 was for an Oracle server running Oracle Applications, and we see that the Oracle Forms 4.5 runtime task (f45runw) is the most intensive task on the server. For each task displayed, we also can see the amount of disk I/O currently being consumed by the task. The glance screen will refresh every few seconds, so we get a continual picture of the load on the Oracle server. To exit glance, you enter CTRL-C. Now let's look at another server monitor tool that is quite similar to glance....

Table of Contents

Part I: Overview of the Method and the Tools

Chapter 1: Overview of Oracle Tuning

Chapter 2: Overview of STATSPACK

Chapter 3: Installing and Configuring STATSPACK

Chapter 4: Data Inside the STATSPACK Tables

Part II: Tuning the Oracle Database with STATSPACK

Chapter 5: Extending STATSPACK to Collect Server Statistics

Chapter 6: Tuning the Server Environment

Chapter 7: Tuning the Network Environment

Chapter 8: Tuning the Disk I/O Subsystem with STATSPACK

Part III: Tuning the Oracle Database with STATSPACK

Chapter 9: Tuning the Oracle Database Instance

Chapter 10: Tuning Oracle Tables and Indexes

Chapter 11: Tuning Oracle SQL

Chapter 12: Tuning with Oracle Parallel Features

Chapter 13: Tuning the Oracle Parallel Server Environment

Part IV: Database Reporting with STATSPACK

Chapter 14: Monitoring Oracle with STATSPACK

Chapter 15: Trend Analysis with STATSPACK