Yeah, the massive log waits could exacerbate the ITL waits as things will be slow commiting their changes. The db file sequential reads are huge as well. Looks like you have a problem on your storage. Check the log write times ( I blogged some of this at http://dboptimizer.com/2011/07/20/wait-event-and-wait-class-metrics-vs-vsystem_event/) Latencies in the past minute col name for a25 select m.intsize_csec, n.name , m.time_waited, m.wait_count, 10*m.time_waited/nullif(m.wait_count,0) avgms from v$eventmetric m, v$event_name n where m.event_id=n.event_id and n.name in ( 'log file sync', 'log file parallel write' ); Latencies averaged over each hour select btime, (time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0) avg_ms from ( select to_char(s.BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI') btime, total_waits count_end, time_waited_micro/1000 time_ms_end, Lag (e.time_waited_micro/1000) OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg, Lag (e.total_waits) OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg from DBA_HIST_SYSTEM_EVENT e, DBA_HIST_SNAPSHOT s where s.snap_id=e.snap_id and e.event_name in ( 'log file sync', 'log file parallel write' ) order by begin_interval_time ) order by btime / If the log file parallel writes are bad then probably something wrong on the storage system. - Kyle Hailey http://dboptimizer.com On Wed, Jul 20, 2011 at 3:37 PM, Stalin <stalinsk@xxxxxxxxx> wrote: > 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 > >