Undo series Learning

Undo series learning: a simple introduction to transaction slots an oracle data block header has a transaction slot (ITL ). When multiple transaction slots modify data blocks at the same time, and pctfree (proportion of free space of data blocks) is less than 10%, there will be ITL contention. This phenomenon may easily occur on update and delete. Because, during insert, oracle inserts other idle blocks separately. For example, check the number of transaction slots in Table a: [SQL] sys @ ORCL> select ini_trans, max_trans from dba_tables 2 where owner = 'hr' and table_name = 'a '; INI_TRANS MAX_TRANS ---------- 1 255 is 1 by default. a maximum of 255 www.2cto.com data blocks can be found in Table: [SQL] hr @ ORCL> select inline (rowid), 2 dbms_rowid.rowid_block_number (rowid), 3 id 4 from a; inline (ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) ID --- --------------------------------- ------------------------------------ ---------- 4 460 1 4 460 2 460 3 4 460 1: Table a has 460th data blocks on file 4. You can dump the No. 4 file with id = 2 and data block 460 to check the ITL header: www.2cto.com [SQL] alter system dump datafile 4 block 460 sys @ ORCL> select spid from v $ process where addr in (select paddr from v $ session 2 where sid in (select sid from v $ mystat where rownum = 1 )); part of SPID ------------ 10696 ITL is extracted as follows: [SQL] Block header dump: 0x010001cc Object id on Block? Y seg/obj: 0xce9d csc: 0x00. 15a47b itc: 2 flg: E typ: 1-DATA brn: 0 bdba: 0x10001c9 ver: 0x01 opc: 0 inc: 0 exflg: 0 www.2cto.com Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0003. 025.000001fa 0x01c00566. 0293.1e ---- 2 fsc 0x0000.00000000 0x02 0x0004. 020.00000151 0x0080068f. 0144.11 C --- 0 scn 0x0000. 000f71fa Note: Itl: Transaction slot No. Xid: Point to transaction table Uba: point to specific rollback block Flag: whether submitted Lck: Locked Flag Scn/Fsc: the commit time point assumes that there are two things at this time, T1 and T2: when a transaction is modified Oracle only updates the transaction information of the undo segment header, but does not update the information of the data block header or perform a small number of updates. It can be seen that the transaction table with the most reliable transaction information belongs to the undo segment header, and the transaction information in it reflects the transaction state most realistically. This is why sometimes select also produces redo. The data row is locked by T1. When T2 wants to modify the data row and finds a lock mark, locate T1 in ITL and view the Flag. There are two cases: 1) submitted: then T2 will remove the row head lock of the Data row (usually the lock will not be cleared in time), which will generate redo and then access the data row. Www.2cto.com 2) not submitted: If not submitted, T2 will doubt, isn't it true? Because he does not believe T1, at this time, he adheres to the good attitude of "never knowing this thing to do", and finds the transaction table of the segment header of the undo segment through the xid of T1, check the actual situation of the event. There are two cases: 2.1) submitted: So the child T2 is dead. After returning, clear the transaction information of T1 and, the row header lock is also removed, which generates a redo. 2.2) not submitted: so T2 determines that this row is indeed on top of the header... no action. After you get back, find the rollback block through the xid of T1, and reconstruct the remaining uncommitted rows and the rows in the rollback block, then read the CR block directly. Assume that T2 is looking for Segment 8, row 15th, and the transaction corresponding to the block that is overwritten 13th times has been committed, if T2 finds a 13th-time covered transaction of Segment 8 and line 15th, oracle will decisively consider that the transaction has been committed because the transaction that has not been committed is active, oracle will not be overwritten. Author: linwaterbin