It has three input variables: the script (generated
by GET_TASK_SCRIPT, to which you pass the name of the task), the output directory, and the
name of the file to be created.
execute DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MV_TUNE'),-
'SCRIPTS','MV_TUNE.sql');
The MV_TUNE.sql file created by the CREATE_FILE procedure will contain commands similar
to those shown in the following listing. Depending on the specific version of Oracle, the
recommendations may differ.
Rem Username: PRACTICE
Rem Task: MV_TUNE
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW "PRACTICE"."MV$$_021F0001"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT PRACTICE.BOOKSHELF.ROWID C1,
"PRACTICE"."BOOKSHELF"."PUBLISHER" M1
FROM PRACTICE.BOOKSHELF;
begin
dbms_stats.gather_table_stats('"PRACTICE"',
'"MV$$_021F0001"',NULL,dbms_stats.auto_sample_size);
end;
/
whenever sqlerror EXIT SQL.SQLCODE
begin
dbms_advisor.mark_recommendation('MV_TUNE',1,'IMPLEMENTED');
end;
/
The MARK_RECOMMENDATION procedure allows you to annotate the recommendation
so that it can be skipped during subsequent script generations. Valid actions for MARK_
RECOMMENDATION include ACCEPT, IGNORE, IMPLEMENTED, and REJECT.
You can use the TUNE_MVIEW procedure of the DBMS_ADVISOR package to generate
recommendations for the reconfiguration of your materialized views.
Pages:
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912