 |
|
Oracle Tips by Burleson |
Easy Oracle Automation
Chapter 7 -
Application and SQL Management
SQL Query Optimizer
When the query optimizer is called
in tuning mode, it is
referred to as the Automatic Tuning Optimizer. The tuning performed
by the Automatic Tuning Optimizer is called Automatic SQL Tuning.
For more information on the automatic tuning process, please refer
to the documentation, Oracle Database Performance Tuning Guide
10g from Oracle.
SQL Tuning Advisor
SQL Tuning Advisor provides automatic tuning
advice for SQL statements. It takes one or more SQL statements as
input and invokes the automatic tuning optimizer to perform SQL
tuning without actually modifying any statement. The output is a
series of advice or recommendations along with the rationale behind
each recommendation and its expected benefits. These recommendations
will prompt the user to collect statistics on the affected objects,
create new indexes, restructure the statements, or create new
profiles. The user can accept the recommendations or reject them.
For tuning multiple SQL statements, a SQL
Tuning Set (STS) is created. An STS is nothing but a database object
to store SQL statements with their execution context. STS can be
created from a command line or the new and improvised Enterprise
Manager. For the hands-on DBA, SQL Tuning Sets can be managed with
dbms_sqltune package procedures.
To manage SQL Tuning Sets through Oracle
Enterprise Manager, do the following steps:
The above text is
an excerpt from:
Easy Oracle Automation
Oracle10g Automatic
Storage, Memory and Diagnostic Features
ISBN 0-9745993-6-0
by Dr. Arun Kumar R.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
