You should review the generated script prior to executing it, particularly noting the tablespace
specifications. Later in this chapter, you will see how to use the QUICK_TUNE procedure to
simplify the tuning advisor process for a single command.
To tune a single SQL statement, use the QUICK_TUNE procedure of the DBMS_ADVISOR
package. QUICK_TUNE has two input parameters, a task name and a SQL statement. Using
QUICK_TUNE shields the user from the steps involved in creating workloads and tasks via
DBMS_ADVISOR.
For example, the following procedure call evaluates a query:
execute DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, -
'MV_TUNE','SELECT PUBLISHER FROM BOOKSHELF');
NOTE
The user executing this command needs the ADVISOR system
privilege.
The recommendations generated by QUICK_TUNE can be viewed via the data dictionary
view USER_ADVISOR_ACTIONS, but they are easier to read if you use the DBMS_ADVISOR
procedures to generate a script file. The recommendation in this example is to create a materialized
view to support the query. Because only one SQL statement was provided, this recommendation
is given in isolation and does not consider any other aspects of the database or application.
You can use the CREATE_FILE procedure to automate the generation of a file containing the
scripts needed to implement the recommendations. First, create a directory object to hold the file:
create directory scripts as 'e:\scripts';
grant read on directory scripts to public;
grant write on directory scripts to public;
Chapter 17: Managing Distributed Databases 619
Next, execute the CREATE_FILE procedure.
Pages:
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911