[20150629]物化视图刷新atomic_refresh.txt

[20150629]物化视图刷新atomic_refresh.txt

--11G物化视图刷新有1个参数atomic_refresh.

--如果为false，采用的方式是truncate，再使用/*+ append */ 提示insert。这样redo最少，但是刷新期间无法访问。

--如果为true，采用的方式是delete，再insert。这样产生许多redo与undo。这样在刷新期间访问没问题，最多有点慢。

--自己做一个测试：

1.建立测试环境：

SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t as select * from all_objects a;

create materialized view t_mv build immediate refresh on demand enable query rewrite as

select * from t  ;

insert into t select * from all_objects a where rownum commit;

2.测试atomic_refresh=>false：

SCOTT@test> @viewredo

NAME                                                         STATISTIC#      VALUE

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

user commits                                                          6          0

redo size                                                           178       1624

redo wastage                                                        183          0

data blocks consistent reads - undo records applied                 293          0

SCOTT@test> exec dbms_mview.refresh('T_MV','C', atomic_refresh=>false);

PL/SQL procedure successfully completed.

user commits                                                          6          8

redo size                                                           178    8945556

--redo size = 8945556-1624=8943932 (大约9M)

3.测试atomic_refresh=>true：

SCOTT@test> exec dbms_mview.refresh('T_MV','C', atomic_refresh=>true);

user commits                                                          6          5

redo size                                                           178   38591500

--redo size = 38591500-1624=38589876 (38M)

4.做一个10046跟踪看看。

SCOTT@test> @10046on 12

Session altered.

SCOTT@test> @10046off

--查看跟踪文件：

SQL ID: 2np117amcrkzv Plan Hash: 1163268061

truncate table "SCOTT"."T_MV" purge snapshot log

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          1           0

Execute      1      0.01       0.55          5          1        248           0

Fetch        0      0.00       0.00          0          0          0           0

total        2      0.01       0.56          5          1        249           0

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84     (recursive depth: 1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  Disk file operations I/O                        2        0.00          0.00

  reliable message                                2        0.00          0.00

  enq: RO - fast object reuse                     1        0.50          0.50

  db file sequential read                         5        0.00          0.00

  local write wait                                3        0.01          0.04

********************************************************************************

SQL ID: 5zvka3vvfgdvb Plan Hash: 570131543

INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND  */ INTO "SCOTT"."T_MV" select *

  from t

Parse        1      0.00       0.00          0          1          0           0

Execute      1      0.34       0.54          2       1162       1719       75118

total        2      0.34       0.54          2       1163       1719       75118

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

         0          0          0  LOAD AS SELECT  (cr=1257 pr=2 pw=1066 time=554942 us)

     75118      75118      75118   TABLE ACCESS FULL T (cr=1106 pr=0 pw=0 time=105001 us cost=315 size=13027100 card=82450)

  db file sequential read                         2        0.00          0.00

  Disk file operations I/O                        1        0.00          0.00

  direct path sync                                1        0.21          0.21