Home > Oracle, Performance > ITL Waits

ITL Waits


ITL waits are so common and sometimes it will kill the concurrency of the application.  In general there  are 1 ITL slot (caused by INITRANS) for tables and 2 slots for indexes.    When a data block is formatted   the ITL slot is created in the variable part of the block header as specified by INITRANS for that segment.  As long as free space is available in the block, ITL slots can be grown up to MAXTRANS for any future requirements.   Here plays the important role PCTFREE which can impact negatively.  With the default setting 10% can fill up the blocks 100%, and that will stop creating new ITL slots for any second transaction happening to that block.

I have created my test table ABC from DBA_OBJECTS. Initiated 7 parallel update sessions to the table such way that each UPDATE will go to all the data blocks.


SQL> select PCT_FREE,INI_TRANS from dba_tables where table_name='ABC';

PCT_FREE  INI_TRANS

---------- ----------

2          1

update abc set owner='XXXXXXXXXX' where mod(object_id,10)=1;

update abc set owner='XXXXXXXXXX' where mod(object_id,10)=2;

update abc set owner='XXXXXXXXXX' where mod(object_id,10)=3;

update abc set owner='XXXXXXXXXX' where mod(object_id,10)=4;

update abc set owner='XXXXXXXXXX' where mod(object_id,10)=5;

update abc set owner='XXXXXXXXXX' where mod(object_id,10)=6;

update abc set owner='XXXXXXXXXX' where mod(object_id,10)=7;

As expected, I got ‘enq: TX – allocate ITL entry” waits for 3 UPDATES out of 7 sessions.


SQL> l

1  select v.seq#,username,v.event,last_call_et,v.SECONDS_IN_WAIT

2  from v$session_wait v, v$session s  where v.event not like '%message%' and s.sid=v.sid and v.event not like '%jobq slave wait%' and s.username='APPLE'

3* order by last_call_et;

SEQ# USERNAME             EVENT                          LAST_CALL_ET SECONDS_IN_WAIT

---------- -------------------- ------------------------------ ------------ ---------------

79 APPLE                enq: TX - allocate ITL entry            151             127

1262 APPLE                enq: TX - allocate ITL entry            155             132

47464 APPLE                buffer busy waits                       161               1

18677 APPLE                enq: TX - allocate ITL entry            169             140

52066 APPLE                free buffer waits                       174               1

27078 APPLE                db file sequential read                 179               0

45204 APPLE                read by other session                   184               0

7 rows selected.

Interestingly the sessions were waiting up to 140 seconds just to move out of the current SEQ#.  That’s not all – even after 3 sessions completed the UPDATES, still the other 3 sessions continued to wait as I did not commit the transactions, so that it will free the ITL slots..


SEQ# USERNAME             EVENT                          LAST_CALL_ET SECONDS_IN_WAIT

---------- -------------------- ------------------------------ ------------ ---------------

79 APPLE                enq: TX - allocate ITL entry            442             411

1262 APPLE                enq: TX - allocate ITL entry            446             416

18677 APPLE                enq: TX - allocate ITL entry            460             424

As soon as I commit, the hanging sessions were able to resume the transactions


SEQ# USERNAME             EVENT                          LAST_CALL_ET SECONDS_IN_WAIT

---------- -------------------- ------------------------------ ------------ ---------------

483 APPLE                read by other session                   496               0

1684 APPLE                db file sequential read                 500               0

19986 APPLE                read by other session                   514               0

So, application concurrency can be in danger if there is no space in a data block to create ITL slots which governed by a neglected table level parameter PCTFREE.   Now how to fix this ITL wait problem?

Option 1 : Allocate enough PCTFREE while creating the table.

Make sure you have allocated enough free space while creating the table by properly sizing the PCTFREE table level parameter.  I will re-build ABC table after changing the PCTFREE parameter to   10% using table MOVE


SQL> alter table abc pctfree 10;

Table altered.

SQL> alter table abc move ;

Table altered.

Now all my UPDATES doing the concurrently – and definitely I am going to ignore the “free buffer waits” for my current test.


SQL> /

SEQ# USERNAME             EVENT                          LAST_CALL_ET SECONDS_IN_WAIT

---------- -------------------- ------------------------------ ------------ ---------------

14991 APPLE                free buffer waits                         4               0

12087 APPLE                free buffer waits                        10               0

56614 APPLE                free buffer waits                        16               0

40377 APPLE                free buffer waits                        21               0

60609 APPLE                free buffer waits                        25               0

33776 APPLE                free buffer waits                        29               0

52940 APPLE                free buffer waits                        34               0

7 rows selected.

So, keeping right PCTFREE is important not only for avoiding row migration, but will help the application concurrency as well.  I will add two more ways to avoid this while one just adding more INITRANS and the other one is more interesting.

About these ads
  1. No comments yet.
  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: