导航：首页 > 数据库 >

How To Use DBMS_ADVISOR.TUNE_MVIEW Tuning Materialized Views

发表于：2024-11-17 作者：千家信息网编辑

千家信息网最后更新 2024年11月17日，SQL Access Advisor index recommendations include bitmap, function-based, and B-tree indexes. A bitma

千家信息网最后更新 2024年11月17日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)