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

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Sun, 13 Jan 2008 10:31:38 +0530
Message-ID: <69E1360E54B50C4A828A136C158E4742036A707678@BLRKECMBX02.ad.infosys.com>

Folks

During a Benchmark Run of OLTP Transactions very High "log file sync" wait is occuring (Statspack info below)

Top 5 Timed Events                                                 Avg    %Total

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

Event                                     Waits        Time (s)    (ms)   Time

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

log file sync                             2,208,357    164,021     74      59.4



CPU usage on DB Server is only 50 % - %sys = 30 %, % usr = 20 %

Thus CPUs are not Choking & hopefully not cauing "log file sync wait"

To identify if the cause of "log file sync" is an IO or CPU bottleneck, truss command was issued on LGWR to find the O.S. function call taking the Longest Time

$ truss -fdD -rall -wall -o truss_lgwr1.log -p <PID of ora_lgwr_$SID>
Shows the Longest wait on the following 2 Function Calls:-

12549/13:        3.2242  3.1328 kaio(AIONOTIFY, 0)                              = 0
12549/1:         1.6162  1.4777 kaio(AIOWAIT, 0xFFFFFFFF7FFFD860)               = 1

Qs Does this output mean that it is an KAIO issue?What corrective action is advisable? NOTE - In init$SID.ora, disk_async_io=TRUE # adb -k
physmem 2584cbf
maxphys/D
maxphys:
maxphys: 131072

vol_maxio/D
vol_maxio:
vol_maxio:      2048

$q

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 to set e.g. _log_parallelism=4, _log_simultaneous_copies=256? Qs Should LGWR process be set to a Higher CPU priority(using renice) since 60% of the CPU power is FREE/Unused?

Will share Statspack, truss Outputs as needed

Cheers & Thanks

P.S.

NOTE - cpu_count=80

Storage Box - Sun StoreEdge 9990V (Hitachi SAN)

Online Redo logfiles exist separately on a Mounted Filesystem with an underlying LUN / Volume of 4+4 Hard Disks , (RAID 1+0 Type) Storage Cache - 160 GB
Online Redo Logfile size 750 MB
log_buffer= 5M

Oracle 10.2.0.3
Solaris 10

  • 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
Received on Sat Jan 12 2008 - 23:01:38 CST

Original text of this message