Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle top down tuning tips

Oracle Tips by Burleson Consulting
TechRepublic - February 28, 2001


Oracle tuning has always been the most difficult area of Oracle database management. In fact, some database administrators say that tuning an Oracle database is like trying to work on a car while it's flying down the highway at 60 mph! Because of Oracle's flexibility, the internal software is phenomenally complex, and there are hundreds of variables that can cause performance problems. The complexity leaves many shops unable to keep their database properly tuned.

An Oracle database is constantly changing, and the dynamic nature of tuning and of the Oracle environment makes it difficult for the Oracle database administrator to get a handle on what's going on inside the database.

Most Oracle experts generally use a top-down approach for tuning. This means that you start at a very high level, taking a look at the overall Oracle environment, and then successively drill down into more detail as you begin to tune the individual components within the database engine.

The popular Ion tool is the easiest way to analyze Oracle disk block changes  and Ion allows you to spot hidden disk I/O performance trends.  Ion is our favorite Oracle tuning tool, and the only 3rd party tool that we use.

The goal of this series of articles is to give you the general tools required to ensure that your Oracle database is performing at an optimal level. Although the articles won't make you an expert, they'll give you a solid understanding of all the important Oracle tuning issues. You'll find more detailed information in my book High-performance Oracle8 Tuning.

A note about database design and performance

The initial design of the Oracle tables and indexes is the single most critical factor in overall performance—and unfortunately, the design can rarely be changed once the system is placed into production use. So while the tuning techniques we'll be discussing can help you maximize the efficiency of your database engine, bear in mind that the initial design is the most important performance factor.

When a database is initially analyzed, the designer will often apply the normalization rules developed by E. F. Codd and C. J. Date. Their normalization study resulted in a set of table definitions that made it easier to design tables with controlled redundancy.

In the 1970s, database redundancy was difficult and expensive. As a result, database designers were taught to create databases in Third Normal Form (3NF), which prevented data duplication in multiple tables. But although a 3NF database was totally free of redundancy, the database queries could run very slowly because of the extra navigation required to access information. Over the 1980s and 1990s, database designers became more liberal with the introduction of redundant data to speed database queries.

The overall tuning approach

While there is no silver bullet for tuning Oracle databases, a comprehensive approach can help ensure that all of the bases are covered and that no important tuning facts have been overlooked. When tuning Oracle databases, you have to start by taking a broad look at the overall environment and successively drilling down for more details.

We begin by looking carefully at the database server for any problems that might exist within the CPU, RAM, or disk configurations. No amount of tuning is going to help an Oracle database when the server it is running on is short on resources.

Once we've completed the tuning of the Oracle server, we can look at the global parameters that affect the Oracle database (the Oracle instance). When looking at the Oracle database, we take a look at the database as a whole, paying careful attention to the Oracle initialization parameters that govern the configuration of the System Global Area (SGA) and the overall behavior of the database.

Once the database server and the Oracle instance have been tuned, we can begin the work of examining the individual Oracle tables and indexes within the database. At this phase, we look at the settings that can govern the behavior of a table and determine how well the settings accommodate the needs of the individual database.

When the Oracle objects are tuned, we proceed with tuning the individual SQL queries that are issued against the Oracle database. This is often one of the most challenging areas of Oracle turning because there can be many thousands of SQL statements issued against a highly active Oracle database. The person tuning the Oracle SQL has to identify the most frequently used SQL statements and apply the tools necessary to tune each statement for the optimal execution plan.

A four-part tuning approach

Oracle tuning involves the following steps, with each step getting more specific and targeted:

  1. Server & network tuning—If there is a problem with the Oracle server, such as an overloaded CPU, excessive memory swapping, or a disk I/O bottleneck, no amount of tuning within the Oracle database is going to improve your performance. Hence, the first thing the Oracle professional examines is the server and network environment.
  2. Instance tuning—Tuning the Oracle SGA is the next step, and all of the Oracle initialization parameters must be reviewed to ensure that the database has been properly configured. This phase of Oracle tuning is directed at looking for resources shortages in the db_block_buffers, shared_pool_size, and sort_area_size. We also investigate important default parameters for Oracle, such as optimizer_mode.
  3. Object tuning—This phase of tuning looks at the setting for Oracle tables and indexes. Settings such as PCTFREE, PCTUSED, and FREELISTS can have a dramatic impact on Oracle performance.
  4. SQL tuning—This is the most time-consuming tuning operation because there can be many thousands of individual SQL statements that access the Oracle database. At a high level, we identify the most common SQL statements, tune each one by carefully reviewing the execution plan for the SQL, and adjust the execution plan using Oracle hints.
The success of your Oracle tuning requires you to follow the steps in their proper order. Many neophyte Oracle DBAs will immediately begin to tune SQL statements without considering the environment in which the SQL is running—which can be a waste of time and cause new problems. Looking at the overall tuning methodology first can enable you to see some of the larger and broader tuning issues that need to be addressed first.

Over the next four articles, we'll examine each of these main techniques and explore some of the details involved in tuning your Oracle database system. In the next article, we'll cover hardware server tuning in more detail and show you how you can determine whether the server is the cause of your performance problems.

For more information, check my notes on Oracle Network Tuning.
If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational