[20251205]验证11g dml日志优化是否存在.txt

[20251205]验证11g dml日志优化是否存在.txt --//鉴于许多生产系统还在运行11g环境，验证11g dml日志优化是否存在。 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production --//关闭最小附加日志。 SYS@book> ALTER DATABASE drop SUPPLEMENTAL LOG DATA; Database altered. SYS@book> @ col_vlist v$database dbid|supp 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 FROM v$database SYS@book> /       DBID SUPPLEME SUP SUP SUP SUP SUP ---------- -------- --- --- --- --- --- 1625575470 NO       NO  NO  NO  NO  NO 2.测试： --//drop table t purge ; SCOTT@book> create table t as select rownum id ,'1234567890' v1,'abcdefghij' v2 from dual ; Table created. --//分析表略。 3.测试: --//前后修改相同的情况。 --//session 1: SCOTT@book> @ spid ============================== SID                           : 133 SERIAL#                       : 15 PROCESS                       : 3689 SERVER                        : DEDICATED SPID                          : 3690 PID                           : 18 P_SERIAL#                     : 6 KILL_COMMAND                  : alter system kill session '133,15' immediate; PL/SQL procedure successfully completed. --//session 2: SYS@book> @ sesz 133 "^redo size$"        SID NAME                              VALUE ---------- ------------------------------ --------        133 redo size                             0 --//session 1: SCOTT@book> update t set v1=v1,v2=v2 where id=1; 1 row updated. SCOTT@book> @ xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ 2.32.1171 C70                                                                        XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS      USED_UBLK  USED_UREC XID              ADDR             START_DATE             0xFLAG ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ------------------- --------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU2_2996391332$' XID 2 32 1171;              2         32       1171          3       1310        323         50 ACTIVE              1          1 0200200093040000 000000008D198808 2025-12-05 15:47:51       E03 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1310; SCOTT@book> commit; Commit complete. --//session 2: SYS@book> @ sesz 133 "^redo size$"        SID NAME                              VALUE ---------- ------------------------------ --------        133 redo size                           580 --//注：如果在没有提交的情况下查看发现redo size依旧是0，因为使用IMU的缘故。 --//session 2: SYS@book> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU2_2996391332$' XID 2 32 1171; System altered. SYS@book> @ t TRACEFILE ----------------------------------------------------------------- /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3377.trc --//查看跟踪文件： ******************************************************************************** Undo Segment:  _SYSSMU2_2996391332$ (2) xid: 0x0002.020.00000493 Low Blk   :   (0, 0) High Blk  :   (16, 7) Object Id :   ALL Layer     :   ALL Opcode    :   ALL Level     :   2 ******************************************************************************** UNDO BLK:  Extent: 14   Block: 30   dba (file#, block#): 3,0x0000051e xid: 0x0002.020.00000493  seq: 0x143 cnt: 0x32  irb: 0x32  icl: 0x0   flg: 0x0000  Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset --------------------------------------------------------------------------- 0x01 0x1f90     0x02 0x1f0c     0x03 0x1eac     0x04 0x1e54     0x05 0x1dd0 0x06 0x1d70     0x07 0x1d18     0x08 0x1c94     0x09 0x1c34     0x0a 0x1bdc 0x0b 0x1b1c     0x0c 0x1a94     0x0d 0x1a30     0x0e 0x19c0     0x0f 0x1954 0x10 0x18e4     0x11 0x1890     0x12 0x184c     0x13 0x17ec     0x14 0x1784 0x15 0x1724     0x16 0x16e0     0x17 0x1690     0x18 0x1638     0x19 0x15e8 0x1a 0x15a4     0x1b 0x1554     0x1c 0x14fc     0x1d 0x14ac     0x1e 0x13ec 0x1f 0x133c     0x20 0x12d0     0x21 0x1264     0x22 0x11f8     0x23 0x118c 0x24 0x1110     0x25 0x1094     0x26 0x1028     0x27 0x0f78     0x28 0x0efc 0x29 0x0e90     0x2a 0x0de0     0x2b 0x0d74     0x2c 0x0cc4     0x2d 0x0c58 0x2e 0x0ba8     0x2f 0x0b3c     0x30 0x0ad0     0x31 0x0a64     0x32 0x09ac *----------------------------- * Rec #0x32  slt: 0x20  objn: 89072(0x00015bf0)  objd: 89072  tblspc: 4(0x00000004) *       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: 0x00c0051e.0143.2e ctl max scn: 0x0000.00145e62 prv tx scn: 0x0000.00145e78 txn start scn: scn: 0x0000.00000000 logon user: 83  prev brb: 12584218 prev bcl: 0 KDO undo record: KTB Redo op: 0x03  ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z Array Update of 1 rows: tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12 ncol: 3 nnew: 2 size: 0 KDO Op code:  21 row dependencies Disabled   xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0100020b  hdba: 0x0100020a itli: 2  ispac: 0  maxfr: 4858 vect = 3 col  1: [10]  31 32 33 34 35 36 37 38 39 30 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ col  2: [10]  61 62 63 64 65 66 67 68 69 6a ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//注意看下划线内容： --//31 32 33 34 35 36 37 38 39 30 = 1234567890 --//61 62 63 64 65 66 67 68 69 6a = abcdefghij --//可以发现undo段记录的前映像包含字段v1,v2的信息即使前后没有变化，验证了11g不支持这种情况下dml优化。