物化视图产生太多redo

 由于业务需要，创建了一些物化视图，定好每天凌晨2点刷新，但经过几天的查看，发现每天2点至3点的时候都会产生40G的归档。这些应该就是物化视图在创建时产生的归档。物化视图，说白了就是视图被物化了, 我们都说物化视图是站存储空间的，其实他的存储只是像表一样来存储的，但又和表的结果不同。具体可以查 dba_segments可以发现，在dba_segments里只能看到和物化视图同名的表。在dba_objects中可以看到物化视图还有相同名字的表。当我们删除物化视图时，表也跟着删除。
 对于物化视图产生redo日志的事情，我想大多数都不想让物化视图产生日志吧，很烦，而且日志没啥用，我们要是需要恢复的话，我们只需要重新刷新就好，干嘛要产生日志呢，但对于这个ddl语句，我们唯一能做的就是尽量避免减少日志的产生。其实当我们完全刷新一个物化视图时，他是先delete from，然后再insert，这样就会产生很多很多redo，要比原表大的多的redo。

对于delete 和insert可以通过awr来看：

上面的awr截图中，sql_id为xm结尾的sql是dbms刷新物化视图的sql，而ug结尾的delete和08结尾的insert 这2个sql的时间正好是前面xm的sql。所以可见，全完刷新是由delete和insert构成的。就算是我们将物化视图改为nologging，其实也并没有太大的帮助的。
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1010
Current log sequence           1012
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2189440 bytes
Variable Size            2181040000 bytes
Database Buffers         2080374784 bytes
Redo Buffers               12177408 bytes
Database mounted.
Database opened.
SQL>  select name,value/1024/1024 from v$sysstat where name='redo size';

NAME                                                             VALUE/1024/1024
---------------------------------------------------------------- ---------------
redo size                                                             .248466492

配置dblink：
$ vi tnsnames.ora--先配置连接
备注：由于我这里的原表是远程的库，所以要先配置。
创建dblink
SQL> conn / as sysdba
Connected.
SQL> grant dba to mao;

Grant succeeded.

SQL> conn mao/mao
Connected.
SQL> create database link SITCSE1
  2    connect to user1 identified by "user1"
  3    using 'sitcse1';

Database link created.

SQL>  create database link ZKFS1
  2    connect to user2 identified by "user2"
  3    using 'zkfs1';

Database link created.

SQL> select object_name,object_type from user_objects order by object_name;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SITCSE1                        DATABASE LINK
ZKFS1                          DATABASE LINK

SQL> select name,value/1024/1024 from v$sysstat where name='redo size';

NAME                                                             VALUE/1024/1024
---------------------------------------------------------------- ---------------
redo size                                                             .426040649

创建物化视图，查看产生多少日志：

SQL> create materialized view TB_BIZBASE_MATERIALINF
  2  refresh force on demand
  3  start with to_date('17-12-2013 02:00:00', 'dd-mm-yyyy hh24:mi:ss') next TRUNC(SYSDATE+1,'DD')+2/24 as
select * from TB_BIZBASE_MATERIALINFO@zkfs1;

Materialized view created.--执行时间约为10分钟

SQL> SQL> SQL>  select name,value/1024/1024 from v$sysstat where name='redo size';

NAME                                                             VALUE/1024/1024
---------------------------------------------------------------- ---------------
redo size                                                             3.14344025---产生了3M的日志
查看对象大小：

SQL>  select segment_name,segment_type,bytes/1024/1024 from user_segments;

SEGMENT_NAME                                                                      SEGMENT_TYPE       BYTES/1024/1024
--------------------------------------------------------------------------------- ------------------ ---------------
TB_BIZBASE_MATERIALINF                                                            TABLE                         2241
SQL> select object_name,object_type from user_objects;   

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TB_BIZBASE_MATERIALINF         MATERIALIZED VIEW
SYS_C0017613                   INDEX
TB_BIZBASE_MATERIALINF         TABLE---可以看到产生了一张和物化视图一样的表，数据块是存在这里的。
SITCSE1                        DATABASE LINK
ZKFS1                          DATABASE LINK

手动刷新，查看产生多少日志：

SQL> begin
    dbms_mview.refresh( 'TB_BIZBASE_MATERIALINF', 'C' ); ---采用完全刷新，执行时间约为100分钟。
    end;
    /

PL/SQL procedure successfully completed.

SQL> SQL> select name,value/1024/1024 from v$sysstat where name='redo size';

NAME                                                             VALUE/1024/1024
---------------------------------------------------------------- ---------------
redo size                                                             11899.2973--产生了11G的日志。

备注：完全刷新，是要先将物化视图中表的数据都删除，然后再执行插入。所以会产生很多日志。

改为快速刷新：
需要先再远程原表上创建物化视图日志：
CREATE MATERIALIZED VIEW LOG ON TB_BIZBASE_MATERIALINFO   WITH PRIMARY KEY   INCLUDING NEW VALUES; 
再创建fast方式的物化视图：
CREATE MATERIALIZED VIEW TB_BIZBASE_MATERIALINFfast
REFRESH fast ON DEMAND
AS
SELECT * FROM "TB_BIZBASE_MATERIALINFO"@"ZKFS1" "TB_BIZBASE_MATERIALINFO";

尝试执行刷新 ：

SQL> begin
  2      dbms_mview.refresh( 'TB_BIZBASE_MATERIALINFfast'); --由于我原库没有数据变动，所以很快执行完毕。但即使有变动也很快。
  3      end;
  4  /

PL/SQL procedure successfully completed.

 18号早上来查看归档情况，发现原先这些物化视图刷新的2-3点之前会产生40G的归档，刚看了下今天的，只有500M的归档，哈哈。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24500180/viewspace-1063467/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24500180/viewspace-1063467/