Databases Reference
In-Depth Information
We begin this chapter by showing how to use the
QUICK_TUNE
procedure to provide indexing advice
for one SQL statement. Then we demonstrate how to access SQL Access Advisor through Enterprise
Manager to obtain indexing advice for a group of SQL statements.
Generating Advice for a Single SQL Statement
If you want indexing advice for one specific SQL statement, use the SQL Access Advisor's
QUICK_TUNE
procedure of the
DBMS_ADVISOR
package. Before running the
QUICK_TUNE
procedure, ensure the following
preparatory tasks have been performed:
Tables referenced in the query should have accurate statistics. Use the
DBMS_STATS
package to generate statistics (if required).
•
The user executing the
QUICK_TUNE
procedure must also have
SELECT
privileges on
the tables referenced in the SQL query.
•
The
ADVISOR
role must be granted to the user executing the
DBMS_ADVISOR
package.
For example, the following statement grants the
ADVISOR
role to the
MV_MAINT
user:
SQL> grant advisor to mv_maint;
•
After the initial setup, using the
QUICK_TUNE
procedure is fairly straightforward. In fact, it's a two-step
process.
1.
Execute the QUICK_TUNE procedure.
2.
Generate recommendations.
For the first step, you need to provide as input to the
QUICK_TUNE
procedure the advisor name, a task
name, and the SQL statement. In this example, the first parameter is the name of the advisor, the second
parameter is the name of the task, and the third parameter is the text of the SQL statement (see Table 10-
1 for descriptions of the
QUICK_TUNE
procedure parameters):
SQL> exec dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor, -
'QUICK_SQL_TUNE', -
'select last_name from emp where upper(last_name) = ''stark''');
PL/SQL procedure successfully completed.
In this code, the dashes “-” at” the end of the first two lines are required for line continuation when
executing a PL/SQL package/procedure directly from SQL*Plus. You could also directly embed the prior
code in an anonymous block of PL/SQL, as shown:
BEGIN
dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor,
'QUICK_SQL_TUNE',
'select last_name from emp where upper(last_name) = ''stark''');
END;
/
