How To Use DBMS_ADVISOR.TUNE_MVIEW Tuning Materialized Views

      SQL Access Advisor index recommendations include bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. Bitmap indexes are most commonly used in a data warehouse to index unique or near-unique keys. SQL Access Advisor materialized view recommendations include fast refreshable and full refreshable MVs, for either general rewrite or exact text match rewrite.

     SQL Access Advisor, using the TUNE_MVIEW procedure, also recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite.The DBMS_ADVISOR package consists of a collection of analysis and advisory functions and procedures callable from any PL/SQL program.
     n alternative to querying the metadata to see the recommendations is to create a script of the SQL statements for the recommendations, using the procedure GET_TASK_SCRIPT. The resulting script is an executable SQL file that can contain DROP, CREATE, and ALTER statements. For new objects, the names of the materialized views, materialized view logs, and indexes are auto-generated by using the user-specified name template. You should review the generated SQL script before attempting to execute it.

[oracle@ORACLERAC2 ~]$ pwd
/home/oracle
[oracle@ORACLERAC2 ~]$ mkdir scripts
SQL> select DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','PROD_COST_MV','SH') from dual;

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','PROD_COST_MV','SH')
--------------------------------------------------------------------------------
  CREATE MATERIALIZED VIEW "SH"."PROD_COST_MV" ("TIME_ID", "PROD_SUBCATEGORY", "
SUM_UNITS")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  BUILD IMMEDIATE
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT time_id, prod_subcategory,SUM( unit_cost) AS sum_units FROM costs c,
 products p WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_subcategory
SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced
SQL> create directory tune_results  as '/home/oracle/scripts';
Directory created.
SQL> grant read,write,execute  on directory  tune_results  to public;
Grant succeeded.
SQL> grant advisor to sh;
Grant succeeded.
SQL> conn sh/sh
Connected.
SQL> var task_cust_mv varchar2(30);
SQL> var create_mv_ddl varchar2(4000);
SQL> exec :task_cust_mv :='cust_mv';
PL/SQL procedure successfully completed.

SQL> exec  :create_mv_ddl :='CREATE MATERIALIZED VIEW "SH"."PROD_COST_MV" TABLESPACE "USERS" USING INDEX PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 64k  BUFFER_POOL DEFAULT) LOGGING  USING INDEX  TABLESPACE "USERS"  PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 64k  BUFFER_POOL DEFAULT) REFRESH FORCE ON DEMAND AS SELECT time_id, prod_subcategory,SUM(unit_cost) AS sum_units FROM costs c,products p WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_subcategory';

PL/SQL procedure successfully completed.

SQL>  exec dbms_advisor.tune_mview(:task_cust_mv, :create_mv_ddl);
#######################################################################################
Script generation using DBMS_ADVISOR.GET_TASK_SCRIPT function and DBMS_ADVISOR.CREATE_FILE procedure.
Now generate both the implementation and undo scripts and place them in /tmp/script_dir/mv_create.sql and /tmp/script_dir/mv_undo.sql, respectively.
#######################################################################################
PL/SQL procedure successfully completed.

SQL>  EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv),'TUNE_RESULTS','mv_create.sql');

PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv),'TUNE_RESULTS','mv_undo.sql');

PL/SQL procedure successfully completed.

The following recommendation from TUNE_MVIEW contains the materialized view logs and multiple materialized view(Use USER_TUNE_MVIEW or DBA_TUNE_MVIEW views):
SCRIPT_TYPE
--------------
STATEMENT
--------------------------------------------------------------------------------
IMPLEMENTATION
CREATE MATERIALIZED VIEW LOG ON "SH"."COSTS" WITH ROWID, SEQUENCE ("PROD_ID","TI
ME_ID","UNIT_COST")  INCLUDING NEW VALUES

IMPLEMENTATION
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."COSTS" ADD ROWID, SEQUENCE ("PROD_ID"
,"TIME_ID","UNIT_COST")  INCLUDING NEW VALUES

IMPLEMENTATION
CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS" WITH ROWID, SEQUENCE ("PROD_ID",
"PROD_SUBCATEGORY")  INCLUDING NEW VALUES

IMPLEMENTATION
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS" ADD ROWID, SEQUENCE ("PROD_
ID","PROD_SUBCATEGORY")  INCLUDING NEW VALUES

IMPLEMENTATION
CREATE MATERIALIZED VIEW SH.PROD_COST_MV TABLESPACE "USERS"  PCTFREE 10 INITRANS
 1 MAXTRANS 255 STORAGE(INITIAL 64k  BUFFER_POOL DEFAULT) LOGGING  USING INDEX
TABLESPACE "USERS"  PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 64k  BUF
FER_POOL DEFAULT)  REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SH.PR
ODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST") M
1, COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS, SH.COSTS WH
ERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY
, SH.COSTS.TIME_ID

The UNDO output is as follows:
UNDO
DROP MATERIALIZED VIEW SH.PROD_COST_MV

IMPLEMENTATION
CREATE MATERIALIZED VIEW LOG ON "SH"."COSTS" WITH ROWID, SEQUENCE ("PROD_ID","TI
ME_ID","UNIT_COST")  INCLUDING NEW VALUES

IMPLEMENTATION
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."COSTS" ADD ROWID, SEQUENCE ("PROD_ID"
,"TIME_ID","UNIT_COST")  INCLUDING NEW VALUES

IMPLEMENTATION
CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS" WITH ROWID, SEQUENCE ("PROD_ID",
"PROD_SUBCATEGORY")  INCLUDING NEW VALUES

IMPLEMENTATION
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS" ADD ROWID, SEQUENCE ("PROD_
ID","PROD_SUBCATEGORY")  INCLUDING NEW VALUES

IMPLEMENTATION
CREATE MATERIALIZED VIEW SH.PROD_COST_MV TABLESPACE "USERS" PCTFREE 10 INITRANS
1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING USING INDEX TA
BLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER
_POOL DEFAULT)  REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SH.PRODU
CTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST") M1,
COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS, SH.COSTS WHERE
 SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY, S
H.COSTS.TIME_ID

UNDO
DROP MATERIALIZED VIEW SH.PROD_COST_MV
25 rows selected.

SQL>
SQL> l
  1* select script_type,statement  from dba_tune_mview

[oracle@ORACLERAC2 ~]$ pwd
/home/oracle
[oracle@ORACLERAC2 ~]$ ls
coldprod1.tar.gz  coldprod2.tar.gz  pwd  scripts
[oracle@ORACLERAC2 ~]$ cd scripts/
[oracle@ORACLERAC2 scripts]$ ls
mv_create.sql  mv.sql  mv_undo.sql  shit
[oracle@ORACLERAC2 scripts]$
[oracle@ORACLERAC2 scripts]$
[oracle@ORACLERAC2 scripts]$ ls -lart
total 24
-rw-r--r--. 1 oracle oinstall  772 Aug 19 11:37 mv.sql
-rw-r--r--. 1 oracle oinstall  574 Aug 19 13:23 shit
drwx------. 4 oracle oinstall 4096 Aug 19 13:23 ..
-rw-r--r--. 1 oracle oinstall 1314 Aug 19 15:17 mv_create.sql
drwxr-xr-x. 2 oracle oinstall 4096 Aug 19 15:20 .
-rw-r--r--. 1 oracle oinstall 1314 Aug 19 15:20 mv_undo.sql
[oracle@ORACLERAC2 scripts]$ cat  mv_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem  
Rem  Username:        SH
Rem  Task:            cust_mv
Rem  Execution date:   
Rem  

CREATE MATERIALIZED VIEW LOG ON
    "SH"."COSTS"
    WITH ROWID, SEQUENCE("PROD_ID","TIME_ID","UNIT_COST")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."COSTS"
    ADD ROWID, SEQUENCE("PROD_ID","TIME_ID","UNIT_COST")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."PRODUCTS"
    WITH ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."PRODUCTS"
    ADD ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.PROD_COST_MV
TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 64K BUFFER_POOL
       DEFAULT) LOGGING USING INDEX TABLESPACE "USERS" PCTFREE 10 INITRANS 2
       MAXTRANS 255 STORAGE(INITIAL 64K BUFFER_POOL DEFAULT)
    REFRESH FAST WITH ROWID
    DISABLE QUERY REWRITE
    AS SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST")
       M1, COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS,
       SH.COSTS WHERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY,
       SH.COSTS.TIME_ID;
SQL> SELECT STATEMENT FROM USER_TUNE_MVIEW
  2  WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE='IMPLEMENTATION';

STATEMENT
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW LOG ON "SH"."COSTS" WITH ROWID, SEQUENCE ("PROD_ID","TI
ME_ID","UNIT_COST")  INCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."COSTS" ADD ROWID, SEQUENCE ("PROD_ID"
,"TIME_ID","UNIT_COST")  INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS" WITH ROWID, SEQUENCE ("PROD_ID",
"PROD_SUBCATEGORY")  INCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS" ADD ROWID, SEQUENCE ("PROD_
ID","PROD_SUBCATEGORY")  INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW SH.PROD_COST_MV TABLESPACE "USERS"  PCTFREE 10 INITRANS
 1 MAXTRANS 255 STORAGE(INITIAL 64k  BUFFER_POOL DEFAULT) LOGGING  USING INDEX
TABLESPACE "USERS"  PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 64k  BUF
FER_POOL DEFAULT)  REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SH.PR
ODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST") M
1, COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS, SH.COSTS WH
ERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY
, SH.COSTS.TIME_ID
這里是紅色標注的關鍵字為DISABLE QUERY REWRITE。

SQL> @/home/oracle/scripts/mv_create.sql   
Materialized view log created.
Materialized view log altered.
Materialized view log created.
Materialized view log altered.
Materialized view created.
SQL> set autot trace exp
SQL> SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST") M1,COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS, SH.COSTS
  2  WHERE  SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY, SH.COSTS.TIME_ID;
Execution Plan
----------------------------------------------------------
Plan hash value: 3557764342
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                         | 21680 |   741K|       |   541   (1)| 00:00:07 |       |       |
|   1 |  HASH GROUP BY           |                         | 21680 |   741K|  3880K|   541   (1)| 00:00:07 |       |       |
|*  2 |   HASH JOIN              |                         | 82112 |  2806K|       |   139   (1)| 00:00:02 |       |       |
|   3 |    VIEW                  | index$_join$_001        |    72 |  1296 |       |     2   (0)| 00:00:01 |       |       |
|*  4 |     HASH JOIN            |                         |       |       |       |            |          |       |       |
|   5 |      INDEX FAST FULL SCAN| PRODUCTS_PK             |    72 |  1296 |       |     1   (0)| 00:00:01 |       |       |
|   6 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_SUBCAT_IX |    72 |  1296 |       |     1   (0)| 00:00:01 |       |       |
|   7 |    PARTITION RANGE ALL   |                         | 82112 |  1363K|       |   137   (1)| 00:00:02 |     1 |    28 |
|   8 |     TABLE ACCESS FULL    | COSTS                   | 82112 |  1363K|       |   137   (1)| 00:00:02 |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COSTS"."PROD_ID"="PRODUCTS"."PROD_ID")
   4 - access(ROWID=ROWID)
####################################################
這里是關鍵，記得修改DISABLE QUERY REWRITE為ENABLE QUERY REWRITE。

"mv_create.sql" 39L, 1313C written                                                                                                                                                                                         
[oracle@ORACLERAC2 scripts]$
[oracle@ORACLERAC2 scripts]$
[oracle@ORACLERAC2 scripts]$ ls
mv_create.sql  mv.sql  mv_undo.sql  shit
[oracle@ORACLERAC2 scripts]$ cat mv_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem  
Rem  Username:        SH
Rem  Task:            cust_mv
Rem  Execution date:   
Rem  

CREATE MATERIALIZED VIEW LOG ON
    "SH"."COSTS"
    WITH ROWID, SEQUENCE("PROD_ID","TIME_ID","UNIT_COST")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."COSTS"
    ADD ROWID, SEQUENCE("PROD_ID","TIME_ID","UNIT_COST")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."PRODUCTS"
    WITH ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."PRODUCTS"
    ADD ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.PROD_COST_MV
TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 64K BUFFER_POOL
       DEFAULT) LOGGING USING INDEX TABLESPACE "USERS" PCTFREE 10 INITRANS 2
       MAXTRANS 255 STORAGE(INITIAL 64K BUFFER_POOL DEFAULT)
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST")
       M1, COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS,
       SH.COSTS WHERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY,
       SH.COSTS.TIME_ID;
####################################################
SQL> DROP MATERIALIZED VIEW SH.PROD_COST_MV;
Materialized view dropped.
SQL> @/home/oracle/scripts/mv_create.sql    
CREATE MATERIALIZED VIEW LOG ON
*
ERROR at line 1:
ORA-12000: a materialized view log already exists on table 'COSTS'
Materialized view log altered.
CREATE MATERIALIZED VIEW LOG ON
*
ERROR at line 1:
ORA-12000: a materialized view log already exists on table 'PRODUCTS'
Materialized view log altered.
Materialized view created.

SQL>
SQL> set autot trace exp
SQL> SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST") M1,COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS, SH.COSTS
  2  WHERE  SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY, SH.COSTS.TIME_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 2761323600

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              | 13762 |  1007K|    22   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| PROD_COST_MV | 13762 |  1007K|    22   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)