Data … as usual

Recently, one of my customer had some performance issues on a batch ran every night.

After analyzing AWR, I found that the top wait event was “enq: TX – allocate ITL entry” on a particular table.

“enq: TX – allocate ITL entry” wait time is increased when an Interest Transaction List (ITL) can’t grow due to lack of space in the block header. (for more information, see this post from Arup Nanda : http://arup.blogspot.fr/2011/01/more-on-interested-transaction-lists.html). If you want to resolve it, you have to increase the INITRANS parameter of the object, and then rebuild it.

Another thing to point out: this wait event is counted by object (in V$SEGMENT_STATISTICS for example), but ITL allocation issues are specific to blocks, and ITL slots count can be different from a block to another one.

So far so good, but … but which value for initrans? To know it, you have to dump every block that contain data (block type : 0x06), and get the block that have the highest number of itl slot allocated. (you can make an arbitrary choice too … 😉 ).

Well, my customer ITL allocation issue was located on a table with more than 60000 blocks of data, so to get the best value for initrans, I had to write few lines of code.

First, I used some code from this blog post (from Oracle Diagnostician : http://savvinov.com/2015/02/26/analyzing-segment-content-by-block-type/) to dump all the blocks of my object:

begin
 for rec in (select file_id, block_id start_block, block_id + blocks - 1 end_block from dba_extents where segment_name = '&segname' and owner = '&ownname') loop
 execute immediate 'alter system dump datafile ' || rec.file_id || ' block min ' || rec.start_block || ' block max ' || rec.end_block;
 end loop;
end;
/

Note : This operation can take some time to be executed, be patient (and don’t forget to check space in your ADR trace directory)

Now, I have a look into my ADR trace directory to get the dump file (which is quite huge).

Then, I wrote a small perl script to analyze this dump file and print high water mark of itl slots.

This very simple perl script can be downloaded here : https://app.box.com/s/ghvb86rg8mk3sb2tieadnie9hbrhxgdf

Here’s a little demo of this script upon a block dump file (which contains many dump blocks)

$ ./cnt_itl.pl p orcl_ora_14878.trc
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0011.01f.00000095  0x00c0364a.00c6.11  ----    1  fsc 0x03fe.00000000
0x02   0x0018.00a.00000044  0x00c0317b.0038.17  ----    1  fsc 0x03fe.00000000
0x03   0x0015.01b.0000004a  0x00c03b10.0044.02  ----    1  fsc 0x03fe.00000000
0x04   0x000e.007.00000077  0x00c03716.008f.03  ----    1  fsc 0x03fe.00000000
0x05   0x0010.020.00000081  0x00c030de.00b1.0d  ----    1  fsc 0x03fe.00000000
0x06   0x0019.01c.00000043  0x00c03555.0042.1c  ----    1  fsc 0x03fe.00000000
0x07   0x0014.01b.0000004c  0x00c036b1.005f.35  ----    1  fsc 0x03fe.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0013.01b.00000052  0x00c039f8.008b.23  --U-    7  fsc 0x0000.00480702
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0013.01b.00000052  0x00c039f8.008b.24  --U-    6  fsc 0x0000.00480702
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

$ ./cnt_itl.pl c orcl_ora_14878.trc
7

Now I can set INITRANS to a value of over 8 (and don’t forget to rebuild it to set this on all data blocks) :

SQL> alter table spl initrans 8;

Table altered.

SQL > alter table spl move tablespace users;

Table altered.