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.

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

 

  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.

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 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:

 

 

  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:

 

  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:

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.

 

  Quiz  

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.


 

This was first published in September 2006

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close