An in-depth analysis of the entire process of UPDATE from birth to death (after reading the oracle system, see this

An in-depth analysis of the entire UPDATE process from birth to death is a question that a brother of Oracle once asked me. Some people later asked me this question: use the Internal principles of ORACLE to describe the following processes: 1. sqlplusgyjgyj @ orcl2, updatet_gyjsetnamegyj1whereid1; 3. commit; 4. exit to view the problem

An in-depth analysis of the entire UPDATE process from birth to death is a question that a brother of Oracle once asked me. Some people later asked me this question: use ORACLE Internal principles to describe the following processes: 1. sqlplus gyj/gyj @ orcl 2. updatet_gyj set name = 'gyj1' where id = 1; 3. commit; 4. exit. Check the problem.

In-depth analysis of the entire UPDATE process from birth to death

This is a question that a brother of Oracle once asked me, and some people later asked me this question:
Use ORACLE's Internal principles to describe the following processes:
1. sqlplus gyj/gyj @ orcl
2. updatet_gyj set name = 'gyj1' where id = 1;
3. commit;
4. exit

Check whether this problem is often complicated. Alibaba's children's shoes especially like to interview people with questions like this, in this way, you can quickly determine the level of DBA (elementary/intermediate/advanced) you are. I will give you a glimpse of this issue. Due to limited capabilities, I can only make a simple explanation, you are welcome to join the discussion. Hey! This is a theoretical task. I hope it will be helpful for you to solve practical problems.

1. sqlplus gyj/gyj @ orcl
This section describes the knowledge of Oracle network connection. First, let's take a look at the figure describing the network connection between the Oracle client and the server, and provide the entire connection steps:

(1) The client SQL Plus requests the connection, listens to accept the TCP connection from the client, and obtains the TNS data packets sent from the client.

(2) The listening process opens the pipeline for communicating with the sub-process, and fork a sub-process, called "Listening to sub-process 1", and then the listening process keeps waiting, until the "listening sub-process 1" ends.

(3) Listen to sub-process 1 Fork out of sub-process 2.

(4) After completing the preceding step, child process 1 immediately exits and ends child process 1.

(5) sub-process 2 collects the host name, IP address, and process Number of the process, the sub-process 2 is renamed as the server process (here we also call the server process as the frontend process or server process), and the application occupies a small piece of PGA memory.

(6) The foreground process sends the host name, IP address, and process number to the listening process.

(7) The listening process receives information from the foreground process and returns the client information (such as the user password environment variable) to the foreground process.

(8) The foreground process queries data dictionaries such as USER $ and PROFILE $ to verify that the USER name and password are valid. If the USER password is incorrect, an error is reported, indicating that the USER name and password are invalid. Otherwise, the process interacts with the client.

(9) The client receives information about the foreground process and interacts with it. The entire connection is created.

2. update t_gyj set name = 'gyj1' where id = 1;

What Should Oracle do when this SQL statement is sent out? Let's first look at a figure (from Library cache of DSI405)

Sharedpool is the most complex in SGA, and library cache is the most complex shared pool. Here we will not discuss its mechanism in detail, but will briefly discuss the SQL operation process in it.

First, after each character of SQL (update t_gyjset name = 'gyj1' where id = 1) includes spaces converted into ASCII codes, take this heap of ASCII code and use the HASH function to generate a SQL _hash value. Oracle uses this SQL _hash value to describe HASH Buckets (see the figure above. This picture is not very good, only the hash buckets on The 0th is drawn. If the SQL _hash value is equal to 0, Oracle will extend the HASH Buckets on The 0th to search for the Object Handle chain, this Object Handle contains SQL text. If it is exactly the same as our update t_gyj set name = 'gyj1' where id = 1, it indicates that this SQL statement has been cached in the sharing pool. This process is soft parsing. Of course, I will not talk about it any more. It is complicated to talk about Parent and Child cursors, And the execution plan is stored in heap 6.

Well, if the text of this SQL statement is not found in the Object Handle chain in the above method, it means that the SQL statement is not in the shared pool, and hard Parsing is required at this time (the process is too large to do the syntax, semantics, permissions, query view expansion, small query blocks, SQL equivalent conversion, cost estimation, and finally generation of execution plans, if there is a lot of hard parsing, it will consume CPU and share pool.

In fact, there are: Soft parsing, no parsing .... Hey! I will not elaborate on it here. Let's talk about it first and let's look at it again.

(1) If the ID column has no index
(1) query the data dictionary such as SEG $ and find the T_GYJ table field header.
(2) read the Extent Map from the field header and start full scanning.
(3) locate the first row that meets the conditions and modify it.
(4) Search for the remaining rows in the same block. First, construct a CR block and continue the search in the CR block. If the row that meets the condition is found, modify it in the Xcur block.

(2) If the ID column has an index and the version is not 11GR1 (10G, 11GR2), no CR block needs to be constructed.

(3) No matter whether the ID column has an index or not, a CR block must be constructed in 11GR1.

(4) If the NAME column has an index, add the index maintenance steps:
(1) first Delete the original value to be modified in the original index Block
(2) Insert the new value

(5) perform the following steps to modify any block (not IMU)
(1) Post-image of the UNDO field header transaction table generated in PGA (5.2)
(2) Post-image of the UNDO block generated in PGA (5.1)
(3) Post-image of the DataBlock block generated in PGA (11.9)
(4) write the first three Redo vectors into the Log buffer as a Redo Recorder.
(5) modify the transaction table of the UNDO field header and start the transaction.
(6) modify the UNDO block and write it to the front image of DataBlock.
(7) Modify DataBlock and write the new value "gyj1" to the Buffer cache.

(6) perform the following steps to modify any block: (IMU)
(1) Post-image of the DataBlock block generated in PGA (11.9)
(2) Post-image of the UNDO field header transaction table generated in PGA (5.2)
(3) Post-image of the UNDO block generated in PGA (5.1)
(4) write the first three Redo vectors as a Redo Recorder to Private strand in the Shared pool.
(5) write the pre-image value in DataBlock to the Imu pool in the Shared pool.
(6) modify the transaction table of the UNDO field header.
(7) modify the UNDO block and write it to the front image of DataBlock.
(8) Modify DataBlock and write the new value "gyj1" to the Buffer cache.

3. commit;
(1) Non-IMU (by the most common fast submission ):
(1) generate the Commit Redo information (number 5.4) in PGA, and use it as a Redo recorder to write the Log buffer
(2) modify the corresponding Slot of the transaction table and declare that the transaction has been committed.
(3) Modify DataBlock and write the quick commit flag and SCN in the ITL Slot. The row locks on each row are unclear.
(4) Notify Lgwr to write the Log buffer into the Redo file.
(5) After receiving the Lgwr notification, the write is complete.
(6) Send the complete information to the user.

(1) IMU (by the most common fast submission ):
(1) generate the Commit Redo information (number 5.4) in PGA, pass in the Private strand in the Shared pool, and append the Redo recorder before the transaction.
(2) modify the corresponding Slot of the transaction table and declare that the transaction has been committed.
(3) Modify DataBlock and write the quick commit flag and SCN in the ITL Slot. The row locks on each row are unclear.
(4) Write Redo data in Private Strand to Log buffer.
(5) Notify Lgwr to write the Log buffer into the Redo file.
(6) After receiving the Lgwr notification, the write is complete.
(7) Send the complete information to the user.

4. exit
(1) disconnect, stop the server process, and release the PGA

Attached: dump information (for update t_gyj set name = 'gyj1' where id = 1;). If you are interested, go to dump to learn more about Oracle, make it your own architecture.

1. dump of redolog

Alter system dump logfile '/u01/app/oracle/oradata/ocm/redo02.log ';

Redo record-Thread: 1 RBA: 0x0000d4. 00000088.015c LEN: 0x00a4 VLD: 0x01
SCN: 0x0000. 00f5e121 SUBSCN: 1 04/12/2013 08:45:43
CHANGE #1 TYP: 0 CLS: 23 AFN: 6 DBA: 0x018000b0OBJ: 4294967295 SCN: 0x0000. 00f5e120 SEQ: 1 OP: 5.4 ENC: 0 RBL: 0
Ktucm redo: slt: 0x0013 sqn: 0x00005a4dsrt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x01800c05. 0747.34 ext: 6 spc: 578 fbi: 0
CHANGE #2 media recovery markerscn: 0x0000.00000000 SEQ: 0 OP: 24.4 ENC: 0

REDORECORD-Thread: 1 RBA: 0x0000d4. 00000089.0010 LEN: 0x0214 VLD: 0x05
SCN: 0x0000. 00f5e123 SUBSCN: 1 04/12/2013 08:45:48
CHANGE #1 TYP: 0 CLS: 17 AFN: 6 DBA: 0x01800080OBJ: 4294967295 SCN: 0x0000. 00f5e114 SEQ: 1 OP: 5.2 ENC: 0 RBL: 0
Ktudh redo: slt: 0x0018 sqn: 0x000057efflg: 0x0012 siz: 164 fbi: 0
Uba: 0x018021ba. 072420.f pxid: 0x0000. 000.00000000
CHANGE #2 TYP: 0 CLS: 18 AFN: 6 DBA: 0x018021baOBJ: 4294967295 SCN: 0x0000. 00f5e113 SEQ: 1 OP: 5.1 ENC: 0 RBL: 0
Ktudb redo: siz: 164 spc: 6094 flg: 0x0012seq: 0x0724 rec: 0x0f
Xid: 0x0001. 018.000057ef
Ktubl redo: slt: 24 rci: 0 opc: 11.1 [objn: 77365 objd: 77365 tsn: 7]
Undo type: Regular undo Begintrans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x018021ba. 0724.0e
Prev ctl max cmt scn: 0x0000. 00f5dc35 prev tx cmt scn: 0x0000. 00f5dc3f
Txn start scn: 0xffff. ffffffff logon user: 91 prev brb: 25174454 prev bcl: 0 BuExt idx: 0 flg2: 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
CHANGE #2 media recovery markerscn: 0x0000.00000000 SEQ: 0 OP: 24.4 ENC: 0

Redo record-Thread: 1 RBA: 0x0000d4. 00000089.0010 LEN: 0x0214 VLD: 0x05
SCN: 0x0000. 00f5e123 SUBSCN: 1 04/12/2013 08:45:48
CHANGE #1 TYP: 0 CLS: 17 AFN: 6 DBA: 0x01800080OBJ: 4294967295 SCN: 0x0000. 00f5e114 SEQ: 1 OP: 5.2 ENC: 0 RBL: 0
Ktudh redo: slt: 0x0018 sqn: 0x000057efflg: 0x0012 siz: 164 fbi: 0
Uba: 0x018021ba. 072420.f pxid: 0x0000. 000.00000000
CHANGE #2 TYP: 0 CLS: 18 AFN: 6 DBA: 0x018021baOBJ: 4294967295 SCN: 0x0000. 00f5e113 SEQ: 1 OP: 5.1 ENC: 0 RBL: 0
Ktudb redo: siz: 164 spc: 6094 flg: 0x0012seq: 0x0724 rec: 0x0f
Xid: 0x0001. 018.000057ef
Ktubl redo: slt: 24 rci: 0 opc: 11.1 [objn: 77365 objd: 77365 tsn: 7]
Undo type: Regular undo Begintrans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x018021ba. 0724.0e
Prev ctl max cmt scn: 0x0000. 00f5dc35 prev tx cmt scn: 0x0000. 00f5dc3f
Txn start scn: 0xffff. ffffffff logon user: 91 prev brb: 25174454 prev bcl: 0 BuExt idx: 0 flg2: 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: 0x00c0027d hdba: 0x00c0027a
Itli: 2 ispac: 0 maxfr: 4858
Tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 0 ckix: 0
Ncol: 2 nnew: 1 size: 2
Col 1: [6] 41 41 41 41 41
CHANGE #3 TYP: 2 CLS: 1 AFN: 3 DBA: 0x00c0027dOBJ: 77365 SCN: 0x0000. 00f5e0cc SEQ: 1 OP: 11.5 ENC: 0 RBL: 0
KTB Redo
Op: 0x11 ver: 0x01
Compat bit: 4 (post-11) padding: 1
Op: F xid: 0x0001. 018.10957ef uba: 0x018021ba. 0724366f
Block cleanout record, scn: 0x0000. 00f5e123 ver: 0x01 opt: 0x02, entriesfollow...
Itli: 1 flg: 2 scn: 0x0000. 00f5e0cc
KDO Op code: URP row dependencies Disabled
Xtype: XA flags: 0x00000000 bdba: 0x00c0027d hdba: 0x00c0027a
Itli: 2 ispac: 0 maxfr: 4858
Tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 2 ckix: 0
Ncol: 2 nnew: 1 size:-2
Col 1: [4] 67 79 6a 31
CHANGE #4 media recovery markerscn: 0x0000.00000000 SEQ: 0 OP: 5.20 ENC: 0
Session number = 162
Serial number = 7
Transaction name =
Version 186646784:
Audit Session ID 1362603
Client Id =

2. undo dump

(1) Operations corresponding to OP = 5.2

Alter system dump undo header "_ SYSSMU1_1918248848 $ ";

Index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt

Bytes ------------------------------------------------------------------------------------------------

0x18 10 0x80 0x57ef 0x0010 0x0000. 00f5e123 0x018021ba 0x0000. 000.00000000 0x00000001 0x00000000 0

(2) Operations corresponding to OP = 5.1, that is, undo block data

Alter systemdump datafile 6 block 8634;

* Rec # 0xf slt: 0x18 objn: 77365 (0x00012e35) objd: 77365 tblspc: 7 (0x00000007)

* Layer: 11 (Row) opc: 1 rci 0x00
Undotype: Regular undo Begin trans Last buffer split: No
TempObject: No
TablespaceUndo: No
Rdba: 0x00000000Ext idx: 0
Flg2: 0
*-----------------------------
Uba: 0x018021ba. 0724.0e ctl max scn: 0x0000. 00f5dc35 prv tx scn: 0x0000. 00f5dc3f
Txn startscn: scn: 0x0000. 00f5e123 logon user: 91
Prev brb: 25174454 prev bcl: 0
KDO undorecord:
KTB Redo
Op: 0x03 ver: 0x01
Compatbit: 4 (post-11) padding: 1
Op: Z
KDO Opcode: URP row dependencies Disabled
Xtype: XA flags: 0x00000000 bdba: 0x00c0027d hdba: 0x00c0027a
Itli: 2 ispac: 0 maxfr: 4858
Tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 0 ckix: 0
Ncol: 2 nnew: 1 size: 2
Col 1: [6] 41 41 41 41 41

3. data dump

Operations corresponding to OP = 11.9
Alter system dump datafile 3 block 637

Object idon Block? Y
Seg/obj: 0x12e35 csc: 0x00. f5e123 itc: 2 flg: E typ: 1-DATA
Brn: 0 bdba: 0xc00278 ver: 0x01 opc: 0
Inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003. 00d. 00007825 0x01802b0c. 0a53. 2d C --- 0 scn 0x0000. 00f5e0cc
0x02 0x0001. 018.000057ef 0x018021ba. 0724366f ---- 1 fsc 0x0002.00000000
Bdba: 0x00c0027d
Data_block_dump, dataheader at 0xa5d664
====================
Tsiz: 0x1f98
Hsiz: 0x14
Pbl: 0x00a5d664
76543210
Flag = --------
Ntab = 1
Nrow = 1
Frre =-1
Fsbo = 0x14
Fseo = 0x1f80
Avsp = 0x1f77
Tosp = 0x1f79
0xe: pti [0] nrow = 1 offs = 0
0x12: pri [0] offs = 0x1f80
Block_row_dump:
Tab 0, row 0, @ 0x1f80
Tl: 11fb: -- H-FL -- lb: 0x2 cc: 2
Col 0: [2] c1 02
Col 1: [4] 67 79 6a 31
　　

Appendix OPCODE:
Layer 5: Transaction Undo-KCOCOTUN [ktucts. h]
Opcode 1: Undo block or undo segment header-KTURDB
Opcode 2: Update rollback segment header-KTURDH
Opcode 3: rolout a transaction begin
Opcode 4: Commit transaction (transaction table update)
-No undo record
Opcode 5: Create rollback segment (format)-no undo record
Opcode 6: Rollback record index in an undo block-KTUIRB
Opcode 7: Begin transaction (transaction table update)
Opcode 8: Mark transaction as dead
Opcode 9: Undo routine to rollback theextend of a rollback segment
Opcode 10: Redo to perform the rollback of extend of rollback segment
To the segment header.
Opcode 11: Rollback DBA in transaction table entry-KTUBRB
Opcode 12: Change transaction state (in transaction table entry)
Opcode 13: Convert rollback segment format (V6-> V7)
Opcode 14: Change extent allocation parameters in a rollback segment
Opcode 15:
Opcode 16:
Opcode 17:
Opcode 18:
Opcode 19: Transaction start audit log record
Opcode 20: Transaction continue audit log record
Opcode 24: Kernel Transaction Undo Relog CHanGe-KTURLGU
Layer 11: Row Access-KCOCODRW [kdocts. h]
Opcode 1: Interpret Undo Record (Undo)
Opcode 2: Insert Row Piece
Opcode 3: Drop Row Piece
Opcode 4: Lock Row Piece
Opcode 5: Update Row Piece
Opcode 6: Overwrite Row Piece
Opcode 7: Manipulate First Column (add or delete the 1rst column)
Opcode 8: Change Forwarding address
Opcode 9: Change the Cluster Key Index
Opcode 10: Set Key Links (change the forward & backward key links
On a cluster key)
Opcode 11: Quick Multi-Insert (ex: insert as select ...)
Opcode 12: Quick Multi-Delete
Opcode 13: Toggle Block Header flags

Note: 11.19 is available only in 11g, and is the update operation code of 11.5.

The charm of Oracle may be that a lot of things can be presented in any small concept. There are not locks, Latch, Mutex, and so on. There are too many things to be integrated, after completing these steps, you can write a book that involves all aspects of Oracle.