 Oracle
Tips by BurlesonOracle10g Using SQL AWR
SQL Statements from cursor cache, AWR, and custom workload can
be filtered and/or ranked before they are input to the SQL Tuning
Advisor.
In the second stage, execute the tuning analysis tasks and view
the results (or create the SQL profiles). Later, you can implement
the suggestions.
Oracle Enterprise manager is a good place to launch and utilize
the SQL Tuning Advisor. SQL Tuning Advisor GUI can be invoked at
multiple places within the OEM.
- For high-load SQL statements identified by ADDM, SQL Tuning
Advisor can be launched from the ADDM Finding Details screen.
- When selecting from the Top SQL statements, Advisor can be
launched from the Top SQL Page.
- When STS becomes the input for tuning, advisor can be
launched from the SQL Tuning Sets page.
OEM also allows you to view the tuning analysis results, create
the SQL profile, and then implement the suggestions.
In addition to the OEM GUI, SQL Advisor functionality can be
obtained by using the PL/SQL package dbms_sqltune. This is a new
package introduced in 10g, and it has comprehensive procedures
that help to conduct the full SQL Advisor session. Some of the
useful procedures include:
- create_tuning_task - Creates a tuning task for a given SQL
statement or for a cached cursor
- execute_tuning_task – Executes the tuning task and generates
the tuning data
- report_tuning_task – Generates a complete report of the
results of a task
- report_plans – Shows the SQL plans
There are two important initialization parameters that affect
the operation of SQL Tuning Advisor. They are as follows:
- optimizer_auto_learn –Enables or disables the auto-learning
optimizer feature
- sqltune_category – Selectively enables the SQL tuning base
features for specific sessions
|
