Databases Reference
In-Depth Information
CHAPTER 10
■ ■ ■
SQL Access Advisor
Oracle's SQL Access Advisor is a flexible tuning tool that analyzes either a single SQL statement or a
group of SQL statements and generates advice on how to improve performance by recommending the
creation of the following types of objects:
•
Indexes
•
Materialized views
•
Materialized view logs
•
Partitions for tables, indexes, and materialized views
SQL Access Advisor provides advice on implementing B-tree, bitmap, and function-based indexes.
The tool provides specific SQL index creation statements as part of its output. Also provided are
recommendations on optimizing materialized views through fast refreshes and query rewrite
capabilities. When applicable, partitioning advice is provided for base tables, indexes, and materialized
views.
Note
The SQL Access Advisor currently requires a license for the Oracle Tuning Pack and the Oracle
Diagnostics Pack.
Since the focus of this topic is on indexes, this chapter will demonstrate how to use the SQL Access
Advisor tool to generate indexing advice. The SQL Access Advisor is invoked from either the
DBMS_ADVISOR
package or the Enterprise Manager SQL Access Advisor Wizard. You can use the following
types of inputs to the SQL Access Advisor:
•
SQL tuning set (populated from SQL in AWR or memory)
•
SQL in memory
•
User-defined workload
•
Single SQL statement
