Oracle Table’s Initrans — Am i missing something???

Since this morning i am confused with the initrans values of the table on a 10.2.0.4 version, for which i am writing this blog expecting some help in understanding it 🙂

Well, what is initrans??? i couldn’t find anything much simpler than this (By Tim Hall) —

Let’s say you have a block that has five records in it. Let’s also say that simultaneously five people update each of the records in the block. The block is taking part in 5 transactions. Space is required in the block to keep track of all this stuff. That’s was these parameters are for. The initrans reserves a minimum amount of space in the block that can be used, the maxtrans specifies the maximum amount of space in the block that can be used to hold this information.

SYS at matrix >select table_name,ini_trans from dba_tables where table_name in ('TAB_ASSM','TAB_MSSM');

TABLE_NAME                      INI_TRANS
------------------------------ ----------
TAB_ASSM                                1
TAB_MSSM                                1

Elapsed: 00:00:00.21
SYS at matrix >

The table TAB_ASSM is created in tablespace with Automatic segment space management and TAB_MSSM on a manual segment space management tablespace.Checked the header blocks of the table

ANAND at matrix >select segment_name,tablespace_name,header_file, header_block from dba_segments where segment_name in ('TAB_ASSM','TAB_MSSM');

SEGMENT_NAME                                                                      TABLESPACE_NAME        HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ------------------------------ ----------- ------------
TAB_ASSM                                                                          MAIN                             6           19
TAB_MSSM                                                                          COMP_TBS                         7            9

Elapsed: 00:00:00.10

Lets dump one of the data block.To do so needed the block_id which can be taken from dba_extents

ANAND at matrix >select SEGMENT_NAME,BLOCK_ID,FILE_ID,TABLESPACE_NAME from dba_extents where SEGMENT_NAME in ('TAB_ASSM','TAB_MSSM');

SEGMENT_NAME                                                                        BLOCK_ID    FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------------- ---------- ---------- ------------------------------
TAB_ASSM                                                                                  17          6 MAIN
TAB_ASSM                                                                                  25          6 MAIN
...............
TAB_ASSM                                                                                  89          6 MAIN
...............
TAB_ASSM                                                                                 649          6 MAIN
TAB_ASSM                                                                                 777          6 MAIN

TAB_MSSM                                                                                   9          7 COMP_TBS
TAB_MSSM                                                                                  17          7 COMP_TBS
....................
TAB_MSSM                                                                                 121          7 COMP_TBS
....................
TAB_MSSM                                                                                 649          7 COMP_TBS

42 rows selected.

Elapsed: 00:00:00.28
ANAND at matrix >

Dumped one of the data block from both tables .

SYS at matrix >alter system dump datafile 6 block 89;

System altered.

Elapsed: 00:00:00.28
SYS at matrix >alter system dump datafile 7 block 121;

System altered.

Elapsed: 00:00:00.23
16:04:16 SYS at matrix >

The dump trace for the blocks of the two different tables showed Initrans values as 3.

Dump of block 89 from datafile 6.The datafile 6 is automatic segment space management tablespace.

*** SESSION ID:(138.45) 2010-12-18 12:57:37.286
Start dump data blocks tsn: 7 file#: 6 minblk 89 maxblk 89
buffer tsn: 7 rdba: 0x01800059 (6/89)
scn: 0x0000.001a51b7 seq: 0x02 flg: 0x04 tail: 0x51b70602
frmt: 0x02 chkval: 0x6bac type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x053B6600 to 0x053B8600
53B6600 0000A206 01800059 001A51B7 04020000  [....Y....Q......]
53B6610 00006BAC 00000001 0000D2D6 001A5196  [.k...........Q..]
......................................
......................................
Block header dump:  0x01c00069
 Object id on Block? Y
 seg/obj: 0xd326  csc: 0x00.1b2220  itc: 3  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.001b2220
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 
data_block_dump,data header at 0x53b6674

Dump of block 121 from datafile 7.The datafile 7 is manual segment space management tablespace.

Start dump data blocks tsn: 8 file#: 7 minblk 121 maxblk 121
buffer tsn: 8 rdba: 0x01c00079 (7/121)
scn: 0x0000.001b223d seq: 0x02 flg: 0x04 tail: 0x223d0602
frmt: 0x02 chkval: 0xb33e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04C36600 to 0x04C38600
4C36600 0000A206 01C00079 001B223D 04020000  [....y...="......]
4C36610 0000B33E 00000001 0000D326 001B2220  [>.......&... "..]
..................................
..................................
Block header dump:  0x01c00079
 Object id on Block? Y
 seg/obj: 0xd326  csc: 0x00.1b2220  itc: 3  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.001b2220
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Now, what if i increase the initrans to 5 and then check data block dump??Lets find out –

ANAND at matrix >alter table TAB_ASSM initrans 5;

Table altered.

Elapsed: 00:00:00.03
ANAND at matrix >alter table TAB_MSSM initrans 5;

Table altered.

Elapsed: 00:00:00.03
ANAND at matrix >select table_name,ini_trans from dba_tables where table_name in ('TAB_ASSM','TAB_MSSM');

TABLE_NAME                      INI_TRANS
------------------------------ ----------
TAB_ASSM                                5
TAB_MSSM                                5

Elapsed: 00:00:00.12
ANAND at matrix >insert into TAB_MSSM select * from TAB_MSSM;

101360 rows created.

Elapsed: 00:00:05.01
ANAND at matrix >commit;

Commit complete.

Elapsed: 00:00:00.01
ANAND at matrix >insert into TAB_ASSM select * from TAB_ASSM;

101282 rows created.

Elapsed: 00:02:11.98
ANAND at matrix >commit;

Taking the dump of blocks and checking the initrans values.

SYS at matrix >conn / as sysdba
Connected.
SYS at matrix >alter system dump datafile 7 block 2441;

System altered.

Elapsed: 00:00:00.15
SYS at matrix >alter system dump datafile 6 block 2056;

System altered.

Elapsed: 00:00:00.09

The data block dump shows 

*** SESSION ID:(159.27) 2010-12-18 19:18:06.687
Start dump data blocks tsn: 8 file#: 7 minblk 2441 maxblk 2441
buffer tsn: 8 rdba: 0x01c00989 (7/2441)
scn: 0x0000.001b92c3 seq: 0x01 flg: 0x06 tail: 0x92c30601
frmt: 0x02 chkval: 0xf78b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04C36600 to 0x04C38600
4C36600 0000A206 01C00989 001B92C3 06010000  [................]
4C36610 0000F78B 00000001 0000D326 001B9258  [........&...X...]
.......................................
.......................................
Block header dump:  0x01c00989
 Object id on Block? Y
 seg/obj: 0xd326  csc: 0x00.1b9258  itc: 5  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.024.0000019b  0x008002a0.01a2.21  --U-   70  fsc 0x0000.001b92c3
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 
data_block_dump,data header at 0x4c366a4
 
=====================================================================
*** SESSION ID:(159.31) 2010-12-18 19:23:40.937
Start dump data blocks tsn: 7 file#: 6 minblk 2056 maxblk 2056
buffer tsn: 7 rdba: 0x01800808 (6/2056)
scn: 0x0000.001b9436 seq: 0x01 flg: 0x06 tail: 0x94360601
frmt: 0x02 chkval: 0x2c1a type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04C36600 to 0x04C38600
4C36600 0000A206 01800808 001B9436 06010000  [........6.......]
4C36610 00002C1A 00000001 0000D2D6 001B9303  [.,..............]
..................................
..................................
Block header dump:  0x01800808
 Object id on Block? Y
 seg/obj: 0xd2d6  csc: 0x00.1b9303  itc: 5  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x180078a ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.02b.000001bc  0x0080018f.01b0.1e  --U-   71  fsc 0x0000.001b9436
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0x4c366ac

So i believe that though the data dictionary shows INITRANS as 1, but in actual the table have 3 ITL slots, by default (atleast in 10.2.0.4).

Reference http://www.oracle-base.com/forums/viewtopic.php?f=1&t=4078

Advertisements

7 thoughts on “Oracle Table’s Initrans — Am i missing something???

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s