Oracle database performance tuning guide

  Performance tuning is a never-ending task for the DBA, and this learning guide can help newbies and experts establish a smart approach to tuning, use and interpret Oracle's tuning utilities and identify specific problem areas.

TABLE OF CONTENTS    Performance tuning basics    Getting started    General considerations    Disk I/O tuning    SQL tuning    Application Server and E-Business Suite tuning    Quiz

No matter what version of Oracle you are using, the basics of performance tuning remain the same. Problems can stem from a variety of causes, and as the DBA, your job is to figure out what they are. For example, poorly designed applications and database schemas can cause issues like excessive CPU consumption due to too many logical I/Os, excessive disk reads due to missing indexes or excessive contention for shared resources.

Fighting the performance war needs to be done quickly and decisively. But you might not be able to rely on performance tools to help you. Find out if and how you can calculate database efficiency. Download the first chapter of Optimizing Oracle performance for an overview of some of the root causes of performance issues, problems with commonly used tuning techniques, requirements of a good method, advances in performance improvements, tools for analyzing response time and more. In this expert response Brian Peasland answers a handful of performance tuning questions. You might also want to familiarize yourself with the target values for performance statistics.

Although preeminent performance tuning guru Don Burleson stresses that there is no shortcut to successful Oracle tuning, and the tuner must intimately understand the complex interactions of the Oracle subsystems and be aware of some common tuning fallacies. Mike Ault further debunks common Oracle tuning myths.

And remember, it's possible to devote too much time to tuning.

Tuning a database is a time-intensive, repetitive task. Over time, the database changes in many ways and must be reevaluated. Read up on some first steps involved in determining database performance. Know that upgrades may cause performance issues. In addition, changes made to improve performance will need to be evaluated. This series identifies a standard approach for performing or evaluating database tuning efforts. It is applicable to data warehouses, custom databases, custom database applications or Oracle Applications database.

• Step 1: Planning a tuning engagement covers the elements of a basic performance tuning plan.
• Step 2: Determining the current state forms a baseline for comparison in later stages.
• Step 3: Working the plan assesses the details of the system, in terms of specific architecture, processes and code.

You'll also want to understand performance forecasting. Craig Shallahamer discusses the essentials of performance forecasting in a two-part column.

Using Oracle's tuning utilities:

• General resources for performance tuning and V$ views
• Oracle tuning indicators
• How to use EXPLAIN PLAN
• Interpreting TKPROF and EXPLAIN PLAN
• Clarification on explain plan statistics
• Get familiar with the Database Resource Manager facility
• Learning to read a STATSPACK report
• Understanding a STATSPACK report

Performance tuning means optimizing specific, individual aspects of your system's hardware and software, but here are a few general ideas to keep in mind:

• Old versus existing databases
  The approach you take to tuning a new database should be no different than that for an existing one. But many database and instance parameters do have a noticeable effect on performance. It may help to reorganize the database. Pay attention to the database cache size, shared pool size, PGA aggregate target and session_cached_cursors as you run applications for the first time, and adjust them as necessary. Read Don Burleson's undocumented secrets for super-sizing your PGA.
• Indexes
  Oracle offers a wealth of index structures, each with their own benefits and drawbacks, and blocksize will have an effect on the structure of the index tree. Here, Tom Kyte addresses some frequently asked questions and myths about indexes. There are several ways to improve the performance of inserts. When dealing with a table that has highly skewed data distribution, histograms are an excellent way to improve performance by getting the optimizer to choose your index. Here are a few guidelines for determining when to rebuild an index and a script that does it automatically. Dropping a composite index could also improve performance. Paul Baumgartel explains how indexes affect DML statements.
• Latch parameters
  Whenever index contention is experienced (as evidenced by process waits), adjusting these parameters may be helpful. Increasing the number of latches available to your database can also reduce latch contention.
• Full table scans
  Full table scans have been blamed for poor query and database performance in the past, but their utility and performance have improved with each successive version of Oracle's DBMS. Here's how to find full table scans in 9i.
• Connectivity
  When a process is on the same machine as the server, use the IPC protocol for connectivity instead of TCP. If your database is distributed across a few servers, and you need to access the database across these servers, one way to improve performance is through the use of database links.
• RAID versus non-RAID
  There are many factors that contribute to the performance of RAID for Oracle databases, including stripe size, disk speed, disk connection architecture and mirroring.
• Real Application Clusters (RAC)
  This chapter excerpt outlines the basics of tuning in RAC. Also, find out what might be causing poor performance on a RAC implementation.
• Partitioning
  Think about the possible advantages of employing partitioning in your tables.

Optimizing disk I/O is a critical part of performance tuning. Mike Ault's book Oracle disk I/O tuning covers topics that include disk performance, RAID management, Oracle data file performance and Oracle data segment internals. Background information includes general disk architecture, disk layout, disk performance statistics and disk capacity.

Specific information on tuning includes:

• Optimizing ATA performance
• ATA tuning in Linux, part 1, part 2, part 3 and part 4
• ATA tuning in Windows
• Discussion of the SCSI bus, part 1 and part 2
• Tuning the SCSI interface in Windows, Linux and Unix, HP-UX (parts 1 and 2) and AIX

SQL tuning is the process of ensuring that the SQL statements that an application issues will run as fast as possible. Just like there may be ten different ways for you to drive from work to your house, there may be ten different ways to execute a query. Tuning SQL and PL/SQL begins with an understanding of how Oracle processes SQL and PL/SQL. You'll need to know how to use SQL trace files. You also have to understand a bit about Oracle's optimizer and how it chooses an execution path/plan for your SQL statement. Get started with five quick tuning tips and scripts.

This script checks all active processes, the latest SQL, and the SQL hit ratio. Once you identify slow running SQL, you can use SQL*Plus autotrace to examine the execution plan. Here is an example of two load profiles showing how reducing consistent gets reduces the overall workload.

Kimberly Floss' book Oracle SQL and index internals describes advanced Oracle SQL internals and Oracle indexing management. These chapter excerpts offer an introduction to the step-by-step process of SQL tuning:

• High performing SQL -- where do you start?
  
• New performance views to identify problem SQL
  
• Identify the resource-intensive code
  
• Tune the code/database for better performance
  
• Methods for creating high-quality code
  
• Know thy database
  
• EXPLAIN and understand
  
• Try different combinations
  
• Revisit when necessary
  
• And if you can't change the code...
  
• Index rebuilds
  
• Histograms and finding the poorly running SQL
  

Don Burleson provides some basic rules for writing efficient queries and lists a few undocumented SQL hints that can be extremely useful for solving complex SQL execution problems. In addition, learn about using SQL Access Advisor to tune SQL.

System performance problems may not be caused by DBMS problems at all. These resources provide an overview of tuning Oracle applications and Application Server.

• John Spirko offers a primer on where Oracle E-Business transaction breakdowns are likely to occur (from the concurrent manager, to forms, databases and the Web) in this webcast.
• Oracle applications implementations are apt to be complex, with plenty of opportunities to improve performance. This tip identifies apps tuning misconceptions, maps out a management approach and names specific parameters to tune.
• Don Burleson discusses techniques Oracle professionals use to detect and correct performance bottlenecks in Oracle9iAS.

Prepare for a job interview or just quiz yourself on your performance tuning knowledge. Here's a list of 26 questions you should be able to answer by now.