[20251202]21c dml日志优化3.txt

[20251202]21c dml日志优化3.txt --//很早以前我就知道，版本18c开始或许12c开始就支持这种功能，如果update修改前后内容没有改变，redo日志记录的信息很少。 --//当然前提条件不能打开附加日志，也许dg环境也不行(注：我没有测试) --//我大概翻看当时做的测试笔记，结合最近遇到的问题，在21c下重复验证看看。 1.环境: SCOTT@book01p> @ ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. --//关闭附加日志： SYS@book> ALTER DATABASE drop SUPPLEMENTAL LOG DATA; Database altered. SYS@book> @ col_vlist v$database supp|^dbid SELECT dbid ,supplemental_log_data_min ,supplemental_log_data_pk ,supplemental_log_data_ui ,supplemental_log_data_fk ,supplemental_log_data_all ,supplemental_log_data_pl ,supplemental_log_data_sr FROM v$database SYS@book> /       DBID SUPPLEME SUP SUP SUP SUP SUP SUP ---------- -------- --- --- --- --- --- --- 1617337831 NO       NO  NO  NO  NO  NO  NO 2.建立测试脚本: --//drop table t purge ; SCOTT@book01p> create table t as select rownum id ,'1234567890' v1,'abcdefghij' v2 from dual ; Table created. --//分析表略。 3.测试： SCOTT@book01p> update t set v1=upper(v1),v2=upper(v2) where id=1; 1 row updated. --//修改成大写，对于实际情况v1全部是数字前后没有改变，而v2字段做了修改。 SCOTT@book01p> @ xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ 1.22.11458 C70                                                                        XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS      USED_UBLK  USED_UREC XID              ADDR             START_DATE             0xFLAG ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ------------------- --------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU1_274027410$' XID 1 22 11458;              1         22      11458         21      40077       1268         10 ACTIVE              1          1 01001600C22C0000 000000007BDC0150 2025-12-02 16:01:14       E03 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU1_274027410$'; ALTER SYSTEM DUMP DATAFILE 21 BLOCK 40077; --//看看转储undo块信息。注意在pdb下操作，不然访问对应的undo段，报如下错误： --//ORA-01534: rollback segment '_SYSSMU1_274027410$' doesn't exist SYS@book01p> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU1_274027410$' XID 1 22 11458; System altered. --//查看跟踪文件内容： ******************************************************************************** Undo Segment:  _SYSSMU1_274027410$ (1)  xid: 0x0001.016.00002cc2 Low Blk   :   (0, 0) High Blk  :   (22, 1023) Object Id :   ALL Layer     :   ALL Opcode    :   ALL Level     :   2 ******************************************************************************** UNDO BLK:  Extent: 21   Block: 397   dba (file#, block#): 21,0x00009c8d  xid: 0x0001.016.00002cc2  seq: 0x4f4 cnt: 0xa   irb: 0xa   icl: 0x0   flg: 0x0000  Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset --------------------------------------------------------------------------- 0x01 0x1f80     0x02 0x1f2c     0x03 0x1ec4     0x04 0x1e48     0x05 0x1df4 0x06 0x1da0     0x07 0x1d38     0x08 0x1ce4     0x09 0x1c94     0x0a 0x1c04 *----------------------------- * Rec #0xa  slt: 0x16  objn: 172504(0x0002a1d8)  objd: 172504  tblspc: 5(0x00000005) *       Layer:  11 (Row)   opc: 1   rci 0x00 Undo type:  Regular undo    Begin trans    Last buffer split:  No Temp Object:  No Tablespace Undo:  No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x05409c8d.04f4.09 ctl max scn: 0x0000000003d49dd4 prv tx scn: 0x0000000003d49de4 txn start scn: scn: 0x0000000000000000 logon user: 109  prev brb: 0 prev bcl: 0 KDO undo record: KTB Redo op: 0x03  ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z KDO Op code: URP row dependencies Disabled   xtype: XA flags: 0x00000000  bdba: 0x030000ab  hdba: 0x030000aa itli: 2  ispac: 0  maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 187 ncol: 3 nnew: 1 size: 0 col  2: [10]  61 62 63 64 65 66 67 68 69 6a ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//注意看下划线内容： --//61 62 63 64 65 66 67 68 69 6a = abcdefghij --//可以发现undo段记录的前映像仅仅包含字段v2的信息，而v1字段根本没有记录，oracle通过这样方式减少日志记录。自然redo日志里 --//面也不存在v1的修改记录。 --//转储undo数据块也可以同样验证这个问题。 SYS@book01p> alter system checkpoint; System altered. SYS@book01p> @ ti New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3630_0001.trc SYS@book01p> ALTER SYSTEM DUMP DATAFILE 21 BLOCK 40077; System altered. --//查看跟踪文件内容： ******************************************************************************** UNDO BLK:  xid: 0x0001.016.00002cc2  seq: 0x4f4 cnt: 0xa   irb: 0xa   icl: 0x0   flg: 0x0000  Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset --------------------------------------------------------------------------- 0x01 0x1f80     0x02 0x1f2c     0x03 0x1ec4     0x04 0x1e48     0x05 0x1df4 0x06 0x1da0     0x07 0x1d38     0x08 0x1ce4     0x09 0x1c94     0x0a 0x1c04 .... *----------------------------- * Rec #0xa  slt: 0x16  objn: 172504(0x0002a1d8)  objd: 172504  tblspc: 5(0x00000005) *       Layer:  11 (Row)   opc: 1   rci 0x00 Undo type:  Regular undo    Begin trans    Last buffer split:  No Temp Object:  No Tablespace Undo:  No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x05409c8d.04f4.09 ctl max scn: 0x0000000003d49dd4 prv tx scn: 0x0000000003d49de4 txn start scn: scn: 0x0000000000000000 logon user: 109  prev brb: 0 prev bcl: 0 KDO undo record: KTB Redo op: 0x03  ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z KDO Op code: URP row dependencies Disabled   xtype: XA flags: 0x00000000  bdba: 0x030000ab  hdba: 0x030000aa itli: 2  ispac: 0  maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 187 ncol: 3 nnew: 1 size: 0 col  2: [10]  61 62 63 64 65 66 67 68 69 6a ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ End dump data blocks tsn: 2 file#: 21 minblk 40077 maxblk 40077 SCOTT@book01p> commit ; Commit complete. 4.继续测试： --//如果打开最小附加日志，就看不见前面的现象： SYS@book> ALTER DATABASE add SUPPLEMENTAL LOG DATA; Database altered. --//注意如果有事务没有提交，执行会停在那里等待事务提交完成。 SYS@book> @ col_vlist v$database supp|^dbid SELECT dbid ,supplemental_log_data_min ,supplemental_log_data_pk ,supplemental_log_data_ui ,supplemental_log_data_fk ,supplemental_log_data_all ,supplemental_log_data_pl ,supplemental_log_data_sr FROM v$database SYS@book> /       DBID SUPPLEME SUP SUP SUP SUP SUP SUP ---------- -------- --- --- --- --- --- --- 1617337831 YES      NO  NO  NO  NO  NO  NO SCOTT@book01p> select * from t;         ID V1         V2 ---------- ---------- ----------          1 1234567890 ABCDEFGHIJ --//当前记录v2大写。 SCOTT@book01p> update t set v1=lower(v1),v2=lower(v2) where id=1; 1 row updated. --//修改为小写。 SCOTT@book01p> @xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ 3.24.11584 C70                                                                        XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS      USED_UBLK  USED_UREC XID              ADDR             START_DATE             0xFLAG ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ------------------- --------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU3_69012697$' XID 3 24 11584;               3         24      11584         21      16275       1016          9 ACTIVE              1          1 03001800402D0000 000000007BDC0150 2025-12-02 16:19:09      1E03 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU3_69012697$'; ALTER SYSTEM DUMP DATAFILE 21 BLOCK 16275; SYS@book01p> alter system checkpoint; System altered. SYS@book01p> @ ti New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3630_0002.trc SYS@book01p> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU3_69012697$' XID 3 24 11584; System altered. --//查看跟踪文件内容： ******************************************************************************** Undo Segment:  _SYSSMU3_69012697$ (3)  xid: 0x0003.018.00002d40 Low Blk   :   (0, 0) High Blk  :   (4, 127) Object Id :   ALL Layer     :   ALL Opcode    :   ALL Level     :   2 ******************************************************************************** UNDO BLK:  Extent: 3   Block: 19   dba (file#, block#): 21,0x00003f93  xid: 0x0003.018.00002d40  seq: 0x3f8 cnt: 0x9   irb: 0x9   icl: 0x0   flg: 0x0000  Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset --------------------------------------------------------------------------- 0x01 0x1f3c     0x02 0x1ed4     0x03 0x1e6c     0x04 0x1db0     0x05 0x1d48 0x06 0x1c9c     0x07 0x1c34     0x08 0x1b7c     0x09 0x1ac8 *----------------------------- * Rec #0x9  slt: 0x18  objn: 172504(0x0002a1d8)  objd: 172504  tblspc: 5(0x00000005) *       Layer:  11 (Row)   opc: 1   rci 0x00 Undo type:  Regular undo    Begin trans    Last buffer split:  No Temp Object:  No Tablespace Undo:  No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x05403f93.03f8.08 ctl max scn: 0x0000000003d4c52d prv tx scn: 0x0000000003d4c53b txn start scn: scn: 0x0000000003d4c9e8 logon user: 109  prev brb: 88096658 prev bcl: 0 KDO undo record: KTB Redo op: 0x03  ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z KDO Op code: URP row dependencies Disabled   xtype: XA flags: 0x00000000  bdba: 0x030000ab  hdba: 0x030000aa itli: 3  ispac: 0  maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 3 nnew: 2 size: 0 col  1: [10]  31 32 33 34 35 36 37 38 39 30 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ col  2: [10]  41 42 43 44 45 46 47 48 49 4a ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//注意看下划线内容： 31 32 33 34 35 36 37 38 39 30 = 1234567890 41 42 43 44 45 46 47 48 49 4a = ABCDEFGHIJ --//可以发现undo段记录的前映像包含字段v1,v2的信息. 5.小结： --//这类优化实际上对生产系统意义不大，大部分环境都要求打开附加日志。