Re: Lots of "log file sync" waits and nothing else on inserts and commits

From: Bobby Durrett <bdurrettccci_at_yahoo.com>
Date: 29 Jan 2004 06:59:02 -0800
Message-ID: <8c63eb2b.0401290659.78ce1790@posting.google.com>

Jonathan,

I realized this just after I posted it. Duh! But, the log file parallel waits are still much lower than the log file sync waits. During a 10 second period, 8.4 seconds were log file sync waits and 1.5 seconds were log file parallel write waits. So, what is the majority of my time spent on?

Here are my top five waits from statspack for a five minute period:

Top 5 Timed Events

---

% Total

Event                                               Waits    Time (s)

Ela Time

-------------------------------------------- ------------ -----------
--------
log file sync                                       1,490         237 
  59.99
log file parallel write                             2,843          75 
  19.03
CPU time                                                           42 
  10.71
db file parallel write                                 35          31 
   7.92
db file sequential read                               625           3 
    .86
          -------------------------------------------------------------

Thanks for your reply.

• Bobby

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<bv9cef$bls$1_at_sparta.btinternet.com>...
> You have v$session_event for SID = 23.
> The only session that does log file writes is
> LGWR - which usually has a SID around
> 4 or 5
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> Next public appearances:
> Jan 29th 2004 UKOUG Unix SIG - v$ and x$
> March 2004 Hotsos Symposium - The Burden of Proof
> March 2004 Charlotte NC OUG - CBO Tutorial
> April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___February
> ____UK___June
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Bobby Durrett" <bdurrettccci_at_yahoo.com> wrote in message
> news:8c63eb2b.0401281343.58e92404_at_posting.google.com...
> > I'm really puzzled. Check out the session event stats for my session
> > below.
> >
> > According to Note 223117.1 from Metalink, if you have a bunch of "log
> > file sync" waits, and not as many "log file parallel write" waits then
> > the problem is not I/O on the redo logs.
> >
> > In this operation, which is all inserts and commits, we have tons of
> > log file syncs, small cpu (not reflected below, but true) and no log
> > file parallel write waits.
> >
> > Any ideas?
> >
> > - Bobby
> >
> > 1* select event,time_waited from v$session_event where sid=23
> > SQL> /
> >
> > EVENT TIME_WAITED
> > ------------------------------------------------------ -----------
> > latch 4
> > free buffer waits 10047
> > buffer busy 5
> > log buffer space 15324
> > log file switch completion 232
> > log file sync 388300
> > db file sequential read 4536
> > db file scattered read 3696
> > db file parallel read 0
> > direct path read 5
> > direct path write 6067
> > undo segment extension 0
> > SQL*Net message to client 18
> > SQL*Net message from client 59222
> > SQL*Net more data from client 173
> > SQL*Net break/reset to client 1255
> >
> > 16 rows selected.
Received on Thu Jan 29 2004 - 08:59:02 CST