Skip to main content

Search

Welcome to Tibero GTS!

[Based on Tibero 6] Example of Checking ITL Changes Within Data Blocks According to Transactions

Document Type | Technical Information

Category | Monitoring/Inspection

Document Number | TMOTI023

 

Overview

This guide explains how information related to transaction processing is recorded and handled inside data blocks, and how to verify the internal data block information using the dump function.
Note
The test was conducted in the following environment.
  • [tibero@T1:/tibero]$ tbboot -v
  • Tibero 6   (DB 6.0 FS06_CS_1703)
  • Linux stopbugs 2.6.9-89.ELsmp #1 SMP Mon Jun 22 12:31:33 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)
  • Patch files (none)
 
 

Method

Table Creation and Data Insertion

CREATE TABLE TIBERO.T2 (c1 number, c2 char(2000));
insert into tibero.t2 values(111,'A');
insert into tibero.t2 values(222,'B');
insert into tibero.t2 values(333,'C');
insert into tibero.t2 values(444,'D');
insert into tibero.t2 values(555,'E');
insert into tibero.t2 values(666,'F');


Data Query

select C1, SUBSTR(C2,1,1) from tibero.t2;


C1    C2
-----------
111   A
222   B
333   C
444   D
555   E
666   F


Querying File and Block Numbers

select C1,
       rowid,
       dbms_rowid.rowid_block_number(rowid) as blkno,
       dbms_rowid.rowid_to_relative_fno(rowid) as fno2
from tibero.t2;

        C1 ROWID                   BLKNO       FNO2
---------- ------------------ ---------- ----------
       111 AAAA5LAACAAADocAAA      14876          2
       222 AAAA5LAACAAADocAAB      14876          2
       333 AAAA5LAACAAADocAAC      14876          2
       444 AAAA5LAACAAADodAAA      14877          2
       555 AAAA5LAACAAADodAAB      14877          2
       666 AAAA5LAACAAADodAAC      14877          2


Executing Data Block Dump

alter system dump datafile 2 block 14876;
alter system dump datafile 2 block 14877;

 

Explanation of Block Dump Items and Analysis Results

ITL (Interested Transaction List)
  • In the example, the fl (flag) value of itl 0 is registered as 'A'.
  • 'A' means that the transaction is in an active state that has not been committed.
 
Row Piece (rp)
  • Currently, there are **6 row pieces (rp)** spanning a total of 2 blocks.
  • There are 3 rp in the first block and 3 rp in the second block.
 
xid value: 0007.33.0134
  • This is interpreted as three columns (USN, SLOT, SQN) in the v$transaction view.
Column
Description
USN=7
Rollback information is stored in undo segment number 7
SLOT=33
Related information stored in transaction slot number 33 within the undo segment's header block
SQN=134
Indicates that the transaction slot has been reused 134 times (incremented by +1 each reuse)
uea value
  • This represents the address of the undo block.
  • Accessing the UNDO block via this address shows that undo data for the data block is stored here.
 
Dump Results
  • The dump performed before the transaction was committed shows that the fl value of the itl information for all 6 rows is set to 'A'.
  • This means that all rows are in the middle of a transaction (active).
[t72499@localhost tracedump]$ cat tb_dump_20650_19_425.trc

 

Block Dump Results

**Dump start at 2017-12-18 18:21:09
DUMP of BLOCK file #2 block #14876
**Dump start at 2017-12-18 18:21:09
data block Dump[dba=02_00014876(8403484),tsn=0000.000a61d7,type=13,seqno =3]
--------------------------------------------------------------
 sgmt_id=3660  cleanout_tsn=0000.00000000  btxcnt=2
 l1dba=02_00014871(8403479), offset_in_l1=5
 btx      xid                undo           fl  tsn/credit
 00  0007.33.0134  01_00028547.00011.00077  A  0000.00000000
 01  0000.00.0000  00_00000000.00000.00000  I  0000.00000000
--------------------------------------------------------------
Data block dump:
  dlhdr_size=14  freespace=2060  freepos=2158  symtab_offset=0  rowcnt=3
Row piece dump:
 rp 0 6178:  [2010] flag=--H-FL--  itlidx=0    colcnt=2
  col 0: [4]
   0000: 03 C2 81 8B                                     ....
  col 1: [2003]
   0000: FE 07 D0 41 20 20 20 20 20 20 20 20 20 20 20 20 ...A
   0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
   0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
            
~~~~~omitted~~~~

   07D0: 20 20 20                                                       
 rp 1 4168:  [2010] flag=--H-FL--  itlidx=0    colcnt=2
  col 0: [4]
   0000: 03 C2 82 96                                     ....           
  col 1: [2003]
   0000: FE 07 D0 42 20 20 20 20 20 20 20 20 20 20 20 20 ...B           
   0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20                
   0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20                

~~~~~omitted~~~~

   07D0: 20 20 20                                                       
 rp 2 2158:  [2010] flag=--H-FL--  itlidx=0    colcnt=2
  col 0: [4]
   0000: 03 C2 83 A1                                     ....           
  col 1: [2003]
   0000: FE 07 D0 43 20 20 20 20 20 20 20 20 20 20 20 20 ...C           
   0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20                
   0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20                 

~~~~~omitted~~~~

   07D0: 20 20 20                    
End of data block dump
**Dump start at 2017-12-18 18:21:31
DUMP of BLOCK file #2 block #14877

**Dump start at 2017-12-18 18:21:31
data block Dump[dba=02_00014877(8403485),tsn=0000.000a61d7,type=13,seqno =3]
--------------------------------------------------------------
 sgmt_id=3660  cleanout_tsn=0000.00000000  btxcnt=2
 l1dba=02_00014871(8403479), offset_in_l1=6
 btx      xid                undo           fl  tsn/credit
 00  0007.33.0134  01_00028547.00011.00080  A  0000.00000000
 01  0000.00.0000  00_00000000.00000.00000  I  0000.00000000
--------------------------------------------------------------
Data block dump:
  dlhdr_size=14  freespace=2060  freepos=2158  symtab_offset=0  rowcnt=3
Row piece dump:
 rp 0 6178:  [2010] flag=--H-FL--  itlidx=0    colcnt=2
  col 0: [4]
   0000: 03 C2 84 AC                                     ....
  col 1: [2003]
   0000: FE 07 D0 44 20 20 20 20 20 20 20 20 20 20 20 20 ...D
   0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
   0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20              

~~~~~omitted~~~~

   07D0: 20 20 20
 rp 1 4168:  [2010] flag=--H-FL--  itlidx=0    colcnt=2
  col 0: [4]
   0000: 03 C2 85 B7                                     ....
  col 1: [2003]
   0000: FE 07 D0 45 20 20 20 20 20 20 20 20 20 20 20 20 ...E
   0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
   0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

           ~~~~~omitted~~~~

   07D0: 20 20 20
 rp 2 2158:  [2010] flag=--H-FL--  itlidx=0    colcnt=2
  col 0: [4]
   0000: 03 C2 86 C2                                     ....
  col 1: [2003]
   0000: FE 07 D0 46 20 20 20 20 20 20 20 20 20 20 20 20 ...F
   0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
   0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

~~~~~omitted~~~~

 [t72499@localhost tracedump]$

 

 

 

Related to