[20181116]18c DML 日志优化.txt

[20181116]18c dml 日志优化.txt

1.环境:
xxxxxxxx> select banner_full from v$version;
banner_full
-----------------------------------------------------------------------------
oracle database 18c enterprise edition release 18.0.0.0.0 - production
version 18.3.0.0.0

2.建立测试脚本:
xxxxxxxx> create table t as select rownum id ,lpad('y',200,'y') v1,lpad('z',100,'z') v2 from dual connect by level<=100;
table created.

xxxxxxxx> select dump('x',16),dump('y',16),dump('z',16) from dual ;
dump('x',16)                     dump('y',16)                     dump('z',16)
-------------------------------- -------------------------------- --------------------------------
typ=96 len=1: 78                 typ=96 len=1: 79                 typ=96 len=1: 7a

xxxxxxxx> @ viewsess redo%size
name                               statistic#          value        sid
---------------------------------- ---------- -------------- ----------
redo size                                 293            684        258
redo size for lost write detection        295              0        258
redo size for direct writes               296              0        258
redo write size count (   4kb)            321              0        258
redo write size count (   8kb)            322              0        258
redo write size count (  16kb)            323              0        258
redo write size count (  32kb)            324              0        258
redo write size count (  64kb)            325              0        258
redo write size count ( 128kb)            326              0        258
redo write size count ( 256kb)            327              0        258
redo write size count ( 512kb)            328              0        258
redo write size count (1024kb)            329              0        258
redo write size count (inf)               330              0        258
imu redo allocation size                  736              0        258
14 rows selected.

--//建立测试脚本a.txt
column member new_value v_member
column member noprint
set numw 12
alter system switch logfile ;
--//alter system archive log current;
--//12c以上不允许在pluggable database执行这条命令.注这个库没有打开归档,alter system archive log current;会报错.
--//采用alter system switch logfile ;.
select  member from v$log a, v$logfile b where a.group#(+) = b.group# and a.status='current' and rownum=1;

column curr1 new_value v_curr1
select current_scn curr1 from v$database;

--//以下操作dml内容:
update t set v1=lpad('y',200,'y') ,v2=lpad('z',100,'z');
commit ;

exec dbms_session.sleep(3);

column curr2 new_value v_curr2
select current_scn curr2 from v$database;

prompt exec dbms_logmnr.start_logmnr(startscn => &&v_curr1 ,endscn  => &&v_curr2 ,options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine);
prompt alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;

--//执行脚本a.txt:
xxxxxxxx> @ a.txt
system altered.
       curr1
------------
  1336662128
100 rows updated.

commit complete.

pl/sql procedure successfully completed.
       curr2
------------
  1336662131

exec dbms_logmnr.start_logmnr(startscn =>   1336662128 ,endscn  =>   1336662131 ,options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine)
alter system dump logfile '+data/orclcdb/onlinelog/group_2.264.985030477' scn min   1336662128 scn max   1336662131

system altered.

xxxxxxxx> @ viewsess redo%size
name                               statistic#          value          sid
---------------------------------- ---------- -------------- ------------
redo size                                 293          23692          258
redo size for lost write detection        295              0          258
redo size for direct writes               296              0          258
redo write size count (   4kb)            321              0          258
redo write size count (   8kb)            322              0          258
redo write size count (  16kb)            323              0          258
redo write size count (  32kb)            324              0          258
redo write size count (  64kb)            325              0          258
redo write size count ( 128kb)            326              0          258
redo write size count ( 256kb)            327              0          258
redo write size count ( 512kb)            328              0          258
redo write size count (1024kb)            329              0          258
redo write size count (inf)               330              0          258
imu redo allocation size                  736          49012          258

14 rows selected.

--//日志大小 23692-648 = 23044 ,大约22k.
--//如果你查询转储文件,你可以发现一个奇特的信息,你根本找不到yyyy,zzzz相关的字符串信息.
--//也就是如果dml字段前后两者一样,日志根本没有记录.

3.修改dml语句重复测试:
$ cat a.txt
..
update t set v1=lpad('z',200,'z') ,v2=lpad('y',100,'y');
commit ;
...

--//执行a.txt
xxxxxxxx> @ viewsess redo%size
name                               statistic#          value        sid
---------------------------------- ---------- -------------- ----------
redo size                                 293            684        258
redo size for lost write detection        295              0        258
redo size for direct writes               296              0        258
redo write size count (   4kb)            321              0        258
redo write size count (   8kb)            322              0        258
redo write size count (  16kb)            323              0        258
redo write size count (  32kb)            324              0        258
redo write size count (  64kb)            325              0        258
redo write size count ( 128kb)            326              0        258
redo write size count ( 256kb)            327              0        258
redo write size count ( 512kb)            328              0        258
redo write size count (1024kb)            329              0        258
redo write size count (inf)               330              0        258
imu redo allocation size                  736              0        258
14 rows selected.

xxxxxxxx> @ a.txt
system altered.
       curr1
------------
  1336662429
100 rows updated.
commit complete.

pl/sql procedure successfully completed.
       curr2
------------
  1336662432

exec dbms_logmnr.start_logmnr(startscn =>   1336662429 ,endscn  =>   1336662432 ,options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine)
alter system dump logfile '+data/orclcdb/onlinelog/group_3.262.985030477' scn min   1336662429 scn max   1336662432
system altered.

xxxxxxxx> @ viewsess redo%size
name                               statistic#          value          sid
---------------------------------- ---------- -------------- ------------
redo size                                 293          73948          258
redo size for lost write detection        295              0          258
redo size for direct writes               296              0          258
redo write size count (   4kb)            321              0          258
redo write size count (   8kb)            322              0          258
redo write size count (  16kb)            323              0          258
redo write size count (  32kb)            324              0          258
redo write size count (  64kb)            325              0          258
redo write size count ( 128kb)            326              0          258
redo write size count ( 256kb)            327              0          258
redo write size count ( 512kb)            328              0          258
redo write size count (1024kb)            329              0          258
redo write size count (inf)               330              0          258
imu redo allocation size                  736          36236          258
14 rows selected.

--//日志大小73948-648 = 73300,至少72k,比原来增加不少.
--//你可以看到如下:
redo record - thread:1 rba: 0x0008b3.0000002c.0134 len: 0x00e0 vld: 0x01 con_uid: 1
scn: 0x000000004fabd870 subscn:  2 11/16/2018 10:30:36
change #1 con_id:1 typ:0 cls:24 afn:4 dba:0x010008de obj:4294967295 scn:0x000000004fabd870 seq:96 op:5.1 enc:0 rbl:0 flg:0x0000
ktudb redo: siz: 68 spc: 866 flg: 0x0022 seq: 0x0ba1 rec: 0x69
            xid:  0x0004.004.00002086
ktubu redo: slt: 4 rci: 104 opc: 11.1 objn: 104890 objd: 104890 tsn: 0
undo type:  regular undo       undo type:  last buffer split:  no
tablespace undo:  no
             0x00000000
kdo undo record:
ktb redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: c  uba: 0x010008de.0ba1.68
kdo op code: lkr row dependencies disabled
ktudb redo: siz: 68 spc: 656 flg: 0x0022 seq: 0x0ba1 rec: 0x6c
op: f  xid:  0x0006.00d.00002131    uba: 0x010000c8.0b0c.01
block cleanout record, scn:  0x000000004fabd99d ver: 0x01 opt: 0x02 bigscn: y compact: y spare: 00000000, entries follow...
  itli: 3  flg: (opt=2 whr=1)  scn:  0x000000004fabd871
array update of 20 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 0
ncol: 3 nnew: 2 size: 0
kdo op code:  21 row dependencies disabled
  xtype: xaxtype kdo_kdom2 flags: 0x00000080  bdba: 0x0041af39  hdba: 0x0041af38
itli: 1  ispac: 0  maxfr: 4863
vect = 3
col  1: [200]
 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
col  2: [100]
 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 1 ckix: 0

4.修改dml语句重复测试:
$ cat a.txt
..
update t set v1=lpad('z',200,'z') ,v2=lpad('x',100,'x');
commit ;
...

--//v1字段修改前后一样,v2字段前后不同,开始是lpad('y',100,'y')后面变成lpad('x',100,'x').

xxxxxxxx> @ viewsess redo%size
name        statistic#          value        sid
----------- ---------- -------------- ----------
redo size          293            684        258
....

xxxxxxxx> @ a.txt
system altered.
       curr1
------------
  1336662656
100 rows updated.
commit complete.
pl/sql procedure successfully completed.
       curr2
------------
  1336662659

exec dbms_logmnr.start_logmnr(startscn =>   1336662656 ,endscn  =>   1336662659 ,options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.continuous_mine)
alter system dump logfile '+data/orclcdb/onlinelog/group_1.265.985030477' scn min   1336662656 scn max   1336662659
system altered.

xxxxxxxx> @ viewsess redo%size
name      statistic#          value          sid
--------- ---------- -------------- ------------
redo size        293          48340          258
....

--//日志大小48340-648 = 47692,47k上下.
--//检查跟踪文件你可以发现:
redo record - thread:1 rba: 0x0008b5.0000005f.0040 len: 0x01d0 vld: 0x01 con_uid: 1
scn: 0x000000004fabda80 subscn:1176 11/16/2018 10:44:53
change #1 con_id:1 typ:0 cls:18 afn:4 dba:0x01000087 obj:4294967295 scn:0x000000004fabda80 seq:27 op:5.1 enc:0 rbl:0 flg:0x0000
ktudb redo: siz: 188 spc: 3282 flg: 0x0022 seq: 0x0b4d rec: 0x1b
            xid:  0x0001.002.000020c9
ktubu redo: slt: 2 rci: 26 opc: 11.1 objn: 104890 objd: 104890 tsn: 0
undo type:  regular undo       undo type:  last buffer split:  no
tablespace undo:  no
             0x00000000
kdo undo record:
ktb redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: c  uba: 0x01000087.0b4d.1a
kdo op code: urp row dependencies disabled
  xtype: xa flags: 0x00000000  bdba: 0x0041af3d  hdba: 0x0041af38
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
col  2: [100]
 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
change #2 con_id:1 typ:0 cls:1 afn:1 dba:0x0041af3d obj:104890 scn:0x000000004fabda80 seq:8 op:11.5 enc:0 rbl:0 flg:0x0000
ktb redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: c  uba: 0x01000087.0b4d.1b
kdo op code: urp row dependencies disabled
  xtype: xa flags: 0x00000000  bdba: 0x0041af3d  hdba: 0x0041af38
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
col  2: [100]
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78

--//没有字符z相关内容(7a 7a).

5.总结:
--//你可以发现oracle 18c在dml下修改字段前后如果信息不变,日志根本没有记录,这样一定程度减少日志大小.

如对本文有疑问， 点击进行留言回复！！