[20150705]12c物化视图刷新Out of place2.txt

--11G物化视图刷新有1个参数atomic_refresh.
--如果为false，采用的方式是truncate，再使用/*+ append */ 提示insert。这样redo最少，但是刷新期间无法访问。
--如果为true，采用的方式是delete，再insert。这样产生许多redo与undo。这样在刷新期间访问没问题，最多有点慢。
--自己做一个测试：

--12c在这个基础上引入1个参数Out of place,刷新时先建立表在外部,刷新后通过类似分区交换的技术与之交换,这个刷新很形象的命名
--为out-of-place refresh.

--上次做的使用10046跟踪不是很好观察,这次使用logminer发掘看看.

--自己做一个测试:

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

--做一个清空回收站.
SCOTT@test01p> purge recyclebin;
Recyclebin purged.

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 ;

SCOTT@test01p> select index_name from dba_indexes where owner=user and table_name='T_MV';
INDEX_NAME
-------------
I_SNAP$_T_MV

SCOTT@test01p> select object_id,data_object_id ,object_name,object_type from dba_objects where owner=user and object_name in ('T','T_MV','I_SNAP$_T_MV');
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- -------------- ------------------------------ -----------------------
     98094                T_MV                           MATERIALIZED VIEW
     98092          98092 T_MV                           TABLE
     98091          98091 T                              TABLE
     98093          98093 I_SNAP$_T_MV                   INDEX
--注意 OBJECT_ID DATA_OBJECT_ID.下面作为观察很重要.

2.开始测试:

SCOTT@test01p> select sysdate from dual ;
SYSDATE
-------------------
2015-07-05 22:09:55

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

exec dbms_mview.refresh('T_MV','C', atomic_refresh=>false, out_of_place=>true);

SCOTT@test01p> select sysdate from dual ;
SYSDATE
-------------------
2015-07-05 22:10:29

--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的缺点.

3.使用logminer发掘:

--以sys用户登录,12c不能在pluggable database执行如下命令,否则报如下错误.

ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 2

BEGIN
   DBMS_LOGMNR.START_LOGMNR (
      STARTTIME   => '2015-07-05 22:09:54',
      ENDTIME     => '2015-07-05 22:10:29',
      OPTIONS     =>   DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
                     + DBMS_LOGMNR.CONTINUOUS_MINE);
END;
/

SYS@test> column c100 format a100
SYS@test> select sql_redo c100,timestamp,data_obj# from V$LOGMNR_CONTENTS where SEG_OWNER='SCOTT' ;
C100                                                                                                 TIMESTAMP            DATA_OBJ#
---------------------------------------------------------------------------------------------------- ------------------- ----------
insert into "SCOTT"."T"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_ 2015-07-05 22:10:14      98091
TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE"
,"EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED") values ('SYS','I_OBJ1',NULL,'36','36','
INDEX',TO_DATE('2013-06-28 09:03:29', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2013-06-28 09:03:29', 'YYYY-
MM-DD HH24:MI:SS'),'2013-06-28:09:03:29','VALID','N','N','N','4',NULL,'NONE',NULL,'Y');
--插入1行.

/* MV_REFRESH (CTB) */CREATE TABLE RV$17F2C ("OWNER","COUNT(*)")  AS SELECT  * FROM (SELECT "T"."OWN 2015-07-05 22:10:26      98095
ER" "OWNER",COUNT(*) "COUNT(*)" FROM "T" "T" GROUP BY "T"."OWNER") WHERE 1 = NULL;
--建立表CREATE TABLE RV$17F2C.

insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('APEX_040200','3009');                        2015-07-05 22:10:26      98095
-- snap ....
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('BI','8');                                    2015-07-05 22:10:26      98095
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('SYS','41418');                               2015-07-05 22:10:26      98095
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('WMSYS','369');                               2015-07-05 22:10:26      98095
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('SI_INFORMTN_SCHEMA','8');                    2015-07-05 22:10:26      98095
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('LBACSYS','209');                             2015-07-05 22:10:26      98095
---注意看~~~,这里实际上应该还是RV$17F26表,因为数据字典的缘故.后面的data_obj#没有变化.

/* MV_REFRESH (CIDX) */ CREATE UNIQUE INDEX "SCOTT"."RV$17F2D" ON "SCOTT"."RV$17F2C" (SYS_OP_MAP_NON 2015-07-05 22:10:26      98096
NULL("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" ;

/* MV_REFRESH (DIDX) */ DROP INDEX SCOTT.RV$17F2D;                                                   2015-07-05 22:10:26      98093
ALTER TABLE "SCOTT"."RV$17F2C" RENAME CONSTRAINT "SYS_C0011081" TO "BIN$izvCtfj/TMyq7Q/mJff0xg==$0"  2015-07-05 22:10:27      98092
;
--这里删除的原来的T_mv表的索引以及改名,注意看后面的DATA_OBJ#

ALTER TABLE "SCOTT"."RV$17F2C" RENAME TO "BIN$LCNGIqa6SD2T5PDkG3yfxg==$0" ;                          2015-07-05 22:10:27      98092
/* MV_REFRESH (DTB) */ drop table "SCOTT"."RV$17F2C" AS "BIN$LCNGIqa6SD2T5PDkG3yfxg==$0" ;           2015-07-05 22:10:27      98092
--这里删除的原来的T_mv表以及改名,注意看后面的DATA_OBJ#

39 rows selected.

4.分析:

SCOTT@test01p> select * from t_mv where owner='SYS';
OWNER    COUNT(*)
------ ----------
SYS         41418

SCOTT@test01p> select * from  "SCOTT"."BIN$LCNGIqa6SD2T5PDkG3yfxg==$0" where owner='SYS';
OWNER    COUNT(*)
------ ----------
SYS         41417

--正好相差1条.

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$LCNGIqa6SD2T5PDkG3yfxg==$0 RV$17F2C       DROP      TABLE    98092       98092        98092 2015-07-05:22:06:17

SCOTT@test01p> @10to16 98092
10 to 16 HEX   REVERSE16
-------------- ------------------
0000000017f2c 0x2c7f0100

SCOTT@test01p> select object_id,data_object_id ,object_name,object_type from dba_objects where owner=user and object_name in ('T','T_MV','I_SNAP$_T_MV');
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- -------------- ------------------------------ -----------------------
     98094                T_MV                           MATERIALIZED VIEW
     98095          98095 T_MV                           TABLE
     98091          98091 T                              TABLE
     98096          98096 I_SNAP$_T_MV                   INDEX

--对比前面的 OBJECT_ID DATA_OBJECT_ID,可以发现t_MV对应的表依旧它的索引的OBJECT_ID DATA_OBJECT_ID都发生了变化.