High log_file_sync

SAP: 4.6C 46D_EXT with latest Kernel

DB: Oracle 10.2.0.2

OS: AIX 5.3

Storage: IBM SAN

I noticed that our system has pretty high log_file sync which is around 50, way above SAP Recommendation <=15. This causes high COMMIT_WRITE in the database.

I understand this is highly to do with SAN, I wonder know beside contacting

hardware vendor on investigate, what can a SAP admin do to explore further on the cause of this?

These are the values in $SYSTEM_EVENT:

Event Total waits Total timeouts Wait time (ms) Avg. wait. (ms)

SQL*Net message from client 629,299,791 0 46,104,535,290 73

rdbms ipc message 13,498,662 6,950,496 22,704,841,860 1,682

Streams AQ: qmn slave idle wai 82,858 2,134 2,265,226,690 27,338

pmon timer 736,534 736,400 2,134,577,780 2,898

Streams AQ: waiting for messag 436,951 436,540 2,131,346,790 4,877

Streams AQ: qmn coordinator id 162,157 87,446 2,120,692,600 13,078

smon timer 9,027 6,709 2,060,610,010 228,271

Streams AQ: waiting for time m 5,462 4,217 1,322,119,300 242,057

db file sequential read 393,004,651 0 1,103,249,290 2

log file sync 6,222,726 286,093 326,571,050 52

log file parallel write 6,564,240 0 145,054,940 22

db file parallel write 14,763,979 0 37,571,620 2

control file parallel write 934,590 0 36,573,670 39

latch: library cache 73,832 0 14,490,360 196

control file sequential read 2,317,337 5 13,509,330 5

enq: TX - row lock contention 8,265 3,600 11,911,250 1,441

db file scattered read 746,387 0 9,356,500 12

Streams AQ: qmn coordinator wa 1,634 1,622 7,913,020 4,842

log file sequential read 377,576 0 7,478,430 19

latch: shared pool 39,318 0 6,936,110 176

SQL*Net more data to client 173,218,270 0 4,688,130 0

log buffer space 4,706 4,308 4,394,850 933

Log archive I/O 434,445 0 3,895,450 9

read by other session 1,684,603 632 3,099,910 1

log file switch completion 7,402 1,512 1,877,860 253

jobq slave wait 460 460 1,347,680 2,929

SQL*Net break/reset to client 18,770,680 0 1,301,640 0

latch: cache buffers lru chain 4,850 0 882,470 182

SQL*Net message to client 629,299,909 0 758,530 0

buffer busy waits 174,354 555 655,810 3

latch: cache buffers chains 29,672 0 650,110 21

enq: CF - contention 284 169 599,590 2,111

os thread startup 3,425 431 566,140 165

SQL*Net more data from client 13,902,062 0 512,310 0

latch free 6,981 0 276,980 39

cursor: pin S wait on X 24,540 24,494 245,580 10

LGWR wait for redo copy 133,525 22,291 233,320 1

latch: library cache lock 1,173 0 205,810 175

latch: redo allocation 2,628 0 189,780 72

latch: enqueue hash chains 665 0 160,260 241

rdbms ipc reply 7,407 59 134,230 18

enq: TX - index contention 1,429 20 105,650 73

latch: redo writing 415 0 92,580 223

switch logfile command 47 16 90,860 1,933

direct path read 60,092 0 84,800 1

latch: object queue header ope 1,128 0 78,630 69

db file single write 4,440 0 73,630 16

latch: row cache objects 813 0 62,300 76

db file parallel read 2,823 0 61,030 21

Regards,

Annie