Redo內部解析-Multi Rows Insert (八)
十一、多行插入
1、建立實驗環境
SQL>create table test1 (id number not null, object_name varchar2(20));
SQL>col scn format 999999999990
SQL>select dbms_flashback.get_system_change_number scn from dual;
SQL>insert into test1 select rownum, object_name from dba_objects where rownum<4;
SQL>commit;
SQL>select dbms_flashback.get_system_change_number scn from dual;
SQL>alter system dump logfile
'/home/xxxxx/oracle/oradata/xxxxx/redo03.log'
scn min 56403000234
scn max 56403000242;
2、trace 檔案解析
CHANGE #10 TYP:0 CLS: 1 AFN:4 DBA:0x01000044 OBJ:142805 SCN:0x000d.21e17bb1 SEQ: 1 OP:11.11
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0004.026.000046d9 uba: 0x00800ef3.4245.0d
KDO Op code: QMI row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000044 hdba: 0x01000043
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 lock: 1 nrow: 3
slot[0]: 0
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] 43 4f 4e 24
slot[1]: 1
tl: 13 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 6] 49 5f 43 4f 4c 32
slot[2]: 2
tl: 14 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 7] 49 5f 55 53 45 52 23
--"OP: 11.11"和"KDO Op code: QMI" 表示插入多行資料
--插入三行資料加到DBA=01000044, slot# 0 1 2,
--nrow:number rows=3
--該操作undo的位置uba= 0x00800ef3.4245.0d(undo block address.sequence number.record number)
--交易XID=0x0004.026.000046d9(USN#.SLOT#.WRAP#)
CHANGE #11 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000d.21e17b18 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0026 sqn: 0x000046d9 flg: 0x0012 siz: 116 fbi: 0
uba: 0x00800ef3.4245.0d pxid: 0x0000.000.00000000
--修改undo segment header資訊,具體對應undo資訊slot, sequence
CHANGE #12 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000d.21e17bb1 SEQ: 1 OP:5.4
ktucm redo: slt: 0x0026 sqn: 0x000046d9 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00800ef3.4245.0d ext: 2 spc: 6712 fbi: 0
--commit;
CHANGE #13 TYP:0 CLS:24 AFN:2 DBA:0x00800ef3 OBJ:4294967295 SCN:0x000d.21e17b17 SEQ: 3 OP:5.1
ktudb redo: siz: 116 spc: 6830 flg: 0x0012 seq: 0x4245 rec: 0x0d
xid: 0x0004.026.000046d9
ktubl redo: slt: 38 rci: 0 opc: 11.1 objn: 142805 objd: 142805 tsn: 4
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00800ee5.4245.26
prev ctl max cmt scn: 0x000d.21e1685f prev tx cmt scn: 0x000d.21e16879
txn start scn: 0x000d.21e17ba7 logon user: 55 prev brb: 8392427 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: QMD row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000044 hdba: 0x01000043
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 lock: 0 nrow: 3
slot[0]: 0
slot[1]: 1
slot[2]: 2
--修改undo資料塊具體資訊
--“KDO Op code: QMD” 多行刪除,刪除的block位置在bdba=0x01000044, slot 0 1 2
總結:
多行的插入所產生的redo log大小與插入一行所產生的redo log大小基本相同
1、建立實驗環境
SQL>create table test1 (id number not null, object_name varchar2(20));
SQL>col scn format 999999999990
SQL>select dbms_flashback.get_system_change_number scn from dual;
SQL>insert into test1 select rownum, object_name from dba_objects where rownum<4;
SQL>commit;
SQL>select dbms_flashback.get_system_change_number scn from dual;
SQL>alter system dump logfile
'/home/xxxxx/oracle/oradata/xxxxx/redo03.log'
scn min 56403000234
scn max 56403000242;
2、trace 檔案解析
CHANGE #10 TYP:0 CLS: 1 AFN:4 DBA:0x01000044 OBJ:142805 SCN:0x000d.21e17bb1 SEQ: 1 OP:11.11
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0004.026.000046d9 uba: 0x00800ef3.4245.0d
KDO Op code: QMI row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000044 hdba: 0x01000043
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 lock: 1 nrow: 3
slot[0]: 0
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] 43 4f 4e 24
slot[1]: 1
tl: 13 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 6] 49 5f 43 4f 4c 32
slot[2]: 2
tl: 14 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 7] 49 5f 55 53 45 52 23
--"OP: 11.11"和"KDO Op code: QMI" 表示插入多行資料
--插入三行資料加到DBA=01000044, slot# 0 1 2,
--nrow:number rows=3
--該操作undo的位置uba= 0x00800ef3.4245.0d(undo block address.sequence number.record number)
--交易XID=0x0004.026.000046d9(USN#.SLOT#.WRAP#)
CHANGE #11 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000d.21e17b18 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0026 sqn: 0x000046d9 flg: 0x0012 siz: 116 fbi: 0
uba: 0x00800ef3.4245.0d pxid: 0x0000.000.00000000
--修改undo segment header資訊,具體對應undo資訊slot, sequence
CHANGE #12 TYP:0 CLS:23 AFN:2 DBA:0x00800039 OBJ:4294967295 SCN:0x000d.21e17bb1 SEQ: 1 OP:5.4
ktucm redo: slt: 0x0026 sqn: 0x000046d9 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00800ef3.4245.0d ext: 2 spc: 6712 fbi: 0
--commit;
CHANGE #13 TYP:0 CLS:24 AFN:2 DBA:0x00800ef3 OBJ:4294967295 SCN:0x000d.21e17b17 SEQ: 3 OP:5.1
ktudb redo: siz: 116 spc: 6830 flg: 0x0012 seq: 0x4245 rec: 0x0d
xid: 0x0004.026.000046d9
ktubl redo: slt: 38 rci: 0 opc: 11.1 objn: 142805 objd: 142805 tsn: 4
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00800ee5.4245.26
prev ctl max cmt scn: 0x000d.21e1685f prev tx cmt scn: 0x000d.21e16879
txn start scn: 0x000d.21e17ba7 logon user: 55 prev brb: 8392427 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: QMD row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000044 hdba: 0x01000043
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 lock: 0 nrow: 3
slot[0]: 0
slot[1]: 1
slot[2]: 2
--修改undo資料塊具體資訊
--“KDO Op code: QMD” 多行刪除,刪除的block位置在bdba=0x01000044, slot 0 1 2
總結:
多行的插入所產生的redo log大小與插入一行所產生的redo log大小基本相同
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/354732/viewspace-609919/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Redo內部解析-Single Row insert (四)
- Redo內部解析-Global Temporary table insert(九)
- Redo內部解析-Insert Single Row with Index(七)Index
- Redo內部解析(三)
- Redo內部解析(二)
- Redo內部解析(一)
- Redo內部解析-Single Row Delete (六)delete
- Redo內部解析-Single Row update (五)
- 【REDO】Oracle redo內部結構Oracle Redo
- Webdis內部解析Web
- MySQL insert的內部操作流程介紹MySql
- 【SQL】 Multi table insert 多表插入操作SQL
- 八問八答搞懂Transformer內部運作原理ORM
- Multi-table insert using pipelined functionFunction
- 深入解析 oracle drop table內部原理Oracle
- ORACLE insert命令解析Oracle
- java內部類解析——菜鳥摘記Java
- 資料塊內部結構dump解析
- Redo 和 Undo 概念解析
- Redo active狀態解析
- Maximum insert commit size and Rows Per Batch in OLE DB Destination in SSISMITBAT
- 隨筆MySQL:Searching rows for update狀態解析MySql
- Oracle redo解析之-1、oracle redo log結構計算Oracle Redo
- java內部類,區域性內部類,靜態內部類,匿名內部類Java
- MySQL redo與undo日誌解析MySql
- java之內部類(InnerClass)----非靜態內部類、靜態內部類、區域性內部類、匿名內部類Java
- 10-Java內部類——成員內部類、區域性內部類、匿名內部類Java
- 雲伺服器 Linux內部無法解析域名伺服器Linux
- OkHttp3.0解析——談談內部的快取策略HTTP快取
- 通過WordCount解析Spark RDD內部原始碼機制Spark原始碼
- java內部類之成員內部類之匿名內部類Java
- on commit delete rows and on commit preserve rowsMITdelete
- 26_Oracle redo物理結構解析Oracle Redo
- [原始碼解析]Oozie來龍去脈之內部執行原始碼
- 深入解析React資料傳遞之元件內部通訊React元件
- 如何在函式計算內部中自定義DNS解析函式DNS
- C#泛型內部工作機制詳細解析C#泛型
- Sql server內部函式fn_PhysLocFormatter存在解析錯誤SQLServer函式ORM