|
|
|
OraDev.com
|
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.
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:
|
|
|
|
|
|
|
