物化视图相关工具包

http://blog.itpub.net/18922393/viewspace-766836/

利用物化视图进行查询重写,可以有效提高系统性能.但物化视图查询重写受很多因素影响,物化视图的快速刷新功能也受很多因素影响.
    Oracle为此提供了一些工具包,可以用于分析查询重写失败原因,影响快速刷新的原因,或者会自动生成支持快速刷新DDL.

1,使用dbms_advisor.tune_mview获取物化视图创建建议
1.1测试:
create table mv_base
as
select * from hr.employees;

--拟创建MV：
create materialized view mv_mview
refresh fast
as
select distinct department_id,manager_id
from mv_base;

begin
  dbms_stats.gather_table_stats(user,'MV_BASE');
end;
/ 

--调优任务：
declare
  task_name varchar2(100);
BEGIN
  task_name := 'My taskabcd';
  DBMS_ADVISOR.TUNE_MVIEW(task_name,'create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base');
END;
/

--获取建议：
SQL> select statement from USER_TUNE_MVIEW where task_name='My taskabcd';
CREATE MATERIALIZED VIEW LOG ON
"BNET"."MV_BASE" WITH ROWID, SEQUENCE ("MANAGER_ID","DEPARTMENT_ID") 
INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON "BNET"."MV_BASE"
ADD ROWID, SEQUENCE ("MANAGER_ID","DEPARTMENT_ID")  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW BNET.MV_MVIEW  
 REFRESH FAST WITH ROWID
 DISABLE QUERY REWRITE
 AS
 SELECT BNET.MV_BASE.DEPARTMENT_ID C1, BNET.MV_BASE.MANAGER_ID C2, COUNT(*) M1 FROM B
OCNET.MV_BASE GROUP BY BNET.MV_BASE.DE
PARTMENT_ID, BNET.MV_BASE.MANAGER_ID;

DROP MATERIALIZED VIEW BNET.MV_MVIEW

1.2常见错误：
1.2.1，task name 不能直接使用字符常量（IN OUT 参数）
SQL> declare
  2    task_name varchar2(100);
  3  BEGIN
  4    task_name := 'My taskabcd';
  5    DBMS_ADVISOR.TUNE_MVIEW('My taskx1','create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base');
  6  END;
  7  /
  DBMS_ADVISOR.TUNE_MVIEW('My taskx1','create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base');
                          *
ERROR at line 5:
ORA-06550: line 5, column 27:
PLS-00363: expression 'My taskx1' cannot be used as an assignment target
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

1.2.2,基表没有统计数据
create table mv_base1
as
select * from hr.employees;

declare
  task_name varchar2(100);
BEGIN
  task_name := 'My taskx3';
  DBMS_ADVISOR.TUNE_MVIEW(task_name,'create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base1');
END;
/
                          *
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03113: Cannot tune the MATERIALIZED VIEW statement
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1075
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 5

begin
  dbms_stats.gather_table_stats(user,'MV_BASE1');
end;
/ 

declare
  task_name varchar2(100);
BEGIN
  task_name := 'My taskx3';
  DBMS_ADVISOR.TUNE_MVIEW(task_name,'create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base1');
END;
/

select statement from USER_TUNE_MVIEW where task_name='My taskx3';
--ok

1.2.3，没有CREATE MATERIALIZED VIEW关键词
declare
  task_name varchar2(100);
BEGIN
  task_name := 'My taskx4';
  DBMS_ADVISOR.TUNE_MVIEW(task_name,'select distinct department_id,manager_id from mv_base');
END;
/
                          *
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03112: Invalid CREATE MATERIALIZED VIEW statement
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1026
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 5

 

2,使用dbms_mview.explain_mview分析物化视图快速刷新、查询重写能力
--根据前面建议，创建物化视图:
CREATE MATERIALIZED VIEW LOG ON
"BNET"."MV_BASE" WITH ROWID, SEQUENCE ("MANAGER_ID","DEPARTMENT_ID") 
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW BNET.MV_MVIEW  
 REFRESH FAST WITH ROWID
 ENABLE QUERY REWRITE
 AS
 SELECT BNET.MV_BASE.DEPARTMENT_ID C1, BNET.MV_BASE.MANAGER_ID C2, COUNT(*) M1
 FROM BNET.MV_BASE
 GROUP BY BNET.MV_BASE.DEPARTMENT_ID, BNET.MV_BASE.MANAGER_ID;

select updatable,
      rewrite_enabled,
      rewrite_capability,
      refresh_mode,
      refresh_method,
      fast_refreshable,
      last_refresh_type,
      staleness,
      after_fast_refresh
 from user_mviews
where mview_name = 'MV_MVIEW';
U R REWRITE_C REFRES REFRESH_ FAST_REFRESHABLE   LAST_REF STALENESS           AFTER_FAST_REFRESH
- - --------- ------ -------- ------------------ -------- ------------------- -------------------
N Y GENERAL   DEMAND FAST     DIRLOAD_DML        COMPLETE FRESH               FRESH
==>fast_refreshable:DIRLOAD_DML,仅支持特定条件下的刷新

 

SQL> @?/rdbms/admin/utlxmv.sql

Table created.

SQL> exec dbms_mview.explain_mview('MV_MVIEW');

SQL> select capability_name,possible,msgtxt from MV_CAPABILITIES_TABLE;
 
CAPABILITY_NAME                POSSIBLE MSGTXT
------------------------------ -------- --------------------------------------------------------------------------------
PCT                            N       
REFRESH_COMPLETE               Y       
REFRESH_FAST                   Y        <<---refresh_mode
REWRITE                        Y       
PCT_TABLE                      N        relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT      Y       
REFRESH_FAST_AFTER_ONETAB_DML  Y       
REFRESH_FAST_AFTER_ANY_DML     Y       
REFRESH_FAST_PCT               N        PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH        Y       
REWRITE_PARTIAL_TEXT_MATCH     Y       
REWRITE_GENERAL                Y        <<---rewrite_capability
REWRITE_PCT                    N        general rewrite is not possible or PCT is not possible on any of the detail tabl
PCT_TABLE_REWRITE              N        relation is not a partitioned table
 
14 rows selected

---如果没有count(*),有什么影响
DROP MATERIALIZED VIEW BNET.MV_MVIEW;

CREATE MATERIALIZED VIEW BNET.MV_MVIEW  
 REFRESH FAST WITH ROWID
 ENABLE QUERY REWRITE
 AS
 SELECT BNET.MV_BASE.DEPARTMENT_ID C1, BNET.MV_BASE.MANAGER_ID C2
 FROM BNET.MV_BASE
 GROUP BY BNET.MV_BASE.DEPARTMENT_ID, BNET.MV_BASE.MANAGER_ID;

select updatable,
      rewrite_enabled,
      rewrite_capability,
      refresh_mode,
      refresh_method,
      fast_refreshable,
      last_refresh_type,
      staleness,
      after_fast_refresh
 from user_mviews
where mview_name = 'MV_MVIEW';
UPDATABLE REWRITE_ENABLED REWRITE_CAPABILITY REFRESH_MODE REFRESH_METHOD FAST_REFRESHABLE   LAST_REFRESH_TYPE STALENESS  AFTER_FAST_REFRESH
--------- --------------- ------------------ ------------ -------------- ------------------ ----------------- ---------- -------------------
N         Y               GENERAL            DEMAND       FAST           DIRLOAD_LIMITEDDML COMPLETE          FRESH      FRESH
==>FAST_REFRESHABLE变化：DIRLOAD_DML-->DIRLOAD_LIMITEDDML

SQL> exec dbms_mview.explain_mview('MV_MVIEW');

SQL> select capability_name,possible,msgtxt from MV_CAPABILITIES_TABLE;
CAPABILITY_NAME                POSSIBLE MSGTXT
------------------------------ -------- --------------------------------------------------------------------------------
PCT                            N       
REFRESH_COMPLETE               Y       
REFRESH_FAST                   Y       
REWRITE                        Y       
PCT_TABLE                      N        relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT      Y       
REFRESH_FAST_AFTER_ONETAB_DML  N        COUNT(*) is not present in the select list                      <==由Y变化为N
REFRESH_FAST_AFTER_ONETAB_DML  N        GROUP BY clause is present but no aggregate functions are used  <==由Y变化为N
REFRESH_FAST_AFTER_ANY_DML     N        see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled    <==由Y变化为N
REFRESH_FAST_PCT               N        PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH        Y       
REWRITE_PARTIAL_TEXT_MATCH     Y       
REWRITE_GENERAL                Y       
REWRITE_PCT                    N        general rewrite is not possible or PCT is not possible on any of the detail tabl
PCT_TABLE_REWRITE              N        relation is not a partitioned table
 
15 rows selected

3,使用dbms_mview.explain_rewrite分析SQL查询重写能力
SQL> @?/rdbms/admin/utlxrw.sql

Table created.

exec dbms_mview.explain_rewrite('select distinct DEPARTMENT_ID,MANAGER_ID from mv_base');

select query,rewritten_txt,pass from REWRITE_TABLE;
QUERY
---------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
---------------------------------------------------------------------------------------------------------------------------------
PAS
---
select distinct DEPARTMENT_ID,MANAGER_ID from mv_base
SELECT MV_MVIEW.C1 DEPARTMENT_ID,MV_MVIEW.C2 MANAGER_ID FROM BNET.MV_MVIEW MV_MVIEW
NO

select distinct DEPARTMENT_ID,MANAGER_ID from mv_base
SELECT DISTINCT MV_MVIEW.C1 DEPARTMENT_ID,MV_MVIEW.C2 MANAGER_ID FROM BNET.MV_MVIEW MV_MVIEW GROUP BY DEPARTMENT_ID,MANAGER_ID
YES
 
4,物化视图刷新
dbms_mview.refresh
dbms_mview.refresh_all_mviews
dbms_mview.refresh_dependent
dbms_refresh.refresh提供组刷新功能

5，DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY，用于针对特定负载提出物化视图等建议，结果保存在如下视图：
SYSTEM.MVIEW_RECOMMENDATIONS