若需簡單化 , 使用10g WEB OEM 最快~
Automatic SQL Tuning in Oracle Database
This article the discusses the new features which automate the tuning of SQL statements in Oracle
In its normal mode the query optimizer needs to make decisions about execution plans in a very short time. As a result it may not always be able to obtain enough information to make the best decision. Oracle
10g allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further. This process may take several minutes for a single statement so it is intended to be used on high-load resource-intensive statements.
In tuning mode the optimizer performs the following analysis:
- Statistics Analysis - The optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in an SQL profile.
- SQL Profiling - The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as the
OPTIMIZER_MODE. If such improvements are possible the information is stored in an SQL profile. If accepted this information can then used by the optimizer when running in normal mode. Unlike a stored outline which fixes the execution plan, an SQL profile may still be of benefit when the contents of the table alter drastically. Even so, it's sensible to update profiles periodically. The SQL profiling is not performed when the tuining optimizer is run in limited mode.
- Access Path Analysis - The optimizer investigates the effect of new or modified indexes on the access path. It's index recommendations relate to a specific statement so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
- SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures that may impact on performance. The implementation of these suggestions requires human intervention to check their validity.
The automatic SQL tuning features are accessible from Enterprise Manager on the "Advisor Central" page these or from PL/SQL using the
DBMS_SQLTUNEpackage. This article will focus on the PL/SQL API as the Enterprise Manager interface is reasonably intuative.
SQL Tuning Advisor
In order to access the SQL tuning advisor API a user must be granted the
The first step when using the SQL tuning advisor is to create a new tuning task using the
CREATE_TUNING_TASKfunction. The statements to be analyzed can be retrieved from the Automatic Workload Repository (AWR), the cursor cache, an SQL tuning set or specified manually.
TASK_NAMEparameter is specified it's value is returned as the SQL tune task identifier. If ommitted a system generated name like "TASK_1478" is returned. If the
SCOPEparameter is set to
scope_limitedthe SQL profiling analysis is omitted. The
TIME_LIMITparameter simply restricts the time the optimizer can spend compiling the recommendations.
The following examples will reference the last tuning set as it has no external dependancies other than the SCOTT schema. The
NVLin the SQL statement was put in to provoke a reaction from the optimizer. In addition we can delete the statistics from one of the tables to provoke it even more.
With the tuning task defined the next step is to execute it using the
During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed.
The status of the tuning task can be monitored using the
Once the tuning task has executed successfully the recommendations can be displayed using the
In this case the output looks like this.
Once the tuning session is over the tuning task can be dropped using the
Managing SQL Profiles
To manage SQL profiles a user needs the following privileges.
If the recommendations of the SQL tuning advisor include a suggested profile you can choose to accept it using the
NAMEparameter is used to specify a name for the profile. If it is not specified a system generated name will be used.
CATEGORYattributes of an SQL profile can be altered using the
Existing SQL profiles can be dropped using the
IGNOREparameter prevents errors being reported if the specified profile does not exist.
SQL Tuning Sets
An SQL tuning set is a group of statements along with their execution context. These can be created automatically via Enterprise Manager or manually provided you have the necessary privileges.
An SQL tuning set is created using the
Statements are added to the set using the
LOAD_SQLSETprocedure which accepts a
REF CURSORof statements retrieved using one of the following pipelined functions:
SELECT_WORKLOAD_REPOSITORY- Retrieves statements from the Automatic Workload Repository (AWR).
SELECT_CURSOR_CACHE- Retrieves statements from the cursor cache.
SELECT_SQLSET- Retrieves statements from another SQL tuning set.
The following are examples of their usage.
The contents of an SQL tuning set can be displayed using the
References can be added to a set to indicate its usage by a client using the
ADD_SQLSET_REFERENCEfunction. The resulting reference ID can be used to remove it using the
UPDATE_SQLSETprocedure is used to update specific string (
ACTION) and number (
PARSING_SCHEMA_ID) attributes of specific statements within a set.
The contents of a set can be trimmed down or deleted completely using the
Tuning sets can be dropped using the
Useful views related to automatic SQL tuning include:
For more information see: