[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下修改字段前后如果信息不变,日志根本没有记录,这样一定程度减少日志大小.