物化檢視相關工具包

    利用物化檢視進行查詢重寫,可以有效提高系統效能.但物化檢視查詢重寫受很多因素影響,物化檢視的快速重新整理功能也受很多因素影響.
    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        <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_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--&gtDIRLOAD_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