Databases Reference
In-Depth Information
The inputs and outputs of SQL Access Advisor are visually displayed in Figure 10-1. Central to the
SQL Access Advisor is the Oracle-supplied
DBMS_ADVISOR
PL/SQL package. This package contains the
QUICK_TUNE
procedure. The
QUICK_TUNE
procedure provides a straightforward method to generate
indexing advice for a specific SQL statement.
Figure 10-1. SQL Access Advisor architecture
When tuning a group of SQL statements, it is possible to manually cobble together the PL/SQL to
call SQL Access Advisor from the
DBMS_ADVISOR
package. However, it is much more efficient to use the
Enterprise Manager screens to choose from a wide variety options/features, and then automatically
generate the PL/SQL. Therefore, if you have a requirement to tune a set of SQL statements, we
recommend you use the Enterprise Manager SQL Access Advisor Wizard. If you need to, you can view
the PL/SQL that the graphical tool generates and manually tweak the output as required.
Note that Chapter 9 covered using the SQL Tuning Advisor for generating indexing advice; this
chapter describes the SQL Access Advisor. So what is the difference between these two tools in regards to
indexing advice? The main difference is the SQL Tuning Advisor provides advice for tuning SQL
statements in isolation; its advice doesn't consider the impact an index might have on other SQL
statements in a given workload whereas the SQL Access Advisor considers the impact of adding an index
across all SQL statements in the given workload. In other words, if adding an index speeds up one query
but adversely impacts other queries in the workload, then index creation is not recommended.
Tip
The Oracle documentation states the SQL Access Advisor is able to make suggestions for a workload by
analyzing structural statistics for a table, index cardinalities of dimension level columns, join key columns, and fact
table key columns. Therefore, before running the SQL Access Advisor, it's critical that accurate statistics exist for
objects used in the SQL workload set.
