Re: High "log file sync" Waits in Statspack, - Online Redo Logs on Mounted File system of SAN Storage

  • From: Asif Momen <asif_oracle@xxxxxxxxx>
  • To: jonathan@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 12 Jan 2008 03:02:23 -0800 (PST)

Hi,

Asynchronous Commit has been introduced with Oracle 10g Release 2 and is useful 
for high transaction throughput. Probably, you might consider using this after 
reading Oracle doc's:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqlproc.htm#BEJIADAH

However, with the new feature there are new drawbacks, transactions may be lost 
if a machine crashes or file i/o problems occur with redo log files.

<<
Note that the specification of the NOWAIT and BATCH  options allows a small 
window of vulnerability in which Oracle Database can roll  back a transaction 
that your application view as committed. Your application  must be able to 
tolerate the following scenarios:
 
    The database host crashes, which causes the database to lose redo that was  
buffered but not yet written to the online redo logs.
 
    A file I/O problem prevents log writer from writing buffered redo to disk. 
If  the redo logs are not multiplexed, then the commit is lost.

>>

Regards

Asif Momen
http://momendba.blogspot.com


Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote: 
Start with a sanity check -

You have 762 transactions per second. So in any
1/100 of a second, roughly 8 transactions commit.

If it takes the log writer 1/100 of a second to clear
that little bit of log buffer, you have 8 sessions waiting
on log file sync while the log writer writes.

In other words (and very informally) the system summary
of log file sync will make it look  at least eight times as bad
as it really is.

Use Tanel Poder's session snapshot code to get a report
for just one session while the test is going on as a guideline
to whether or not any individual session can see a problem
with log file sync.

Alternatively, run up a quick reports against V$session_event
at some point in mid-test.  You don't have to do anything very
sophisticated to be able to discount this wait if it really isn't
causing a genuine problem.





Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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


----- Original Message ----- 
From: "VIVEK_SHARMA" 
To: 
Sent: Saturday, January 12, 2008 1:56 AM
Subject: High "log file sync" Waits in Statspack, - Online Redo Logs on Mounted 
File system of SAN Storage



Folks

During a Benchmark Run of OLTP Transactions very High "log file sync" wait is 
occuring
Storage Box - Sun StoreEdge 9990V (Hitachi SAN)
Online Redo logfiles exist on Mounted Filesystem on a LUN / Volume of 4+4 Hard 
Disks , (RAID 1+0 Type)
Hard Disk - 15 K RPM
Storage Cache - 128 GB
Online Redo Logfile size 750 MB
log_buffer= 4M

Oracle 10.2.0.3
Solaris 10

Qs On the Storage Box, should a few of its HBA Controllers & respective Ports 
be 
assigned dedicatedly only to the online Redo LUN / Volume?
Qs Should Online Redo Logiles be moved to RAW Devices on the SAN Storage?
Qs Any advisable init.ora parameters?

Qs How should this issue be approached?

Will share Statspack as needed

Cheers & Thanks

P.S.

Top 5 Timed Events                                                 Avg    %Total

~~~~~~~~~~~~~~~~~~                                                 wait   Call

Event                                     Waits        Time (s)    (ms)   Time

----------------------------------------- ------------ ----------- ------ ------

log file sync                             3,511,274    328,288     93     73.6

SQL*Net break/reset to client             732,491      49,120      67     11.0

CPU time 31,699 7.1

latch: virtual circuit queues             1,099,453    10,695      10     2.4

db file parallel write                    3,253,478    9,535       3      2.1

-------------------------------------------------------------



Host CPU (CPUs: 80)

~~~~~~~~ Load Average

Begin    End      User    System   Idle   WIO     WCPU

------- -------   ------- ------- ------- ------- --------

                9.13    1.79

               16.35   23.30   60.35    0.00    2.79



Load Profile     Per Second Per  Transaction

~~~~~~~~~~~~     --------------- ---------------

Redo size:       3,143,457.88    4,121.10

...

Block changes:   15,320.88       20.09

...

Physical writes: 2,176.56        2.85

User calls:      14,769.89       19.36

...

Logons:          3.55            0.00

Executes:        12,950.32       16.98

Transactions:    762.77

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely 
for 
the use of the addressee(s). If you are not the intended recipient, please 
notify the sender by e-mail and delete the original message. Further, you are 
not to copy, disclose, or distribute this e-mail or its contents to any other 
person and any such actions are unlawful. This e-mail may contain viruses. 
Infosys has taken every reasonable precaution to minimize this risk, but is not 
liable for any damage you may sustain as a result of any virus in this e-mail. 
You should carry out your own virus checks before opening the e-mail or 
attachment. Infosys reserves the right to monitor and review the content of all 
messages sent to or from this e-mail address. Messages sent to or from this 
e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
--
//www.freelists.org/webpage/oracle-l




-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.0/1218 - Release Date: 10/01/2008 
13:32


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




       
---------------------------------
Never miss a thing.   Make Yahoo your homepage.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2008