Home > Oracle, Performance > ITL Waits – 2

ITL Waits – 2


As I said in the previous post, the straight forward method to resolve ITL wait is to increase the ITL slots while creating the table or modifying an existing table.  As this is a standard way, I am not going explain this method.  But, sometimes the data distribution in a table can contribute to the ITL waits.  From my test table I will create two tables using the same data.


SQL> create table abc_unorg as select * from abc where 1 = 2 ;

Table created.

SQL> alter table abc_unorg  pctfree 1 ;

Table altered.

SQL> create table abc_org as select * from abc where 1 = 2 ;

Table created.

SQL> alter table abc_org  pctfree 1 ;

Table altered.

SQL> select TABLE_NAME,PCT_FREE,INI_TRANS from user_tables;

TABLE_NAME                       PCT_FREE  INI_TRANS

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

ABC_UNORG                               1          1

ABC                                     2          1

ABC_ORG                                 1          1

Then I will populate both table from the same source in two different ways – data organized and unorganized as below.  Unorganized table will use a simple  INSERT.


SQL> insert into ABC_UNORG select * from abc;

371744 rows created.

SQL> commit ;

Commit complete.

While for the second table, ABC_ORG,  I will ORDER the data and load.


SQL> insert into ABC_ORG select * from abc order by mod(object_id,10);

371744 rows created.

SQL> commit ;

Commit complete.

So we have the test tables are ready with same configuration and same data, except the data distribution is different.  Then I run 7 different  SQL  UPDATEs as 7 different transactions ( from 7 different sessions simultaneously)  against the first unorganized table.


update abc_unorg set owner='XxxXXXXXXXXXXXXXXXXXxxxxxxXXXXXXXXXXXX' where mod(object_id,10)=1;

update abc_unorg set owner='XxxXXXXXXXXXXXXXXXXXxxxxxxXXXXXXXXXXXX' where mod(object_id,10)=2;

update abc_unorg set owner='XxxXXXXXXXXXXXXXXXXXxxxxxxXXXXXXXXXXXX' where mod(object_id,10)=3;

update abc_unorg set owner='XxxXXXXXXXXXXXXXXXXXxxxxxxXXXXXXXXXXXX' where mod(object_id,10)=4;

update abc_unorg set owner='XxxXXXXXXXXXXXXXXXXXxxxxxxXXXXXXXXXXXX' where mod(object_id,10)=5;

update abc_unorg set owner='XxxXXXXXXXXXXXXXXXXXxxxxxxXXXXXXXXXXXX' where mod(object_id,10)=6;

update abc_unorg set owner='XxxXXXXXXXXXXXXXXXXXxxxxxxXXXXXXXXXXXX' where mod(object_id,10)=7;

As expected I got ITL waits as the pctfree was just  1% and INITTRANS were 1.    Only 3 session were able proceed with the UPDTAE statement while the 5 session waiting on ‘enq: TX – allocate ITL entry ‘


SEQ#        SID USERNAME             EVENT                                  P1         P2         P3  SQL_HASH_VALUE SECONDS_IN_WAIT SQL_ID

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

28         91 APPLE                enq: TX - allocate ITL entry   1415053316     589839       3040     2346547447              14 bmgrgky5xuy7r

28         74 APPLE                enq: TX - allocate ITL entry   1415053316     393242       3188     3997157940              14 dtw3dcbr3zkjn

27         81 APPLE                enq: TX - allocate ITL entry   1415053316     589839       3040      764038200              20 8k203rwqsnm1s

2693         92 APPLE                free buffer waits                       4      17673          3     3143069066               0 5tsz18yxpfvca

28         87 APPLE                enq: TX - allocate ITL entry   1415053316      65546       2424     2491327179              14 26ht7d6a7x8qb

2636         86 APPLE                buffer busy waits                       4      16071          1     2664539258               0 4tn3m36gd393u

2658         82 APPLE                free buffer waits                       4      16071          0     2618831903               0 dsxvrhyf1hd0z

Since I have configured with the table with  PCTFREE 1%, dynamic ITITTRANS allocation is not happening for concurrent transactions as there is no space left in the block.    Then I run the same UPDATE transactions against ABC_ORG table and  the waits were,


SEQ#        SID USERNAME             EVENT                                  P1         P2         P3 LAST_CALL_ET SQL_HASH_VALUE SECONDS_IN_WAIT SQL_ID

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

2602         74 APPLE                free buffer waits                       4      22921          3           28     1294515348               0 1xfxu856kjg4n

1677         86 APPLE                free buffer waits                       4      22021          0           28     2345621301               0 017jxka5wyptp

2184        115 APPLE                free buffer waits                       3       2256          3           28     3530279707               0 8pnr4sg96rksv

2524         92 APPLE                free buffer waits                       4      22921          3           28     2822052462               0 aqsb0f6n3a6mf

2572         83 APPLE                free buffer waits                       4      22921          3           28     1753863190               0 3pdu5b1n8mp0q

2594         84 APPLE                free buffer waits                       4      22636          3           28     2561380704               0 58tppz6car4b0

2637         87 APPLE                free buffer waits                       4      22921          3           28     2118971024               0 1mwba09z4tvnh

Updates to the second table going well, not even a single “enq: TX – allocate ITL entry   ”, all the session were able to progress, though there are too many “free buffer waits” which I will cover in the next post.   So, the first table got too many ITL waits and transactions were hanging while transactions are fine with the second table.  Let us take a random ROWID and get the BLOCK_ID for that row.


SQL> select object_id,mod(object_id,10),dbms_rowid.ROWID_BLOCK_NUMBER('AAAE3pAAEAAAD1uAAP')

from abc_unorg where rowid = 'AAAE3pAAEAAAD1uAAP' ;

OBJECT_ID MOD(OBJECT_ID,10) DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAE3PAAEAAAD1UAAP')

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

5321                 1                                               15726

SQL> select object_id,mod(object_id,10) from abc_unorg where dbms_rowid.ROWID_BLOCK_NUMBER(rowid) = 15726 ;

OBJECT_ID MOD(OBJECT_ID,10)

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

5302                 2

5304                 4

5305                 5

5306                 6

== Removed the rows for clarity ==

5383                 3

5385                 5

5386                 6

 

73 rows selected.

SQL> select mod(object_id,10),count(*) from abc_unorg

where dbms_rowid.ROWID_BLOCK_NUMBER(rowid) = 15726

group by mod(object_id,10) ;

MOD(OBJECT_ID,10)   COUNT(*)

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

5          8

6          8

8          8

7          6

2          6

4          7

9          8

0          8

1          7

3          7

10 rows selected.

That means the WHERE clause condition MOD(object_ID,10)  satisfies minimum of 6 rows for each value  in each block, so our transaction forced to create an ITL slot in all the blocks.  While the second table that is not the case, remember I have ordered the data.


SQL> select object_id,mod(object_id,10) from abc_org

where dbms_rowid.ROWID_BLOCK_NUMBER(rowid) = 15856 ;

OBJECT_ID MOD(OBJECT_ID,10)

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

157460                 0

157470                 0

157480                 0

== removed the rows for clarity ====

60240                 0

60250                 0

80 rows selected.

Since I have ordered the data, each block will have similar data so that only one transaction slot will be created almost in all the blocks.   There will be some blocks may contain different values for mod(object_id,10), but chances are very less to get a ITL waits.   Data distribution can leads to various CONCURRENCY waits, not just ITL waits alone.

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: