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

  • From: VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, "kevinc@xxxxxxxxxxxxx" <kevinc@xxxxxxxxxxxxx>, "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "krish.hariharan@xxxxxxxxxxxx" <krish.hariharan@xxxxxxxxxxxx>, "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "jkstill@xxxxxxxxx" <jkstill@xxxxxxxxx>, "greg@xxxxxxxxxxxxxxxxxx" <greg@xxxxxxxxxxxxxxxxxx>, "tim@xxxxxxxxx" <tim@xxxxxxxxx>, "tanel.poder.003@xxxxxxx" <tanel.poder.003@xxxxxxx>
  • Date: Sun, 13 Jan 2008 10:31:38 +0530

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***
--
//www.freelists.org/webpage/oracle-l

Other related posts:

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