 |
|
Oracle
Using the SQL Tuning Advisor
Oracle Tips by Burleson Consulting |
This
is an excerpt from "Oracle 10g New Features for Administrators" by
Ahmed Baraka.
Providing SQL Statements to the SQL Tuning Advisor
o Create a new set of statements as an input for
the SQL Tuning Advisor.
o The ADDM may often recommend high-load statements.
o Choose a SQL statement that’s stored in the AWR.
o Choose a SQL statement from the database cursor cache.
How the SQL Tuning Advisor Works
The optimizer will work in the new tuning mode
wherein it conducts an in-depth analysis to come up with a set of
recommendations, the rationale for them and the expected benefit if
you follow the recommendations.
When working in tuning mode, the optimizer is
referred to as the Automatic Tuning Optimizer (ATO).
The ATO performs the following tuning tasks:
o Statistics analysis
o SQL profiling
o Access path analysis
o SQL structure analysis
Statistics Analysis
ATO recommends collecting new statistics for
specific objects, if required.
SQL Profiling
The ATO’s goal at this stage is to verify that its
own estimates of factors like column selectivity and cardinality of
database objects are valid.
• Dynamic data sampling
Using a sample of the data, the ATO can check if its own estimates for
the statement in question are significantly off the mark.
• Partial execution
The ATO may partially execute a SQL statement, so it can check if
whether a plan derived purely from inspection of the estimated
statistics is actually the best plan.
• Past execution history statistics
The ATO may also use any existing history of the SQL statement’s
execution to
determine
appropriate settings for parameters like OPTIMIZER_MODE.
The output of this phase is a SQL Profile of
the concerned SQL statement. If you create that SQL profile, it will
be used later by the optimizer when it executes the same SQL
statement in the normal mode. A SQL profile is simply a set of
auxiliary or supplementary information about a SQL statement.
Access Path Analysis
The ATO analyzes the potential impact of using
improved access methods, such as additional or different indexes.
SQL Structure Analysis
The ATO may also make recommendations to modify the
structure, both the syntax and semantics, in your SQL statements.
SQL Tuning Advisor Recommendations
The SQL Tuning Advisor can recommend that you do the
following:
o Create indexes to speed up access paths
o Accept a SQL profile, so you can generate a better execution
plan
o Gather optimizer statistics for objects with no or stale
statistics
o Rewrite queries based on the advisor’s advice
Using the DBMS_SQLTUNE Package
The DBMS_SQLTUNE package is the main Oracle Database
10g interface to tune SQL statements.
Following are the required steps:
1. Create a task. You can use the CREATE_TUNING_TASK
procedure to create a task to tune either a single statement or
several statements.
execute :v_task :=
DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text=>'sele
ct count(*) from hr.employees,hr.dept')
2. Execute the task. You start the tuning process by
running the EXECUTE_TUNING_TASK procedure.
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:v_task) FROM DUAL;
3. Get the tuning report. By using the
REPORT_TUNING_TASK procedure.
4. Use DROP_TUNING_TASK to drop a task, removing all
results associated with the task.
Managing SQL Profiles
Use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to
create a SQL profile based on the recommendations of the ATO.
Managing SQL Tuning Categories
• Any created SQL Profile will be assigned to a
category defined by the parameter SQLTUNE_CATEGORY.
• By default, SQLTUNE_CATEGORY has the value of DEFAULT.
• You can change the SQL tuning category for all users with the
following command:
ALTER SYSTEM SET
SQLTUNE_CATEGORY = PROD
• To change a session’s tuning category, use the
following command:
ALTER
SESSION SET SQLTUNE_CATEGORY = DEV
You may also use the DBMS_SQLTUNE.ALTER_SQL_PROFILE
procedure to change
the SQL tuning category.
Using the Database Control to Run the SQL Tuning
Advisor Under the Performance tab, click the
Advisor Central link and then click the SQL Tuning
Advisor link.
There are several possible sources for the tuning
advisor’s SQL Tuning Set (STS) input:
o high-load SQL statements identified by the ADDM
o statements in the cursor cache
o statements from the AWR
o a custom workload
o another new STS.
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
