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

* PL/SQL

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.