In-depth analysis of a complete transaction, in-depth analysis of the complete transaction

In-depth analysis of a complete transaction, in-depth analysis of the complete transaction

Oracle029 deeply analyzes a complete transaction
2. Diagram of the operational process of a transaction
Undo segment composition: Segment header and rollback Block
Transaction ID: the transaction id allocated by oracle for each transaction

Select xid, xidusn, xidslot, xidsqn, ubablk, ubafil from v $ transaction; // lists the active transactions in the system. Null Insert into t values (1, 'hangsan '); Select xid, xidusn, xidslot, xidsqn, ubablk, ubafil from v $ transaction; 070017008F020000 7 23 655 1037 3 // current active transaction COMMITselect xid, xidusn, xidslot, xidsqn, ubablk, ubafil from v $ transaction; Null // indicates that the transaction has been committed.

Transaction table
The first data block of the undo segment. Each rollback segment can have a maximum of 47 transactions.

Segment Header Select * from v $ rollname; 0 SYSTEM 1 _ syssmuw.378038527 $ 2 _ SYSSMU2_2232571081 $ 3 _ SYSSMU3_2097677531 $ 4 _ SYSSMU4_1152005954 $ 5 _ SYSSMU5_1527469038 $ 6 _ SYSSMU6_2443381498 $ 7 _ SYSSMU7_3286610060 $ 8 _ SYSSMU8_2012382730 $ 9 _ SYSSMU9_1424341975 $ 10 _ SYSSMU10_3550978943 $ Select header_block, header_file from dba_segments where segment_name = '_ syssmu%3780%27 $'; 128 3 Alter system dump undo header '_ syssmu%3780%27 $'; // view the process ID of the current session by dumping the rollback segment header 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 '_ syssmuw.378020.27 $ '; // dumping the rollback segment alter system dump datafile 5 block 4308; // dumping the rollback segment data block

1. At the beginning of a transaction, write the transaction information XID in the transaction table of the undo header block, and write the transaction information XID in the transaction slot of the data block to be modified.
Transaction slot: xid (the transaction slot of the block to be modified) and uba (the address information of the data block where the original data exists in the undo)
In-depth analysis of transaction slots and transaction commit Methods 7. Transaction slot ITL
The default value is 1.
Maximum 255 (cannot be changed from Oracle10g)

DBA_TABLESDescribes all relational tables in the database. USER_TABLESDescribes the relational tables owned by the current user. This view does not displayOWNERColumn. 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, 'A ');
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 transform (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 // the file number and block number corresponding to 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 19 868 1234672 // UBA file number, block number, rollback block header block, which row of records in the table, the number of times the record is overwritten (the information exists in both the transaction table and the transaction operation)

Select * from v $ rollname; // query the field Header

0 SYSTEM 1 _ syssmuw.378038527 $ 2 _ SYSSMU2_2232571081 $ 3 _ SYSSMU3_2097677531 $ 4 _ SYSSMU4_1152005954 $ 5 _ SYSSMU5_1527469038 $ 6 _ SYSSMU6_2443381498 $ 7 _ SYSSMU7_3286610060 $ 8 _ SYSSMU8_2012382730 $ 9 _ SYSSMU9_1424341975 $ 10 _ 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 // use 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>

[Oracle @ localhost trace] $ cat jiagulun_ora_11175.trc Trace file/u01/app/oracle/diag/rdbms/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 16 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: oracle@localhost.localdomain (TNS V1-V3) * ** 00:05:56. 449 * ** Session id: (36.245) 00:05:56. 449 * ** Client id :() 00:05:56. 449 * ** Service name :( SYS $ USERS) 00:05:56. 449 * Module name :( sqlplus@localhost.localdomain (TNS V1-V3) 00:05:56. 449 * ** Action name :() 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 Bytes ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x0355 0x0016 0x0000. 00132a0f 0x00c000f6 0x0000. 000.00000000 0x00000001 0x00000000 1420272011 ........................................ 0x20 9 0x00 0x0355 0 xffff 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 [Oracle @ localhost trace] $

Alter system dump datafile 3 block 11254; // rollback block, data block