HowToUseDBMS_ADVISOR.TUNE_MVIEWTuningMaterializedViews
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://shouzuofang.com/article/gcicis.html