cancel
Showing results for 
Search instead for 
Did you mean: 

Database Key Performance Indicators --> log file sync av. wait time for a c

Former Member
0 Kudos

hello Gurus,

I just took a lock in last Early Watch session and found that in section Database Key Performance Indicators the Performance indicator Log file sync (av. wait time for a commit) is 45 when it should be < 40.

Database Key Performance Indicators:

Performance Indicator --> log file sync

Description --> av. wait time for a commit

Observed Value --> 45

Reference Value --> < 40

High Average Response Time per Commit

The commit time of the processes in your system is too high. The system has to wait for the redo entry to be flushed to disk, which increases the database response time of your system. See 'log file sync' in the table above.

How we can fix it? or What can we check?

Thanks,

HEPC

Edited by: Hernando Polania Cadena on Aug 30, 2010 11:25 PM

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Usually, the commit performance is directly linked with the speed of your disks. So you should check your disk performance. Obviously if you have more than one redo log members per group this also impacts commit speed, especially when they are not located on separate disks.

Besides that, it is possible that your system commits to frequently (for example every single row during a batch run), or you have a huge amount of redo on your system (something like > 20mb/s). But you should focus on the disk speed first.

Cheers Michael

PS: depending on your platform, the filesystem options (like blocksize, caching) can greatly influence the disk performance

Former Member
0 Kudos

Hello Cheers,

Our system is Linunx RH and Oracle 10.2.0.4.

The redo logs on the DB are:

SQL> select GROUP#,MEMBERS, STATUS,BYTES from V$LOG;

GROUP# MEMBERS STATUS BYTES

-


-


-


-


1 2 INACTIVE 52428800

2 2 CURRENT 52428800

3 2 INACTIVE 52428800

4 2 INACTIVE 52428800

Thanks,

HEPC

Edited by: Hernando Polania Cadena on Aug 31, 2010 7:03 PM

Edited by: Hernando Polania Cadena on Aug 31, 2010 7:10 PM

Former Member
0 Kudos

At the moment you are only investigating the issue because of the early watch alert, right? So, as long as you don't have a real performance problem you are probably completely ok.

I can see you have 4 log groups with two members each with 50mb size. This is the default after installation. Are both members located on the same disk, or do you have a storage system with raid attached to the server?

It might be interesting to see the actual values on your system. You can see them in DBACOCKPIT -> Performance -> Wait Event Analysis -> System Event

Event                                                       Wait time % non-idle % tot.resp        Waits Timeouts Avg.WT ms                                                                                
Total                                                  1,051,940,149     100.00     100.00  507,330,923   92,274         2
db file sequential read                                  834,915,648      87.95      79.37  152,987,980        0         5
CPU used by this session                                 102,629,540       0.00       9.76            0        0         0
log file sync                                             68,413,505       7.21       6.50    6,754,212    5,471        10

In my example (SLES 10) the avg. wait is 10ms and we are spending 7.21% overall on waiting for "log file sync"

Cheers Michael

Former Member
0 Kudos

Hello Cheers,

I'm insterested in the issue because we have performance problems and the early watch alert. I'm new with this system and I've changed some oracle paramerters to improve the performance.

the members locations are:

SQL> select GROUP#,MEMBER from V$LOGFILE;

GROUP# MEMBER

1 /oracle/PRA/origlogA/log_g11m1.dbf

1 /oracle/PRA/mirrlogA/log_g11m2.dbf

2 /oracle/PRA/origlogB/log_g12m1.dbf

2 /oracle/PRA/mirrlogB/log_g12m2.dbf

3 /oracle/PRA/origlogA/log_g13m1.dbf

3 /oracle/PRA/mirrlogA/log_g13m2.dbf

4 /oracle/PRA/origlogB/log_g14m1.dbf

4 /oracle/PRA/mirrlogB/log_g14m2.dbf

The result of Tx DBACOCKPIT -> Performance -> Wait Event Analysis -> System Event:

[http://i54.tinypic.com/3304msx.png]

Thanks for your help.

HEPC

Edited by: Hernando Polania Cadena on Sep 1, 2010 4:19 PM

Former Member
0 Kudos

Ok, if you have real performance problems, and after studying your wait events i recommend definitely check your disk performance. You probably need to get in contact with your OS admins and/or hardware partner.

Besides that you should not primarily focus on the log sync time just because it is mentionend in the EWA alert. You don't seem to spend a significant time on log file sync (only 8.68% of the database time). You are spending much more on full table scans (scattered read with 19%).

You should also ask what actions are slow and then analyse what is happening on the system and what is taking the most time. Let me give you an example: if a user complains that a certain transaction is very slow and you can see in ST03 that most of the time is in fact database time for this transaction. Then do an SQL trace and look for the longest running SQL's. Depending on that you decide on your tuning steps.

As starter you might have a look at note [766349 - FAQ: Oracle SQL optimization|https://service.sap.com/sap/support/notes/766349]

There is quite a bunch of very good information in this and the attached notes there.

Cheers Michael

Former Member
0 Kudos

Hello Cheers,

Thanks for your advices, I'll contact my OS and HW partner to verify the Hard disk performance, this is something that worried me and in the EWA report is also referenced. I checked some transactions that take the most time and followed some SAP Notes to improve the performance in those transactions, the performance improved but still I need to improve.

By the moment, I'll leave the message open and I'll comment you about the checks on the disks performance.

HEPC