[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 <=5;
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.

SCOTT@test> @viewredo
NAME                                                         STATISTIC#      VALUE
------------------------------------------------------------ ---------- ----------
user commits                                                          6          8
redo size                                                           178    8945556
redo wastage                                                        183          0
data blocks consistent reads - undo records applied                 293          0

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

3.测试atomic_refresh=>true：

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=>true);
PL/SQL procedure successfully completed.

SCOTT@test> @viewredo
NAME                                                         STATISTIC#      VALUE
------------------------------------------------------------ ---------- ----------
user commits                                                          6          5
redo size                                                           178   38591500
redo wastage                                                        183          0
data blocks consistent reads - undo records applied                 293          0

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

4.做一个10046跟踪看看。

SCOTT@test> @10046on 12
Session altered.

SCOTT@test> exec dbms_mview.refresh('T_MV','C', atomic_refresh=>false);
PL/SQL procedure successfully completed.

SCOTT@test> @10046off
Session altered.

--查看跟踪文件：

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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.34       0.54          2       1162       1719       75118
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.34       0.54          2       1163       1719       75118

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84     (recursive depth: 1)
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)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  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
********************************************************************************

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

转载于:http://blog.itpub.net/267265/viewspace-1715789/