完全刷新物化视图和undo使用量
完全刷新物化视图和undo使用量
生产一系统在最近变更中增加了物化视图，可是在完全刷新时alertlog日志报错，信息如下：
Thu May 12 05:48:03 2011
Thread 1 advanced to log sequence 7070 (LGWR switch)
Current log# 1 seq# 7070 mem# 0: /oradata/rccadb/redo01.log
Thu May 12 05:48:50 2011
Errors in file /oracle/admin/rccadb/bdump/rccadb_j001_2966.trc:
ORA-12012: error on auto execute of job 75
ORA-12008: error in materialized view refresh path
ORA-30036: unable to extend segment by8 inundo tablespace 'UNDOTBS1'
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Thu May 12 05:49:16 2011
Thread 1 advanced to log sequence 7071 (LGWR switch)
Current log# 2 seq# 7071 mem# 0: /oradata/rccadb/redo02.log
很显然，undo表空间过小，导致定时作业刷新物化视图失败。
该系统oracle版本10.2.0.4。10g后，物化视图的complete refresh默认是用delete来清理原记录，目的是为了保证原子性及读一致性，这会导致undo的大量使用。
而在10g前，complete refresh的行为默认是truncate，可以指定atomic_refresh=>FALSE来使用truncate的行为。该行为会使查询结果突然为0，对业务可能造成短期影响。
exec dbms_mview.refresh('
Complete Refresh Read Consistency Behavior. During Refresh and Complete Refresh Performance as Influenced by the ATOMIC_REFRESH Refresh Parameter
The ATOMIC_REFRESH parameter of the DBMS_MVIEW.REFRESH, DBMS_MVIEW.REFRESH_ALL_MVIEWS, and DBMS_MVIEW.REFRESH_DEPENDENT procedures influences a number of characteristics of materialized view complete refresh, and behaves differently depending upon the version of the Oracle server.
The dafault value for the ATOMIC_REFRESH parameter of the DBMS_MVIEW.REFRESH, DBMS_MVIEW.REFRESH_ALL_MVIEWS, and DBMS_MVIEW.REFRESH_DEPENDENT procedures is TRUE for all versions, but in10gand above the behavior. associated with this parameter has changed.
In 9.2, if ATOMIC_REFRESH is set to TRUE (the default):
If a single materialized view is being complete refreshed, the materialized view will be truncated and then the data inserted back in. This means that queries initiated against the materialized view will return zero rows for the duration of the refresh. In9.2 atruncate is always done when a single materialized view is refreshed, regardless of the setting of the ATOMIC_REFRESH parameter.
If a group of materialized views are being complete refreshed, each materialized view in the group will be deleted from row-by-row prior to inserting the data back in. In addition, all materialized views are refreshed together in the same transaction; either all refreshes succeed, or if one fails all of the refreshes will be
rolled back. This maintains read consistency, meaning that queries against the materialized views will return read consistent results during the refresh because they are able to access undo information.
In 9.2, if ATOMIC_REFRESH is set to FALSE:
If a single materialized view is being complete refreshed, the materialized view will still be truncated. As said above, in9.2 atruncate is always done when a single materialized view is refreshed, regardless of the setting of the ATOMIC_REFRESH parameter.
If a group of materialized views are being complete refreshed, each one will be truncated and refreshed individually in a separate transaction. The number of job queue processes must be set to one or greater if this parameter is false.
In10gand above, if ATOMIC_REFRESH is set to TRUE (the default):
If a single materialized view is being refreshed, a row-by-row delete will be done prior to inserting rows back into the materialized view. This makes the refresh slower, but also maintains a principle of atomicity that implies that the materialized view should allow read-consistent queries against it during the refresh for the refresh to be atomic. This is why the change in behavior. of the ATOMIC_REFRESH parameter was done.
If a group of materialized views are being complete refreshed, each materialized view in the group will be deleted from row-by-row prior to inserting the data back in. In addition, all materialized views are refreshed together in the same transaction; either all refreshes succeed, or if one fails all of the refreshes will be rolled back. This is the same behavior. as in 9.2.
In10gand above, if ATOMIC_REFRESH is set to FALSE, the behavior. is the same as in 9.2, i.e:
If a single materialized view is being complete refreshed, the materialized view will be truncated.
If a group of materialized views are being complete refreshed, each one will be truncated and refreshed individually in a separate transaction. The number of job queue processes must be set to 1 or greater if this parameter is false.