[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.小结：
--//这类优化实际上对生产系统意义不大，大部分环境都要求打开附加日志。