[20150629]12c物化視圖刷新Out of place.txt

--11G物化視圖刷新有1個參數atomic_refresh.
--如果為false，采用的方式是truncate，再使用/*+ append */ 提示insert。這樣redo最少，但是刷新期間無法訪問。
--如果為true，采用的方式是delete，再insert。這樣產生許多redo與undo。這樣在刷新期間訪問沒問題，最多有點慢。
--自己做一個測試：

--12c在這個基礎上引入1個參數Out of place,刷新時先建立表在外部,刷新后通過類似分區交換的技術與之交換,這個刷新很形象的命名
--為out-of-place refresh.

--自己做一個測試:

1.建立測試環境:

SCOTT@test01p> @ver1

PORT_STRING??????????????????? VERSION??????? BANNER?????????????????????????????????????????????????????????????????????????????? CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0?????????? 12.1.0.1.0???? Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production????????????? 0

create table t as select * from all_objects a;

create materialized view t_mv build immediate refresh on demand enable query rewrite as
select owner, count(*) from t group by owner ;

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

@10046on 12
exec dbms_mview.refresh('T_MV','C', atomic_refresh=>false, out_of_place=>true);
@10046off

--atomic_refresh=>false, out_of_place=>true
--在刷新時先建立表RV$xxxxxx( xxxxx 表示 新表的object_id),然后插入數據,如果物化視圖有索引在插入完成后建立,命名RV$xxxxxxx.
--( xxxxx 表示 新索引的object_id).一旦以上步驟完成,做交換在數據字段內部,刪除的對象保持在recyclebin.這種方式減少redo與
--undo的生成.避免atomic_refresh=>false, out_of_place=>false的缺點.

--看看跟蹤文件:

=====================
PARSE ERROR #179385424:len=68 dep=2 uid=109 oct=26 lid=109 tim=947239691 err=1418
LOCK TABLE? FOR INDEX "SCOTT"."RV$17EDF" IN EXCLUSIVE MODE? NOWAIT
CLOSE #179385424:c=0,e=3,dep=2,type=0,tim=947239944
=====================
PARSE ERROR #350722896:len=49 dep=1 uid=109 oct=10 lid=109 tim=947246385 err=1418
/* MV_REFRESH (DIDX) */ DROP INDEX SCOTT.RV$17EDF
CLOSE #350722896:c=0,e=6,dep=1,type=0,tim=947246764
XCTEND rlbk=0, rd_only=1, tim=947247033
=====================
PARSING IN CURSOR #350722896 len=52 dep=1 uid=109 oct=12 lid=109 tim=947247214 hv=2144366463 ad='22d9988' sqlid='55vc89xzx0vvz'
/* MV_REFRESH (DTB) */ drop table "SCOTT"."RV$17EDE"
END OF STMT
.....

--先刪除表"SCOTT"."RV$17EDE",索引"SCOTT"."RV$17EDF".

=====================
PARSING IN CURSOR #350722896 len=181 dep=1 uid=109 oct=1 lid=109 tim=947494659 hv=1271955313 ad='7ff59657c00' sqlid='az9jhb95x0zvj'
/* MV_REFRESH (CTB) */CRE
END OF STMT
PARSE #350722896:c=78001,e=71340,p=0,cr=160,cu=0,mis=1,r=0,dep=1,og=1,plh=1064870033,tim=947494658
=====================

--這里是建表,僅僅看見CRE.

=====================
PARSING IN CURSOR #350722896 len=135 dep=1 uid=109 oct=2 lid=109 tim=947756432 hv=898979897 ad='7ff55b616a0' sqlid='32hyvfhutaq1t'
/* MV_REFRESH (ITB) */INSERT INTO "SCOTT"."RV$17EDE" ("OWNER","COUNT(*)") SELECT "T"."OWNER",COUNT(*) FROM "T" "T" GROUP BY "T"."OWNER"
END OF STMT
PARSE #350722896:c=15600,e=4699,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,plh=47235625,tim=947756431
=====================

=====================
PARSING IN CURSOR #350722896 len=736 dep=1 uid=109 oct=9 lid=109 tim=947886624 hv=2337637262 ad='7ff559d86b8' sqlid='1frh4hu5pb0wf'
/* MV_REFRESH (CIDX) */ CREATE UNIQUE INDEX "SCOTT"."RV$17EDF" ON "SCOTT"."RV$17EDE" (SYS_OP_MAP_NONNULL("OWNER"))
? PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
? STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
? PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
? BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
? TABLESPACE "USERS"
END OF STMT
PARSE #350722896:c=0,e=6877,p=0,cr=9,cu=1,mis=1,r=0,dep=1,og=1,plh=1685961876,tim=947886624
PARSE #350689800:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=947887312
EXEC #350689800:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,plh=0,tim=947887446
CLOSE #350689800:c=0,e=9,dep=2,type=3,tim=947887554
CLOSE #728695808:c=0,e=4,dep=2,type=3,tim=947887674
=====================
--不知道為什么要建立這個索引?看了建立的mv確實有1個索引.

--...交換后.

=====================
PARSING IN CURSOR #350722896 len=49 dep=1 uid=109 oct=10 lid=109 tim=950101269 hv=3950017501 ad='22d9988' sqlid='cfrmrnbpr0yyx'
/* MV_REFRESH (DIDX) */ DROP INDEX SCOTT.RV$17EDF
END OF STMT
====================

=====================
PARSING IN CURSOR #350722896 len=52 dep=1 uid=109 oct=12 lid=109 tim=952228430 hv=2144366463 ad='22d9988' sqlid='55vc89xzx0vvz'
/* MV_REFRESH (DTB) */ drop table "SCOTT"."RV$17EDE"
END OF STMT
PARSE #350722896:c=0,e=342,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=952228429
=====================

SCOTT@test01p> show recyclebin
ORIGINAL NAME??? RECYCLEBIN NAME??????????????? OBJECT TYPE? DROP TIME
---------------- ------------------------------ ------------ -------------------
RV$17EDE???????? BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0 TABLE??????? 2015-06-29:20:42:31

--可以發現原來的t_mv是RV$17EDE.很容易驗證.

SCOTT@test01p> select * from "BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0" minus select * from t_mv;
OWNER??? COUNT(*)
------ ----------
SYS???????? 41418

SCOTT@test01p> select * from t_mv minus select * from "BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0";
OWNER??? COUNT(*)
------ ----------
SYS???????? 41423

--正好相差5條.
--說明原來的t_mv就是"BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0",oracle在內部數據字典做了交換.

SCOTT@test01p> @16to10 17EDE
16 to 10 DEC
------------
?????? 98014

SCOTT@test01p> select object_name,original_name,operation,type,related,base_object,purge_object,CREATETIME from user_recyclebin ;
OBJECT_NAME??????????????????? ORIGINAL_NAME? OPERATION TYPE????? RELATED BASE_OBJECT PURGE_OBJECT CREATETIME
------------------------------ -------------- --------- ------ ---------- ----------- ------------ -------------------
BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0 RV$17EDE?????? DROP????? TABLE?????? 98014?????? 98014??????? 98014 2015-06-29:20:41:29

總結

以上是生活随笔為你收集整理的[20150629]12c物化视图刷新Out of place的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。