Oracle database performance tuning guide

Learn the tricks of industry experts for identifying and eliminating performance problems in this guide. Topics include tuning fundamentals and fallacies, explanations of performance tuning tools, disk I/O and SQL tuning specifics, and common problem areas.

  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.

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


  Performance tuning basics  

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.

More on Oracle database performance tuning

Learn to use Oracle segment space management to improve performance

Improve database optimizer performance with Oracle dynamic sampling

Read this tip to learn how to tune Oracle instance recovery

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.


  Getting started  

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


  General considerations  

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.


  Disk I/O tuning  

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  

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.



  Application Server and E-Business Suite tuning  

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



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.


Dig Deeper on Oracle database performance problems and tuning

Data Management
Business Analytics
  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

  • How developers can avoid remote work scams

    Software developers can find good remote programming jobs, but some job offers are too good to be true. Follow these tips to spot...

Data Center
Content Management