Automated SQL Tuning Features
Oracle Database 10g has provided a handful
of new features like Automatic Workload Repository (AWR),
Automatic Database Diagnostic Monitor (ADDM), SQL Tuning Advisor
and SQLAccess Advisor for tuning the SQL statements or
application code. While AWR and ADDM render services to support
automatic SQL tuning, SQL Tuning Advisor and SQLAccess Advisor
are the actual tuning tools.
SQL Tuning Advisor provides tuning advice
for SQL statements with
out modifying any statement. 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 or restructure the
For complex applications and large
databases, SQLAccess Advisor comes in very handy. SQLAccess
Advisor is a tuning tool that provides advice on indexes,
materialized views and materialized view logs for a given work
load. It also provides advice on database schema issues and
determines optimal data access paths.
For both the tuning tools,