RE: Deadlock ITL Waits

From: Chitale, Hemant Krishnarao <Hemant.Chitale_at_sc.com>
Date: Fri, 22 Jul 2011 11:47:16 +0800
Message-ID: <B50E89E32AE9D848BC2DC93BD744308DEA07BE_at_HKJUMXMB107B.zone1.scb.net>

This seems to be similar to this thread : http://forums.oracle.com/forums/thread.jspa?threadID=2256521&tstart=0

1.4million commits and 1.4million 'log file sync' waits of 3seconds each ?!!!

Given that you have reported (from another email)

Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
log file parallel write      38K  72.5  15.4   5.4   2.0    .8    .4   1.3   2.2
log file sync               838K   2.9   1.0    .5   1.7   1.7    .8   7.6  83.8

I would guess that are are certain very very large spikes in I/O response times (or that there's a bug in the timed_statistics)

(A 64 CPU install without the Diagnostic Pack licence ?)

 
Hemant K Chitale

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stalin Sent: Thursday, July 21, 2011 6:37 AM
To: oracle-l
Subject: Deadlock ITL Waits

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

This email and any attachments are confidential and may also be privileged. If you are not the addressee, do not disclose, copy, circulate or in any other way use or rely on the information contained in this email or any attachments. If received in error, notify the sender immediately and delete this email and any attachments from your system. Emails cannot be guaranteed to be secure or error free as the message and any attachments could be intercepted, corrupted, lost, delayed, incomplete or amended. Standard Chartered PLC and its subsidiaries do not accept liability for damage caused by this email or any attachments and may monitor email traffic.

Standard Chartered PLC is incorporated in England with limited liability under company number 966425 and has its registered office at 1 Aldermanbury Square, London, EC2V 7SB.

Standard Chartered Bank ("SCB") is incorporated in England with limited liability by Royal Charter 1853, under reference ZC18. The Principal Office of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the United Kingdom, SCB is authorised and regulated by the Financial Services Authority under FSA register number 114276.

If you are receiving this email from SCB outside the UK, please click http://www.standardchartered.com/global/email_disclaimer.html to refer to the information on other jurisdictions.

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

Received on Thu Jul 21 2011 - 22:47:16 CDT