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 statements.

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, a task is created, the Advisor is run which generates recommendations, and the recommendations may or may not be implemented The recommendations given by these tools can then be accepted or rejected. These Advisor tools are available through Oracle Enterprise Manager 10g (OEM) and from SQL command prompt. More about the SQL tuning Advisor and SQLAccess Advisor will be provided in a later chapter.