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

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 @ orcl2, updatet_gyj set name = 'gyj1' where id = 1; 3. commit; 4. exit 1. check whether this problem is often complicated. Alibaba kids 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 is a collection of knowledge points related to 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 connection steps: (1) the client SQL Plus requests the connection and listens to accept the TCP connection from the client, and obtain 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 does Oracle do when this SQL statement is sent? Let's take a look at a figure (Library cache from DSI405). sharedpool is the most complicated in SGA, and library cache is the most complicated in shared pool. Here we will not discuss its mechanism in detail, briefly describe the SQL operation process. 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 no index is found on the ID column (1) query the data dictionary such as SEG $, find the T_GYJ table field header (2) read the Extent Map from the field header and start full scanning (3) find the first row that meets the condition, modify it (4), find the remaining row in the same block, construct a CR block, and continue searching 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), you do not need to construct the CR block (3) ID column, whether or not there is an index, in 11GR1, CR blocks must be constructed. (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), and then insert the new value (5) to modify any block, follow these steps (not IMU): (1) generate the post image of the UNDO field header transaction table 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 as a Redo Recorder to the transaction table where Log buffer (5) modifies the UNDO field header. The transaction starts. (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) generate a post-image of the DataBlock block in PGA (11.9) (2) post-image (5.2) of the UNDO segment header transaction table generated in PGA (3) Post-image of the UNDO block generated in PGA (5.1) (4) write the first three Redo vectors as a Redo Recorder to the 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 Commit): (1) generate the Redo information of commit in PGA (No. 5.4), and use it as a Redo recorder, write Log buffer (2) to 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 Commit): (1) generate the Commit Redo information (number 5.4) in PGA, pass in the Private strand in the Shared pool, append after 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. The last record is the dump information (for update t_gyj set name = 'gyj1' where id = 1 ;), if you are interested, you can go to dump to gain a deeper understanding of Oracle and make it your own architecture. 1. dumpalter system dump logfile of redolog '/u01/app/oracle/oradata/ocm/redo02.log'; redo record-Thread: 1 RBA: 0x0000d4. listen 0088.015c LEN: 0x00a4 VLD: 0x01SCN: 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. 074 7.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: 0x05SCN: 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: 0x018021 Ba.0724109f 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.000057 efktubl redo: slt: 24 rci: 0 opc: 11.1 [objn: 77365 objd: 77365 tsn: 7] Undo type: Regular undo Begintrans Last buffer split: NoTemp Object: NoTablespace Undo: no 0x00000000 prev Ctl uba: 0x018021ba. 0724.0 eprev ctl max cmt scn: 0x0000. 00f5dc35 prev tx cmt scn: 0x0000. 00f5dc3ftxn start scn: 0xffff. ffffffff logon user: 91 prev brb: 25174454 prev bcl: 0 BuExt idx: 0 flg2: 0KDO undo record: KTB Redoop: 0x03 ver: 0x01compat bit: 4 (post-11) padding: 1op: ZKDO Op code: URP row dependencies DisabledCHANGE #2 media recovery markerscn: 0x0000.00000000 SEQ: 0 OP: 24.4 ENC: 0 REDO RECORD -Thread: 1 RBA: 0x0000d4. 00000089.0010 LEN: 0x0214 VLD: 0x05SCN: 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: 0x021ba. 0724109f pxid: 0x0000. 000.00000000 CHANGE #2 TYP: 0 CLS: 18 AFN: 6 DBA: 0x018021baOBJ: 4294967295 SCN: 0x0000. 00f5 E113 SEQ: 1 OP: 5.1 ENC: 0 RBL: 0 ktudb redo: siz: 164 spc: 6094 flg: 0x0012seq: 0x0724 rec: 0x0f xid: 0x0001. 018.000057 efktubl redo: slt: 24 rci: 0 opc: 11.1 [objn: 77365 objd: 77365 tsn: 7] Undo type: Regular undo Begintrans Last buffer split: NoTemp Object: NoTablespace Undo: no 0x00000000 prev ctl uba: 0x018021ba. 0724.0 eprev ctl max cmt scn: 0x0000. 00f5dc35 prev tx cmt scn: 0x0000. 00f5dc3ftxn start SC N: 0xffff. ffffffff logon user: 91 prev brb: 25174454 prev bcl: 0 BuExt idx: 0 flg2: 0KDO undo record: KTB Redoop: 0x03 ver: 0x01compat bit: 4 (post-11) padding: 1op: ZKDO Op code: URP row dependencies Disabledxtype: XA flags: 0x00000000 bdba: 0x00c0027d hdba: 0x00c0027aitli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: 2col 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: 0KTB Redoop: 0x11 ver: 0x01compat bit: 4 (post-11) padding: 1op: F xid: 0x0001. 018.10957ef uba: 0x018021ba. 0724.0 fBlock cleanout record, scn: 0x0000. 00f5e123 ver: 0x01 opt: 0x02, entriesfollow... itli: 1 flg: 2 scn: 0x0000. 00f5e01_do Op code: URP row dependencies Disabledxtype: XA flags: 0x00000000 bdba: 0x00 C0027d hdba: 0x00c0027aitli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 2 ckix: 0 ncol: 2 nnew: 1 size: -2col 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 sessionid 1362603 Client Id = 2. undo dump (1) corresponds to the OP = 5.2 operation alter system dump undo header "_ SYSSMU1_1918248848 $ "; Index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt defaults 0x18 10 0x80 0x57ef 0x0010 0x0000. 00f5e123 0x018021ba 0x0000. 000.00000000 0x00000001 0x00000000 0 (2) corresponds to the OP = 5.1 operation, that is, the 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: 0flg2: 0 * ----------------------------- uba: 0x018021ba. 0724e e ctl max scn: 0x0000. 00f5dc35 prv tx scn: 0x0000. 00f5dc3ftxn startscn: scn: 0x0000. 00f5e123 logon user: 91 prev brb: 25174454 prev bcl: 0KDO undorecord: KTB Redo op: 0x03 ver: 0x01 compatbit: 4 (Post-11) padding: 1op: ZKDO Opcode: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00c0027d hdba: 0x00c0027aitli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: 2col 1: [6] 41 41 41 41 41 3. data dump corresponds to the OP = 11.9 operation alter system dump datafile 3 block 637 Object idon Block? Yseg/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/Fsc0x01 0x0003. 00d. 00007825 0x01802b0c. 0a53. 2d C --- 0 scn 0x0000. 00f5e0cc0x02 0x0001. 018.10957ef 0x018021ba. 0724109f ---- 1 fsc 0x0002. 00000000 bdba: 0x00c0027ddata_block_dump, dataheader at 0xa5d664 ================== tsiz: 0x1f98hsiz: 0x14pbl: 0x00a5d664 76543210a G = -------- ntab = 1 nrow = 1 frre =-1 fsbo = 0x14fseo = 0x1f80avsp = 0x1f77tosp = 0x1f790xe: pti [0] nrow = 1 offs = 00x12: pri [0] offs = 0x1f80block_row_dump: tab 0, row 0, @ 0x1f80tl: 11fb: -- H-FL -- lb: 0x0 cc: 2col 0: [2] c1 02col 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: R Ollout 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 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-KTURLGULayer 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 th E 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 in 11 GB 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.