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
can be used to look at executed SQL statements.
If SQL statemnts are not performing you can do many things about it. Some solutions can be:
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:
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.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
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);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name =>
'Edwin_AWR_tuning_task');
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('Edwin_AWR_tuning_task')
AS recommendations FROM dual;
SET PAGESIZE 24
Books
The following books on performance tuning are recommended: