RE: High "log file sync" Waits in Statspack, - Online Redo Logs on Mounted File system of SAN Storage
Date: Sat, 12 Jan 2008 00:29:32 -0700
Message-ID: <009a01c854ec$e1067150$6401a8c0@BHAIRAVIPC01>
Vivek,
<<< Qs How should this issue be approached? >>> 1. Presuming an OLTP system, you would be committing when the transaction is done. Nevertheless you should verify that you are committing in a manner that models the business transaction and not based on a, perhaps, contrived example.
2. You are trying to deal with the aggregate with this report. What part of the response time is the log file sync (commit) time? Take one of the threads of this benchmark and run a level 8 SQL trace to get a more accurate picture of its influence.
3. I would look at whether you are IO bound or so CPU bound that the LGWR is not getting time. And determine the time log writer to is made to write vs its default timer and thresholds.
See
Cary Millsap's work in http://www.hotsos.com
Steve Adams' work in http://www.ixora.com.au/
Regards,
-Krish
Krish Hariharan
President/Executive Architect, Quasar Database Technologies, LLC
http://www.linkedin.com/in/quasardb
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of VIVEK_SHARMA
Sent: Friday, January 11, 2008 6:57 PM
To: oracle-l_at_freelists.org
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
~~~~~~~~~~~~~~~~~~ waitCall
Event Waits Time (s) (ms)Time
- ------------ ----------- ------
log file sync 3,511,274 328,288 9373.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 32.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***
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jan 12 2008 - 01:29:32 CST