[Laskey99] Chapter 3. Maximizing Oracle Performance

Chapter 3.

If you ask aroom full of Oracle database administrators, "What is thesingle biggest part of your job?", chances are that the almostuniversal response will be "Configuration and tuning of thedatabase." Oracle is a very complex and powerful product, andpart of its power lies in its ability to get the best possibleperformance out of each individual database configuration. Thischapter presents our approach to the configuration and tuning of anOracle database and provides guidelines for implementing ahigh-performance database at your site.

The ongoing, day-to-day responsibility of most Oracle DBAs is to getthe best possible performance from the Oracle database. A number ofdefinitions may be offered for "performance," but wedefine performance as the objective, measurable amount of timerequired to perform a typical operation in the database in question.Yes, this is a simplistic definition that ignores other metrics likeresource utilization, but let's face it: the database isexpected to be as fast as possible, so this is areasonable definition for this purpose.

Entire books have been written on the subject of Oracle performance(see the appendix, Resources for theDBA, for some we consider worthy of yourattention^[1]), so we cannot hope to address all theintricacies of Oracle performance tuning in a single chapter. Rather,we hope to document a straightforward approach to performance tuningand to provide some practical guidelines that can be applied to avariety of installations.

^[1] We particularly recommend Mark Gurry andPeter Corrigan's Oracle PerformanceTuning, Second Edition (O'Reilly & Associates,1997).

It is important to realize that every Oracle installation isdifferent in terms of its physical and logical databaseimplementation, the types of transactions processed, and theperformance requirements for those transactions. As a result, therecannot be an automatic tuning methodology, although several vendors,including Oracle, have attempted to provide one. Nor can a single setof rules provide a method for maximizing database performance. Whatcan be provided, however, is a methodology that, when properlyapplied and combined with the knowledge and experience of a workingDBA, will result in good performance for any given database.

3.1. Configuration and Tuning—What's the Difference?

Getting maximumperformance from an Oracle database requires careful attention toboth configuration and tuning of the database. These terms are oftenused interchangeably, but in reality, they are two differenttasks—admittedly, with a bit of grey area between them.

Configuration is the process of setting up thephysical and logical components of the database and its host systems,while tuning is the process of modifying theinternal behavior of the database so that operations are performed ina particular manner. The entire process can become somewhat circular,since proper tuning often includes modifying the configuration, whichthen requires another look at tuning. Figure 3.1shows the basic steps in the configuration and tuning process.

Figure 3.1. The configuration and tuning process

3.1.1. What Can Be Configured?

Some items that can be configured in an Oracle database are thefollowing:

• Components of the database that affect the allocation of system processes, such as:

  SQL*Net

  Multi-Threaded Server (MTS)

  Parallel Query

  Parallel Server

• The layout and sizes of physical storage

• The sizing of database objects, such as:

  Tables

  Indexes

  Rollback segments

  Sort areas

  Temporary tablespaces

  Redo logs

  Partition tables

  Index-only tables

• The amount and allocation of memory, such as:

  Database buffers

  Redo log buffers

  Shared pool

3.1.2. What Can Be Tuned?

Aspects of the Oracle database that can be tuned include thefollowing:

• Memory utilization

• Disk utilization

• SQL statement execution

• 
• Create Bookmark (Key: b)Create Bookmark
• Create Note or Tag (Key: t)Create Note or Tag
• Download (Key: d)Download
• Email This Page (Key: e)Email This Page
• PrintPrint

• Html View (Key: h)Html View
• Zoom Out (Key: -)Zoom Out
• Zoom In (Key: +)Zoom In
• Toggle to Full Screen (Key: f)
• Previous (Key: p)Previous
• Next (Key: n)Next

Related Content

Oracle tuning
From: Database Performance Tuning on AIX

Books for Oracle database administration and tuning
From: Database Performance Tuning on AIX

Oracle tuning order
From: Database Performance Tuning on AIX

The Oracle Data Provider
From: ADO.NET Programming in Visual Basic™ .NET

Oracle CREATEs
From: Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Oracle
From: Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Oracle databases
From: Database Performance Tuning on AIX

Oracle Performance
From: Oracle Essentials, 4th Edition

Oracle Database Examples
From: Beginning Oracle Database 11g Administration: From Novice to Professional

Let Oracle do most of the tuning for you.
From: Oracle PL/SQL Best Practices, 2nd Edition