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.
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.
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
That is one answer but
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
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.
waste hours scouring the Internet for Oracle
to our ezine
get them delivered straight to your in box.
SKYROCKET Your Career!
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
- SQL trace
- DBMS_STATS, DBMS_SPM and
- 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.
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 is an Oracle database facility that provides individual performance
information about SQL statements either at the instance or
The information gathered about SQL statements comprises:
If the statement's cursor is closed, row source information is
also provided including:
- Parse, execute, and fetch counts
- CPU and
- 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
- 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
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).
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
- create a SQL script that stores
statistics in the database
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.
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.
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
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