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 Concepts -  Major Tuning Steps and Responsibilities

Oracle Tips by Burleson Consulting

Tuning Responsibilities

Since tuning will be covered in other presentations I will just list the major tuning steps and discuss in general the goals for each step. The major thing to remember is that running is recursive, changes to one area may affect others, an example would be the correction of  IO waits resulting in a CPU bottleneck.

Before beginning a tuning effort always set goals, what do you want to accomplish? Otherwise you won?t know when you are successful.

Step 1: Tune the Business Rules

In this step the goals should be to create a set of business rules that truly model the business needs for the database. The business rules should specify requirements not give solutions or methods.

Step 2: Tune the Data Design

In this step the goals should be to provide a data design that optimizes the access of data. Usually the data structure will be normalized according to the rules of normalization to at least a third normal form design. Once a normal design is reached, specific tables will be denormalized to optimize performance. At this step indexing is laid out to optimize join paths.

Step 3: Tune the Application Design

In this step the DBA must have input to the developers and provide them with tools and guidance in the proper methods to optimize the SQL and PL/SQL used to access data. The goal of this step is to provide a properly tuned and optimized application that utilizes the data design to provide maximum performance.

Step 4: Tune the Logical Structure of the Database

This step may  require changes to the data design and additional creation of indexes. In this step the application is analyzed to set such parameters as INITRANS and FREELISTS. Proper setup of sequences is also accomplished in this step.

Step 5: Tune Database Operations

System designers and application developers must understand Oracle's query processing mechanism to write effective SQL statements.

Before tuning the Oracle Server itself, be certain that your application is taking full advantage of the SQL language and the Oracle features designed to speed application processing. Use features and techniques such as the following based on the needs of your application:

* Array processing

* The Oracle optimizer

* The row-level lock manager


Step 6: Tune the Access Paths

Ensure that there is efficient access to data. Consider the use of clusters, hash clusters, B*-tree indexes and bitmap indexes as well as new features of Oracle8 such as index only tables, partitioned views and indexes and using new object oriented features.

Ensuring efficient access may mean adding indexes, or adding indexes for a particular application section (such as for specialized reporting) and then dropping them again. It may mean revisiting your design after you have built the database. You may want to do more denormalization or create alternative indexes at this point. Upon testing the application you may find that you're still not obtaining the required response time. Look for more ways to improve the design.

Step 7: Tune Memory Allocation

Appropriate allocation of memory resources to Oracle memory structures can have a large impact on performance.

Oracle8 shared memory is allocated dynamically to the following structures, which are all part of the shared pool. Although you explicitly set the total amount of memory available in the shared pool, the system dynamically sets the size of each structure contained within it:

* The data dictionary cache

* The library cache

* Context areas (if running a multithreaded server)

You can explicitly set memory allocation for the following structures:

* Buffer cache

* Log buffer

* Sequence caches

Proper allocation of memory resources can improve cache performance, reduce parsing of SQL statements, and reduce paging and swapping.

Process local areas include:

* Context areas (for systems not running a multithreaded server)

* Sort areas

* Hash areas

Be careful not to allocate to the system global area (SGA) to such a large percentage of the machine's physical memory that it causes paging or swapping.

Step 8: Tune I/O and Physical Structure

Disk I/O tends to reduce the performance of many software applications. Oracle Server, however, is designed so that its performance need not be unduly limited by I/O. Tuning I/O and physical structure involves these procedures:

* Distributing data so that I/O is distributed, thus avoiding disk contention

* Storing data in data blocks for best access: setting the right number of free lists, and proper values for PCTFREE and PCTUSED

* Creating extents large enough for your data so as to avoid dynamic extension of tables, which would hurt high-volume OLTP applications

* Evaluating the use of raw devices

Step 9: Tune Resource Contention

Concurrent processing by multiple Oracle users may create contention for Oracle resources. Contention may cause processes to wait until resources are available. Take care to reduce the following kinds of contention:

* Block contention

* Shared pool contention

* Lock contention

* Pinging (in a parallel server environment)

* Latch contention

Step 10: Tune the Underlying Platform(s)

See your platform-specific Oracle documentation to investigate ways of tuning the underlying system. For example, on UNIX-based systems you might want to tune the following:

* Size of the UNIX buffer cache

* UNIX memory segments

* Logical volume managers

* Memory and size for each process

Tuning Summary

Remember that without clear tuning goals you will never know if you have been successful. Tuning is a recursive process and is never finished.


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.



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