SQL Advisors

For SQL tuning, Oracle Database 10g has two advisors: SQL Tuning Advisor and SQL Access Advisor. SQL Tuning Advisor provides tuning advice for SQL statements without modifying any SQL statements. SQL Access Advisor provides tuning advice on indexes, materialized views, and materialized view logs for larger reports or work load. Both advisors are available through OEM. Rule Based Optimizers are completely de-supported in Oracle Database 10g, and these SQL advisors could help to a certain extent in rewriting your old RBO queries and taking advantage of Cost Based Optimization (CBO).

SQL Tuning Advisor takes one or more SQL statements as its input and invokes the automatic tuning optimizer to perform SQL tuning. The output is a series of advice or recommendations along with the rationale behind each one, as well as their expected benefits. The recommendations 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. The various inputs to SQL Tuning Advisor come from a variety of sources including ADDM, top SQL statements from AWR, the cursor cache, the SQL tuning set, and SQL statements given by users.

SQL Access Advisor is used for applications with complex queries on large sets of data. It usually recommends a combination of indexes, materialized views, materialized view logs, and benefits from general query rewrites. It also recommends the use of bitmap and B-tree indexes.

For those who prefer the hands-on approach, these advisors have separate procedures to be run from a command window.