High log_file_sync

[Former Member]

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

[stefan_koehler · Accepted solution]

Hello Annie,

i have never thought, that i will need my blog about this topic

But here it is: /people/stefan.koehler/blog/2008/11/30/oracle-tuning-log-file-syncs-on-aix-with-diocio

I have investigated this topic and post the results in my blog - please check it and if you have questions post them here.

@ Markus:

In this case the mount options are not important.

If the filesystem is JFS2 and FILESYSTEMIO_OPTIONS is set to SETALL, oracle access the files with CIO, whatever the mount option is. The mount option is only important if you use some third party backup tools, that are accessing the files not through oracle (for example TDP for mySAP, etc.).

Regards

Stefan

[Former Member · Accepted solution]

Thanks Stefan! 😃 I will take a detail look at your blog and come back if I have any findings and questions.

Cheers,

Annie

[Former Member · Accepted solution]

Hi Stefan,

Your blog has exactly describe the situation I am facing! I am seeing demoted I/O in our systems. I would now need to run the test scenario to capture the before and after result, do you think you can share with me the PL/SQL script you have wriiten? And, how exactly can I run AWR snapshot in sqlplus level?

Thanks a lot! It's a great article!

[stefan_koehler · Accepted solution]

Hello Annie,

of course i can send you the PL/SQL scripts.

Please write me a mail (you will find my address in the profile) and i will send you the scripts.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Thanks for your great solution. I implemented the change two weeks ago and the result is fantastic!

Our production log file sync drops from 52 to 1! I certainly hope SAP can put your solution in their OSS note database for more people to benefit from it.

Once again, thanks for your great help! =D

Cheers,

Annie

[markus_doehr2]

You check if you mounted the harddisk with the right options:

Note 948294 - AIX JFS2: mount options to use with Oracle 10g

Markus

[Former Member]

Hi Markus,

I have mounted correctly as according to Note 948294 - AIX JFS2: mount options to use with Oracle 10g. In fact, if you can remember, this post is related to the Archivelog Backup Performance that I posted last Dec. I did what you advised and the performance does improve from the OS level. Less CPU utilization and less I/O. However, there is no change in log file sync area. Hence I posted this message.

Regards,

Annie