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

[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 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都发生了变化.

时间： 2024-09-20 17:20:03

[20150705]12c物化视图刷新Out of place2的相关文章

物化视图刷新原理与性能诊断

参考文档:Materialized View Refresh: Locking, Performance, Monitoring (文档 ID 258252.1) How to Monitor the Progress of a Materialized View Refresh (MVIEW) (文档 ID 258021.1) 1.名词解释: 基表 指的是英文里面的Master Table和Master Materialized View,并不只是只一个表,而是创建MView的时候所需要用到的

[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

特殊的物化视图刷新

现在有一个需求,某个环境中存在两个用户,一个用户中存在物化视图,另一个用户中存在源表,根据业务的需要,需要做一种特别的物化视图刷新. 物化视图用户中的物化视图为CORP_NAME 源数据用户中的表为ADD_CORP_NAME 可能数据刷新是没有问题,关键就是在于CORP_NAME中的字段要比ADD_CORP_NAME多一些.CORP_NAME           ADD_CORP_NAME CORP_ID               |  CORP_ID            SYS_CREAT

物化视图刷新结合ADG的尝试

之前写过一篇 物化视图刷新结合ADG的尝试,想必绝大多数的朋友看完再没有深究,其实也有些朋友做了建议,让我尝试prebuilt来做.这种数据迁移方式用的比较少,但是个人感觉还是很不错的.如果迁移的表不是很多,这种迁移方式还是非常强大的. 如果一个表非常大,我目前的设想就是通过ADG备库来把数据首先同步到统计库中,然后在主库端通过物化视图日志来增量刷新. 使用物化视图 prebuilt的方式确实可以实现,我产生了几个疑问,物化视图日志该什么时候创建.创建的时间太早或者太晚,对于增量刷新是否有影响,

oracle如何利用触发器对物化视图刷新进行定制

物化视图的刷新其实和普通的SQL执行没有什么本质的区别,因此也可以通过在物化视图上创建触发器的方式,对刷新操作进行定制. 正好前两天有人在BLOG上问我,如果在物化视图添加一个时间戳列,并在物化视图更新的时候,自动维护这个列.其实很简单,通过触发器就可以达到这个目的: SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30)); 表已创建. SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM

物化视图刷新失败导致日志表异常增大

整理自:http://blog.itpub.net/231499/viewspace-63714/ 今天在检查时,发现某个物化视图日志占用的空间超过150M,再检查看,该物化视图日志表的记录数有150W,由于其对应的物化视图没有会刷新一次,结合业务量分析可知:物化视图日志不能正常清除. 下面的解决步骤 --在源库查询物化视图对应日志条目个数SQL> select count(1) from MLOG$_ITEM_TAG; COUNT(1)----------532515 --在物化视图端刷新物化

物化视图刷新的问题及分析

最近现场需要搭建一套全新的环境,对于数据字典的管理采用了物化视图,因为数据量不大,采用了全量刷新的方式.因为有好几套环境,有几套环境是通过db link和主节点的表创建的物化视图,这几个节点间的网络情况不好,刷新一个稍微大一些的表或者带有lob字段的表时,速度会很慢,因为有好几套环境,一套一套的等待刷新完得花费不少的时间,所以自己想写一个shell脚本让它在后台慢慢跑,这样过一段时间再看看日志保证数据都已经刷新完毕就可以了. 原本采用的方式是 create materialized view x

【物化视图】根据物化视图日志快速刷新物化视图的过程

先来再次分析一下物化视图日志的结构. yang@rac1>create table t (id number ,name varchar2(30),val number); Table created. yang@rac1>create materialized view log on t with rowid,sequence (id,name) including  new values; Materialized view log created. yang@rac1>desc m

oracle基于COMMIT SCN的物化视图日志快速刷新的方法

11.2中物化视图日志可以指定COMMIT SCN,这时物化视图刷新就不需要时间戳了,这篇简单描述Oracle是如何实现通过COMMIT SCN来进行刷新的. 建立一个测试环境: SQL> SELECT * FROM V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle Database11gEnterprise Edition Relea