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.

Advertisements
  1. Priyanka Gupta
    August 12, 2017 at 6:51 am

    Is below statement correct for – 100 rows, 100 updates (in a single block) how many ITL entries?

    “If 100 rows in one block and one update is affecting 1 row in the same block (i.e. 100 updates for a single block) max ITL entries will be upto maxtrans value if that much of PCTFREE is available in the block. In this case as soon as commit happens after the 100 statements, ITL slots will be given to the pending update transactions (among those 100) waiting for the ITL slot in that block.”

  2. August 14, 2017 at 3:33 am

    Thanks Priyanka for the question. If 100 different updates from 100 different transactions – it will create 100 ITL enries. There are 3 cases.

    1) 100 rows updated by one update – 1 ITL entry
    2) 100 rows updated by 100 updates in one transaction – 1 ITL Entry
    3) 100 rows updated by 100 updates in 100 transactions – 100 ITL entry

    assumption – all rows are in the same block.

    So the above statement can be right or wrong based on the number of transactions.

    • Priyanka Gupta
      August 14, 2017 at 6:02 pm

      Thanks a lot, it clears the doubt. I was considering 100 updates and one commit after that as 100 transactions. While it will be only one until we commit. Thank u so much.

      • August 24, 2017 at 1:55 am

        Again – 100 transactions should have 100 commits! One commit is not enough.

  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

%d bloggers like this: