In-depth analysis of a complete transaction

oracle029 in-depth analysis of a complete transaction
2. Diagram the operation flow of a transaction
Undo Segment Composition: Segment header, rollback block
Transaction id:oracle the transaction ID assigned to each transaction

Select Xid,xidusn,xidslot,xidsqn,ubablk,ubafil from v$transaction;// lists the active transactions in the system. NULL NULL NULL NULL NULL INSERT into t values (1, ' Zhangsan '); Select Xid,xidusn,xidslot,xidsqn,ubablk,ubafil from V$transaction; 070017008f020000 7 23 655 1037 3//currently active transaction Commitselect Xid,xidusn,xidslot,xidsqn,ubablk,ubafil from V$transaction; NULL NULL NULL NULL NULL//indicates that the transaction has been committed

Transaction table
The first chunk of the undo segment, with a maximum of 47 transactions per rollback segment

Segment Header block for rollback segments SELECT * FROM V$rollname;0 SYSTEM 1 _syssmu1_3780397527$ 2 _syssmu2_2232571081$ 3 _syssmu3_2097677531$ 4 _syssmu4_1152005954$ 5 _syssmu5_1527469038$ 6 _syssmu6_2443381498$ 7 _syssmu7_3286610060$ 8 _syssmu8_2012382730$ 9 _syssmu9_1424341975$ Ten _syssmu10_3550978943$ Select Header_block,header_file from dba_segments where segment_name= ' _syssmu1_3780397527$ '; 128 3 Alter system dump Undo header ' _syssmu1_3780397527$ ';//dump rollback Segment Header View the process number of the current session Select spid from v$process where addr in (select Paddr from V$session where sid= (select Sid from V$mystat where Rownum=1)); Spid//server Process Id5179alter system dump Undo Header ' _syssmu1_37803975 27$ ';//dump ROLLBACK segment alter system dump DATAFILE 5 block 4308;//dump rollback segment Data block

1. The beginning of a transaction writes the transaction information XID in the transaction table of the header block of the undo, and also writes the transaction information XID in the transaction slot of the block of data that will be modified.
Transaction slots: XID (the transaction slot of the block that needs to be modified), UBA (the address information that modifies the data block that the original data exists in undo)
Deep analysis of transaction slots and transaction submission 7, transaction slot ITL
Default is 1
Maximum 255 (cannot be changed from oracle10g)

DBA_TABLES describes all relational tables in the database. USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column. Sql> Select Ini_trans,max_trans from dba_tables where table_name= ' T '; Ini_trans Max_trans ---------- ---------- 1 255

Transaction slot Contention
CREATE TABLE T (ID number (5), name Char (2000));
INSERT into t values (1, ' AA ');
INSERT into t values (2, ' BB ');
INSERT into t values (3, ' BB ');
INSERT into t values (4, ' cc ');
INSERT into t values (5, ' DD ');
Commit
Select Dbms_rowid.rowid_relative_fno (ROWID), Dbms_rowid.rowid_block_number (ROWID), id from T;dbms_rowid. ROWID_RELATIVE_FNO (ROWID), Dbms_rowid. Rowid_block_number (ROWID), ID1 86193 1//file number and block number for a record
1 86193 1
1 86193 2
1 86193 3
1 86193 4
1 86193 5
Update T10 set name= ' ABCD ' where id=1;
Select UBAFIL,UBABLK,XIDUSN,XIDSLOT,XIDSQN,START_SCNB from V$transaction;

Ubafil, Ubablk, Xidusn, Xidslot, XIDSQN, START_SCNB 3 11254 9 868 1234672 Uba the file number, block number, the segment header block of the rollback block, which row of the table is recorded, the record is overwritten several times (the information exists in the transaction table as well as in the transaction operation)

SELECT * from V$rollname;//Query Segment header block

0 SYSTEM 1 _syssmu1_3780397527$ 2 _syssmu2_2232571081$ 3 _syssmu3_2097677531$ 4 _syssmu4_1152005954$ 5 _syssmu5_1527469038$ 6 _syssmu6_2443381498$ 7 _syssmu7_3286610060$ 8 _syssmu8_2012382730$ 9 _syssmu9_1424341975$ Ten _syssmu10_3550978943$

Sql> select spid from v$process where addr in (select Paddr from V$session where sid= (select Sid from V$mystat where Rownum=1)); 2 SPID ------------------------ 11175//through server Pro to find the corresponding file in Udump

Alter system dump Undo header ' _syssmu8_2012382730$ ';

Sql> Show parameter Dump NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ Background_core_dump string Partial background_dump_dest String/u01/app/oracle/diag/rdbms/jia Gulun/jiagulun/trace Core_dump_dest String/u01/app/oracle/diag/rdbms/jia Gulun/jiagulun/cdump Max_dump_file_size String Unlimited Shadow_core_dump string Partial User_dump_dest String/u01/app/oracle/diag/rdbms/jia Gulun/jiagulun/trace Sql>

[email protected] trace]$ cat JIAGULUN_ORA_11175.TRC Trace FILE/U01/APP/ORACLE/DIAG/RDBMS/JIAGULUN/JIAGULUN/TRACE/JIAGULUN_ORA_11175.TRC Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production With the partitioning, OLAP, Data Mining and Real application testing options Oracle_home =/u01/app/product/11.2.0/db_1 System Name:linux Node Name:localhost.localdomain Release:2.6.18-194.el5 Version: #1 SMP Tue Mar 21:52:39 EDT 2010 Machine:x86_64 Instance Name:jiagulun Redo thread mounted by this instance:1 Oracle Process number:29 Unix process pid:11175, Image: [email protected] (TNS v1-v3) 2015-01-03 00:05:56.449 SESSION ID: (36.245) 2015-01-03 00:05:56.449 CLIENT ID: () 2015-01-03 00:05:56.449 SERVICE NAME: (sys$users) 2015-01-03 00:05:56.449 MODULE NAME: ([email protected] (TNS v1-v3)) 2015-01-03 00:05:56.449 ACTION NAME: () 2015-01-03 00:05:56.449 ******************************************************************************** Undo Segment: _syssmu8_2012382730$ (8) ******************************************************************************** Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1:0 spare2:0 #extents: 3 #blocks: 143 Last map 0x00000000 #maps: 0 offset:4080 Highwater:: 0X00C000FC ext#: 1 blk#: 4 ext Size:8 #blocks in SEG. HDR ' s freelists:0 #blocks below:0 MAPBLK 0x00000000 Offset:1 Unlocked Map Header:: Next 0x00000000 #extents: 3 obj#: 0 flag:0x40000000 Extent Map ----------------------------------------------------------------- 0X00C000F1 Length:7 0x00c000f8 Length:8 0X00C02C00 length:128 Retention Table ----------------------------------------------------------- Extent number:0 Commit time:1420272055 Extent number:1 Commit time:1420272055 Extent number:2 Commit time:1420267116 TRN CTL:: seq:0x00b3 chd:0x001b ctl:0x0020 inc:0x00000000 nfb:0x0002 mgc:0xb000 xts:0x0068 flg:0x0001 opt:2147483646 (0x7ffffffe) uba:0x00c000fc.00b3.09 scn:0x0000.0013265c version:0x01 Free BLOCK POOL:: uba:0x00c000fc.00b3.0b ext:0x1 spc:0x1a54 uba:0x00c000fa.00b3.33 ext:0x1 spc:0x680 Uba:0x00000000.00b1.2d ext:0x2 SPC:0X11BC uba:0x00000000.0000.00 ext:0x0 spc:0x0 uba:0x00000000.0000.00 ext:0x0 spc:0x0 TRN TBL:: Index state cflags wrap# uel SCN dba parent-xid NUB stmt_num CMT ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x0355 0x0016 0x0000.00132a0f 0x00c000f6 0x0000.000.00000000 0x00000001 0x00000000 1420272011 ....    . ...   ....     ...     ......           ....            ....             ....            ...       .... 0x20 9 0x00 0x0355 0xffff 0x0000.00132b09 0x00c000fc 0x0000.000.00000000 0x00000001 0x00000000 1420272233 0x21 9 0x00 0x0354 0x000a 0x0000.00132900 0x00c000f6 0x0000.000.00000000 0x00000001 0x00000000 1420271514 EXT TRN CTL:: Usn:8 sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000 sp5:0x00000000 sp6:0x00000000 sp7:0x7fff00000000 sp8:0x00000000 EXT TRN TBL:: Index Extflag exthash extSpare1 extSpare2 --------------------------------------------------- 0x00 0x00000000 0x00000000-0x00000000 0x00000000 ...     .....      .....       ....        ... 0x21 0x00000000 0x00000000 0x00000000 0x00000000 [Email protected] trace]$

Alter system dump DATAFILE 3 block 11254; Rollback block, data block

Dig into a complete transaction