Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  SQL performance tuning


Info on performance
dbms_profiler explained
How to use hints
Undocumented hints
How to Trace sessions
Create statistics


Oracle Performance Tuning

When SQL statements are fired in the Oracle database, a part called the optimizer will determine the most efficient execution path of the statement after considering many factors related to the objects referenced and the conditions specified in the query. In the old days a rule based optimizer was used, but since Oracle 8i most of the time a cost based optimizer is used.

For the Cost Based Optimizer to work efficiently, Oracle needs to have right statistics. There is a seperate document that descripts how to create statistics for Oracle tables/columns.

Tracing SQL

To trace SQL statements you can also use TKProf
Since Oracle 10g Oracle ADDM can be used to look at executed SQL statements.
In SQL*Plus statements can be traced by using the command 'set autotrace on;'.

Tuning SQL

If SQL statemnts are not performing you can do many things about it. Some solutions can be:
  • Add a hint to the statement to influence the optimizer.
  • Add indexes
  • Rewrite the SQL

Oracle 10 automated performance tuning features.

Oracle 10g provides some automated features:
Automatic Workload Repository (AWR)
The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views. The statistics collected and processed by AWR include:
  • Object statistics that determine both access and usage statistics of database segments
  • Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
  • Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views
  • SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time
  • Active Session History (ASH) statistics, representing the history of recent sessions activity
  • AWR automatically generates snapshots of the performance data once every hour and collects the statistics in the workload repository. You can also manually create snapshots, but this is usually not necessary. The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).

Automatic Database Diagnostic Monitor
Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database. An ADDM analysis is performed every time an AWR snapshot is taken and the results are saved in the database. You can view the results of the analysis using Oracle Enterprise Manager or by viewing a report in a SQL*Plus session. In most cases, ADDM output should be the first place that a DBA looks when notified of a performance problem.
Automatic database diagnostic monitoring is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable the automatic database diagnostic monitoring. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle features, including ADDM, and is strongly discouraged.
The primary interface for diagnostic monitoring is the Oracle Enterprise Manager Database Control. On the Database Home page, ADDM findings for the last analysis period are displayed under Diagnostic Summary.
ADDM can also be invoked in SQL*Plus. This can be done by running the $ORACLE_HOME/rdbms/admin/addmrpt.sql script.
SQL Tuning Advisor
SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements. Automatic SQL Tuning capabilities are exposed through a server utility called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of SQL Profile. A user can choose to accept the recommendation to complete the tuning of the SQL statements.
The recommended interface for the SQL Tuning Advisor is the Oracle Enterprise Manager. The SQL Tuning Advisor may be used to tune a single or multiple SQL statements. When tuning multiple SQL statements, Oracle Enterprise Manager will automatically create a SQL Tuning Set (STS) from a user-defined set of SQL statements. An STS is a database object that stores SQL statements along with their execution context.
While the recommended interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control, the advisor can be administered with procedures in the DBMS_SQLTUNE package. To use the APIs, the user must be granted specific privileges. Running SQL Tuning Advisor using DBMS_SQLTUNE package is a multi-step process:
1. Create a SQL Tuning Set (if tuning multiple SQL statements)
2. Create a SQL tuning task
3. Execute a SQL tuning task
4. Display the results of a SQL tuning task
5. Implement recommendations as appropriate

Sample code:
-- Tuning task created for specific a statement from the AWR.
  l_sql_tune_task_id  VARCHAR2(100);
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                   begin_snap  => 743,
                   end_snap    => 804,
                   sql_id      => '2udx7yrn9gcf2',
                   scope       => DBMS_SQLTUNE.scope_comprehensive,
                   time_limit  => 60,
                   task_name   => 'Edwin_AWR_tuning_task',
                   description =>
                    'Test Tuning task for select distinct .');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name =>

SET LONG 10000;
SELECT DBMS_SQLTUNE.report_tuning_task('Edwin_AWR_tuning_task') 
AS recommendations FROM dual;


The following books on performance tuning are recommended:
Oracle High Performance Tuning for 9i and 10g, First Edition (Paperback)
Gavin JT Powell
Optimizing Oracle Performance
Cary V. Millsap
O Reilly & Associates
Oracle9i Performance Tuning Tips & Techniques
Richard J. Niemiec
McGraw-Hill Osborne Media
High-Performance Oracle: Proven Methods for Achieving Optimum Performance and Availability
Geoff Ingram
John Wiley & Sons
Oracle9i High-Performance Tuning with STATSPACK
Donald K. Burleson
McGraw-Hill Osborne Media
Oracle SQL Tuning Pocket Reference
Mark Gurry
O Reilly & Associates
Oracle Performance Tuning
Edward Whalen
Addison-Wesley Pub Co
Oracle Performance Tuning (Nutshell Handbook)
Mark Gurry
O Reilly & Associates
Oracle Performance Tuning 101
Gaja Krishna Vaidyanatha
McGraw-Hill Osborne Media