Oracle 物化視圖和物化視圖日誌

一、相關概念

物化視圖是將查詢預先定義在結構中，並手動或者定期刷新將結果存儲在物化視圖段中，也就是說跟普通視圖不同，它是需要存儲空間的，從而不需要重新或者反複的執行sql語句，支持增量刷新，快速獲取結果，提高數據獲取的效率。

物化視圖類型根據刷新模式，可分為on demand、on commit 。on demand 是需要刷新時才進行刷新，可以通過job或者手動進行刷新；on commit 是DML型的刷新，一旦事務commit立即刷新。

物化視圖的刷新方式有四種：fast、complete、force、never。

--fast 刷新采用增量刷新，隻刷新上次刷新以來的修改。

--complete 刷新針對整個物化視圖刷新。

--force 在刷新時oracle自動選擇刷新方式，滿足fast就增量刷新，不滿足則選擇complete。

--never 不進行任何刷新

二、管理視圖

在源數據庫端的相關視圖   

DBA_BASE_TABLE_MVIEWS   

DBA_REGISTERED_MVIEWS   

DBA_MVIEW_LOGS

在MView數據庫端的相關視圖    

DBA_MVIEWS    

DBA_MVIEW_REFRESH_TIMES   

DBA_REFRESH和DBA_REFRESH_CHILDREN

源端可以理解為基表所在的庫，數據庫端是視圖存放的位置，基表和視圖可以在同一個庫中，也可以通過dblink創建分布式的遠程的物化視圖。

三、問題處理

客戶環境Goldengate目標庫發現有大表持續增長，表空間占用緊張。

首先根據段空間管理查看近期增長頻繁的段，發現是MLOG$命名的表增長很快，MLOG$是物化視圖基表上的日誌表，根據上麵的物化視圖原理可以確定是有相關的物化視圖沒有進行快速刷新，因此這些日誌表中的數據會一直增長下去。使用easydb可以輕鬆的獲取資源增長情況，Easydb是袋鼠雲研發，目前支持了眾多雲上雲下的客戶，有關Easydb的詳情參考：https://easydb.dtstack.com。

處理步驟：

1、首先查看有多少物化視圖注冊到了刷新機製中

目標庫中隻有4個業務相關的注冊信息，

SQL> select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS;

OWNER        NAME                   MVIEW_SITE            MVIEW_ID

--------------- ------------------------------ ------------------------------ ----------

SYSMAN        MGMT_ECM_MD_ALL_TBL_COLUMNS    SEEDDATA                    0

USER2    CIPMV_T_REPORT_2           USER1                      44

USER2    CIPMV_T_REPORT_1           USER1                      43

USER2    CIPMV_T_ORDER_2            USER1                      42

USER2    CIPMV_T_ORDER_1            USER1                      41

存在的物化視圖日誌

SQL> select table_name,owner from dba_tables where table_name like 'MLOG$%';

TABLE_NAME                           OWNER

-------------------------------------------------------------------------- ---------------

MLOG$_T_REPORT                         USER1

MLOG$_T_PATIENT                        USER1

MLOG$_T_ORDERREPORTLINK       USER1

MLOG$_T_ORDER                           USER1

2、查找近期進行刷新的物化視圖，確定哪些物化視圖沒有進行刷新，發現有幾個MVID對應的物化視圖是不存在的，有可能這些物化視圖是遠程數據庫上的。

主庫上的mvid信息

SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS;

OWNER        MASTER                  MVIEW_LAST_REFRESH_     MVIEW_ID

--------------- --------------------------------------------- ------------------- ----------

USER1        T_PATIENT                  2017-09-20 20:00:12        1

USER1        T_REPORT                  2017-09-20 20:00:47           46

USER1        T_ORDER                   2017-09-20 20:03:02           45

USER1        T_ORDERREPORTLINK          2017-09-20 20:03:02           61

USER1        T_ORDER                   2017-09-26 14:03:21           42

USER1        T_REPORT                  2017-09-26 14:03:21           44

USER1        T_ORDER                   2017-09-26 14:03:22           41

USER1        T_REPORT                  2017-09-26 14:03:22           43

備庫上已經沒有了

SQL> select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS;

OWNER        NAME                   MVIEW_SITE            MVIEW_ID

--------------- ------------------------------ ------------------------------ ----------

SYSMAN        MGMT_ECM_MD_ALL_TBL_COLUMNS    SEEDDATA                    0

USER2    CIPMV_T_REPORT_2           USER1                      44

USER2    CIPMV_T_REPORT_1           USER1                      43

USER2    CIPMV_T_ORDER_2            USER1                      42

USER2    CIPMV_T_ORDER_1            USER1                      41

3、跟客戶確認環境，這個庫是個災備庫，不需要這些視圖刷新，此時我們把這些無效的且注冊的物化視圖信息去掉.

begin

DBMS_MVIEW.UNREGISTER_MVIEW('DBLINK','RIS_T_PATIENT_1', 'ORCL');

end;

/

4、根據刷新情況清空物化視圖日誌

EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(46);

EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(61);

EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(1);

5、此時本地物化視圖刷新後，MLOG$中的數據就會隨著刷新而清空了。

6、存在的物化視圖進行自動刷新

--快速刷新

begin

     dbms_mview.refresh('USER2.CIPMV_T_REPORT_2','F');

end;

/

begin

     dbms_mview.refresh('USER2.CIPMV_T_REPORT_1','F');

end;

/

begin

     dbms_mview.refresh('USER2.CIPMV_T_ORDER_2','F');

end;

/

begin

     dbms_mview.refresh('USER2.CIPMV_T_ORDER_1','F');

end;

/

--定時刷新

alter materialized view USER2.CIPMV_T_REPORT_2 refresh fast on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');

alter materialized view USER2.CIPMV_T_REPORT_1 refresh fast on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:01:00'),'dd-mm-yyyy hh24:mi:ss');

alter materialized view USER2.CIPMV_T_ORDER_2 refresh fast on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:02:00'),'dd-mm-yyyy hh24:mi:ss');

alter materialized view USER2.CIPMV_T_ORDER_1 refresh fast on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:03:00'),'dd-mm-yyyy hh24:mi:ss');

總結：本次問題的原因是表被多個物化視圖使用，且包含遠程物化視圖，MLOG物化視圖日誌如果不被所有已注冊的物化視圖刷新是不會清空的，保留正常的物化視圖即可。