Re: log file sync Wait

Usual caveat:

    looking a v$system_event can be very misleading,     you need to examine v$session_event to determine     if anyone is actually noticing a problem.

Usual caveat 2:

    A statspack report without a time interval     is almost meaningless. However, in this case,     log file sync at the top is sufficiently unusual to     warrant a little hypothesis.

Question: Was log file write really number two, or have you knocked out one or two lines between the two log-related waits ?

Log file syncs are from the sessions,
log file writes are from LGWR

A log file sync is a call from a session to lgwr to write some log buffer to disc. As such, you could get multiple sessions calling at about the same time - and only the first one in gets lgwr to write, the rest have to wait until lgwr returns and notices that there is now a queue and does a piggyback write.

Consequently, it is possible on a highly concurrent system for log file sync to have far more WAITS then log file write, and therefore look a much bigger problem than it really is.

However, in your case, the number of log file sync WAITS is about the same as the number of log file write WAITS - so the fact that the TIME is five times as long suggests that concurrency of waits is not the issue, and you may have a proper problem.

I suspect that the problem is the number of processes running on your system. Session A issues a log file sync, and goes off the run queue; some time later, lgwr gets the message and writes and posts session A to allow it to go back on the run queue. Session A sits on the run queue for ages, and finally becomes runnable. Solution - look at MTS, or get more CPUs on the box.

But having said that - do check if any sessions are actually noticing a significant loss of time due to log file sync before worry about it.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 02 January 2003 07:48

>
>What ALL may be Done to Address the Following ?
>Any /etc/system , init.ora parameter Changes too ?
>Moving the Online Redo Logfiles onto RAID 1 NOT possible as that may
warrant Additional Hardware . Moreover T3+ does NOT Support RAID 1 (Only RAID 1+ )
>
>
>Concurrent Oracle processes = 1500 Approx.
>Statspack Taken during Mostly OLTP Operations :-
>
>Top 5 Wait Events
>~~~~~~~~~~~~~~~~~ Wait
% Total
>Event Waits Time (cs)
Wt Time
>-------------------------------------------- ------------ -----------
- -------
>log file sync 970,563
2,597,831 57.46
>log file parallel write 831,141
484,948 10.73
>
>log_buffer = 2MB
>Online Redo Logfiles Exist on RAID 1+
>Storage Box is T3+
>File System = UFS
>
>Application = Banking (Hybrid )
>Oracle 8.1.7.4
>Solaris 8
>Machine Box = SF6800
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).