Deadlock ITL Waits

From: Stalin <stalinsk_at_gmail.com>
Date: Wed, 20 Jul 2011 15:37:07 -0700
Message-ID: <CA+FfP7j57U4TAmHvxy4j5_04LSDrbhGPCCJfTs8fRhAjoBPn0g_at_mail.gmail.com>

We have been seeing lots of deadlock errors lately in load testing environments and they all have been due to enq: TX - allocate ITL entry. In reviewing the statspack report for the periods of deadlock, i see that, log file sync wait being the top consumer with a terrible wait time. That makes to me think the deadlock, is just a symptom of high log file sync wait times. Below is the snippet from statspack and looking at these numbers, especially CPU not being heavily loaded, wondering if this could be a case of storage issue. Sys Admins are checking the storage layer but thought would check here get any opinions/feedback.

Top 5 Timed Events                                                    Avg
%Total
~~~~~~~~~~~~~~~~~~                                                   wait
Call
Event                                            Waits    Time (s)   (ms)

Time

----------------------------------------- ------------ ----------- ------
------
log file sync                                1,400,773   4,357,902   3111
91.4
db file sequential read                        457,568     334,834    732
 7.0
db file parallel write                         565,843      27,573     49


  .6

read by other session                           16,168       7,395    457


  .2

enq: TX - allocate ITL entry                       575       6,854  11919


  .1

         -------------------------------------------------------------
Host CPU  (CPUs: 64  Cores: 8  Sockets: 1)
~~~~~~~~              Load Average
                     Begin     End      User  System    Idle     WIO
WCPU
                   ------- -------   ------- ------- ------- -------
--------
                      3.13    7.04      2.26    3.30   94.44    0.00    7.81

Statistic                                      Total     per Second    per

Trans

--------------------------------- ------------------ --------------
------------
redo synch time                          435,852,302      120,969.3
 309.7
redo synch writes                          1,400,807          388.8
 1.0
redo wastage                               5,128,804        1,423.5
 3.6
redo write time                              357,414           99.2
 0.3
redo writes                                    9,935            2.8
 0.0
user commits                               1,400,619          388.7

 1.0

Environment : 11gr2 EE (11.2.0.1), Sol 10 Sparc

Thanks,
Stalin

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 20 2011 - 17:37:07 CDT