[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.
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
********************************************************************************
