|
Oracle 10201，OS： Redhat as4
问题描述：创建了一个增量刷新的物化视图，但是每次刷新的时间相当长， 源表数据量变化不大每天40-50w，源表已有数据量大约1500W 创建物化视图log sql CREATE MATERIALIZED VIEW LOG ON BANDWIDTH_MONITOR_CHANNEL WITH rowid( CHANNEL_ID, USER_ID, BUSINESS_TYPE, NODE_ID, CUR_TIME, BANDWIDTH_IN, BANDWIDTH_OUT ) INCLUDING NEW VALUES; 创建物化视图sql
CREATE MATERIALIZED VIEW MV_BANDWIDTH_MONITOR_USER2 REFRESH FAST ON DEMAND START WITH TO_DATE('08-01-2008 10:05:17', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1/1440 AS SELECT a.USER_ID, a.CUR_TIME, SUM(a.BANDWIDTH_IN) AS TOTAL_IN, SUM(a.BANDWIDTH_OUT) AS TOTAL_OUT, COUNT(a.BANDWIDTH_IN) AS COUNT_IN, COUNT (a.BANDWIDTH_OUT) AS COUNT_OUT, COUNT (*) AS COUNT_ALL FROM BANDWIDTH_MONITOR_CHANNEL a GROUP BY a.USER_ID, a.CUR_TIME 查看物化视图log中的数据量，发现与源表数据量一致 SQL> select count(*) from mlog$_bandwidth_monitor_ch; COUNT(*)
---------- 15423230 执行一次刷新
SQL> exec dbms_refresh.refresh('"BWMONITOR"."MV_BANDWIDTH_MONITOR_USER2"'); PL/SQL procedure successfully completed.
再次检查数据量发现该log中的数量并未减少
SQL> select count(*) from mlog$_bandwidth_monitor_ch; COUNT(*)
---------- 15425623 此时怀疑执行的是完全刷新
查看物化视图的状态，可以得到下一次应该执行哪种刷新 SQL> select mview_name, last_refresh_date, staleness from user_mviews;
MVIEW_NAME LAST_REFR STALENESS
------------------------------ --------- ------------------- MV_BANDWIDTH_MONITOR_USER2 08-JAN-08 NEEDS_COMPILE 再次执行exec dbms_refresh.refresh('"BWMONITOR"."MV_BANDWIDTH_MONITOR_USER2"');后
结果仍然是 SQL> select mview_name, last_refresh_date, staleness from user_mviews; MVIEW_NAME LAST_REFR STALENESS
------------------------------ --------- ------------------- MV_BANDWIDTH_MONITOR_USER2 08-JAN-08 NEEDS_COMPILE 由此可以确定是完全刷新，这是什么原因导致的？ 检查视图DBA_BASE_TABLE_MVIEWS
该视图表示基于某个表下的物化视图的一些信息。 SQL> select * from DBA_BASE_TABLE_MVIEWS;
OWNER MASTER MVIEW_LAS
------------------------------ ------------------------------ --------- MVIEW_ID ---------- BWMONITOR BANDWIDTH_MONITOR_CHANNEL 08-JAN-08 22 BWMONITOR BANDWIDTH_MONITOR_CHANNEL 07-DEC-07
1 检查已经注册的物化视图，此时发现MVIEW_ID=1的物化视图没有注册
SQL> select MVIEW_ID from ALL_REGISTERED_MVIEWS;
MVIEW_ID
---------- 22 SQL>
而在我们的实际应用中也只有一个物化视图，由此可以判定是MVIEW_ID=1搞的鬼
产生的原因怀疑是第一次创建物化视图的时候没有成功，而Oracle回滚的时候也没有将 视图DBA_BASE_TABLE_MVIEWS中MVIEW_ID=1的记录清除掉 于是将其从mv log中删除 --删除的MVIEW_ID应该是不需要的MVIEW对应的ID SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(1); 经过漫长等待执行成功
SQL> select * from DBA_BASE_TABLE_MVIEWS; OWNER MASTER MVIEW_LAS
------------------------------ ------------------------------ --------- MVIEW_ID ---------- BWMONITOR BANDWIDTH_MONITOR_CHANNEL 08-JAN-08 22 SQL> 检查物化视图日志表数据量明显减少 SQL> select count(*) from mlog$_bandwidth_monitor_ch;
COUNT(*)
---------- 262981 执行一次增量刷新
SQL> exec dbms_refresh.refresh('"BWMONITOR"."MV_BANDWIDTH_MONITOR_USER2"'); PL/SQL procedure successfully completed.
物化视图日志表中的数据清空
SQL> select count(*) from mlog$_bandwidth_monitor_ch; COUNT(*)
---------- 0 SQL>
清除物化视图注册信息
SQL> exec DBMS_MVIEW.unregister_mview('TEST','ITEM_TAG','SC1.SOUCHANG.COM'); PL/SQL procedure successfully completed
nested exception is java.sql.BatchUpdateException: ORA-12096: error in materialized view log on "BANDWIDTH_MONITOR_CHANNEL"
ORA-00942: table or view does not exist [oracle@CCN-BJ-6-579 ~]$ oerr ora 12096
12096, 00000, "error in materialized view log on \"%s\".\"%s\"" // *Cause: There was an error originating from this materialized view log. // One possible cause is that schema redefinition has occurred // on the master table and one or more columns in the log is now // a different type than corresponding master column(s). Another // possible cause is that there is a problem accessing the // underlying materialized view log table. // *Action: Check further error messages in stack for more detail about // the cause. If there has been schema redefinition, drop the // materialized view log and recreate it. // 经过检查发现没有表BANDWIDTH_MONITOR_CHANNEL的mv log [oracle@CCN-BJ-6-579 ~]$ sqlplus bwmon/******* SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 9 10:47:42 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> drop materialized view log on BANDWIDTH_MONITOR_CHANNEL;
Materialized view log dropped.
SQL>
|
|
|
