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.

从事西部信息机房，服务器租用，云主机，网站空间，国际域名空间，CDN，网络代维等服务。

     SQL Access Advisor, using theTUNE_MVIEW procedure, also recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite.TheDBMS_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 procedureGET_TASK_SCRIPT. The resulting script is an executable SQL file that can containDROP,CREATE, andALTER 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 usingDBMS_ADVISOR.GET_TASK_SCRIPT function andDBMS_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 fromTUNE_MVIEW contains the materialized view logs and multiple materialized view(UseUSER_TUNE_MVIEW orDBA_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

TheUNDO 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 ROWIDDISABLE 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 REWRITEAS 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)

本文标题：HowToUseDBMS_ADVISOR.TUNE_MVIEWTuningMaterializedViews
转载源于：http://azwzsj.com/article/gcicis.html