Oracle Performance Tuning

Oracle performance tuning is a big subject and whole books have been written on it which means we can't possibly cover the subject in a few web pages. What we can do though is zoom in on the most important aspects and provide some tips and techniques as well as where to find more information.

So let's get started.

What is Oracle Performance Tuning?

This might seem like an obvious question. "It's tuning the Oracle database so that it's faster, isn't it?"

That is one answer but

is not really the best answer. Speed implies response time and this may not always be the factor that needs tuning. It may be overall throughput or other resources that need tuning, such as memory usage, cpu, i/o in general or reads specifically or writes specifically.

The answer in each specific case also needs to include whether it's the Oracle database per se that needs to be tuned or whether it's the application using it. There may be a fine line between the two but it defintely exists.

It's better, therefore, to generalise the definition as reducing resource usage by the database.

Where to start with Oracle Performance Tuning

Now that we've defined it, where do we start?

The answer to that is you start where the problem is. In other words you need to stop thinking of tuning the system as a whole and start considering individual susbsystems and application modules.

You need to start by gathering information about the prformance problem. Does it occur at a particular time of day or day of the week or at month end or just at the end of the financial/celendar year? Does it affect a group of users or just one in particular or is it system wide? If it affects just one group of users you need to find out what they are doing differently to everyone else. The more information you have before you start, the more effective you will be at solving the problem.

Oracle Tips & Tricks
to SKYROCKET Your Career!

Don't waste hours scouring the Internet for Oracle tips. Subscribe to our ezine and get them delivered straight to your in box.

Tools Available for Oracle Performance Tuning

The tools available to help you diagnose and resolve performance issues depend to a certain extent on the version of the database you are using. Oracle 10g and upwards have more tools available than earlier versions. In general the tools available include:

explain plan / DBMS_XPLAN package
SQL trace
TKPROF
DBMS_STATS, DBMS_SPM and DBMS_OUTLN packages
Automatic Workload Repository (AWR)
Automatic Database Diagnostic Monitor (ADDM)

Explain Plan / DBMS_XPLAN

When faced with an SQL statement that you suspect could be improved, before changing it you need to know what it's current execution plan is. This can be achieved by preceding the statement with the explain plan clause and then running it. This writes the predicted execution plan that the statement would use if it were to be run into the plan table from where it can be retrieved by calling DBMS_XPLAN.DISPLAY.

As the predicted execution plan may not be entirely accurate you can determine the actual execution plan of the statement after it has been run by using DBMS_XPLAN.DISPLAY_CURSOR.

Often this is the only tool required for Oracle performance tuning as most problems are due to SQL statements that are not using the optimal path to access the data.

SQL Trace

SQL trace is an Oracle database facility that provides individual performance information about SQL statements either at the instance or session level.

The information gathered about SQL statements comprises:

Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each commit and rollback
Wait event data for each SQL statement, and a summary for each trace file

If the statement's cursor is closed, row source information is also provided including:

The actual execution plan of each SQL statement
Number of rows, number of consistent reads, number of physical reads, number of physical writes, and time elapsed for each operation on a row

TKPROF

TKPROF is a program that is run against Oracle trace files to format them and generate a readable output. It can also:

display the actual execution plan of a statement
create a SQL script that stores statistics in the database

DBMS_STATS package

The DBMS_STATS package provides a number of routines for generating and manipulating statistics on tables and indexes in the database. These statistics are then used by the Oracle cost-based optimizer when determining the optimal execution plan for SQL statements.

DBMS_SPM

This package enables you to manage SQL execution plans to ensure that the plan and therefore the performance of the corresponding SQL statements are unchanged irrespective of any changes to the system such as more data, a new version of the Oracle database, or new applications.

DBMS_OUTLN

This package enables the management of stored outlines (stored data for an an execution plan for a specific SQL statement) in the Oracle database. It enables the

Oracle optimizer to re-create an execution plan equivalent to the original plan. In future releases of Oracle this will be deprecated in favour of SQL plan management (DBMS_SPM).

Automatic Workload Repository (AWR)

Introduced with Oracle 10g, AWR helps with Oracle performance tuning by collecting, processing and maintaining performance information which can be displayed in reports and/or views.

Automatic Database Diagnostic Monitor (ADDM)

This analyzes the data collected by AWR to identify any performance issues and provides recommendations to correct problems.

More information on these tools can be found in the Oracle Database Performance Tuning Guide.

More detail is available in the following tutorials on:
tuning index usage and full table scans in your SQL
when to use concatenated index and bitmap indexes