This content has been marked as final. Show 9 replies
Waits on event "log file sync" while performing COMMIT and ROLLBACK operationsWhat storage type is assigned to redo log groups?
were consuming significant database time.
Do you have anything else shared on this disk?
I want to know what will be the best size of redo logs for my databaseThat purely depends on the amount of redo generated. 3-4 log switches per hour should be ok.
Thread 1 cannot allocate new log, sequence 4234Increase the number of redo log groups.
Before doing anything , I shall recommend reading this excellent page about Log buffer and its working,
In best practise advice, its recommended that a log switch should take a time lap of about 20 minutes. 10 minutes as suggested is probably a little small. Also you are using 10g I believe as you have mentioned ADDM.In EM of 10g,there is an option that you can get advice of the size which should be used by your redo log files. So you can use that.And why you are still using these deperecated parameters of log_chckpoint_timout and all that?Oracle has recommended to use FAST_START_MTTR_TARGET since 9i. I shall suggest that you should take care of this parameter. Also one more thing to conclude that look in your application that how the transactions are written.Too much commit rate can also cause Log File Sync wait event.Read teh page that I have given for the thorough discussion for it.
Thanks all. I am checking all the suggestion but still some questions.
Citrus, Many databases are sharing the same disks for redo log (because these disks are fast so I pointed all database's redo logs to these disks). I have only 3 fast disks and this database has 3 redo log groups (each has 1 member) and each redo log group assigned to disk1 , disk2 and disk3 respectively. Can I add the fourth redo log group to the 1st disk again ( even though 1st redo log group is on the same disk) ?
Aman, I was also reading the link you have mentioned . In the link it is mentioned that "It is very rare to require a log buffer more than a few hundred kilobytes in size" but when I checked the size of log_buffer in my database, it is 4MB, is it okay ? also I tried to find the advise for redo log buffer in Grid , I checked Advisor Central but no information for the redo log. I got the information for redo log in ADDM but not giving any recommendation
because these disks are fast so I pointed all database's redo logs to these disksAs these disks were fast, you added everything from everywhere to make them slow.
Why not spend some money and add few disks so that you may have separate disks for separate databases.
Can I add the fourth redo log group to the 1st disk again ( even though 1st redo log groupYes, you may create 4th group on the 1st disk, 5th group on the 2nd disk, and 6th group on the 3rd disk.....
is on the same disk) ?
Message was edited by:
There are a couple of oddities in the information:
1 - the average time per write was 1 m/s: this is pretty quick (and has to be a write to a cache, not to disc) so basically you can't make it significantly faster, and it's slightly odd that ADDM reported it, except that ...
2 - The average write size is 16Kb, which is quite small, and you get two 'cannot allocate next log' messages in the space of 28 seconds when your log file size is 256Mb; which means you seem to be doing lots of very small transactions very quicky. (Possibly, this is peak processing on a highly concurrent system, or it's a batch job processing and committing one row at a time - the latter should be investigated for reducing rate of commits).
You may get some relief from increasing the size of number of redo logs. You also need to look at the reasons why you are generating so much log in case the underlying code is inefficient.
It would also be helpful to give us a clue about where in the recommendations this one appeared - was it the top one, or just an "also-ran" somewhere near the bottom suggesting a couple of percent benefit ? It would be nice to see the Top 5 Timed events from the AWR report for the same period. (Check the FAQ at top-right to see how to use the 'pre' tags to make this appear in a readable fixed-font)
In 10gR2, by the way, you basically don't need to mess with the log buffer size - and you should take any reference to it out of the spfile (init.ora). It's controlled by Oracle, and the default is usually good enough.
One last thought - if you've set processes, sessions, or transactions to an unnecessarily high value, this could contribute to the complaint about 'private strand flush not complete'.
Thanks for your information.
The ADDM finding I found in Advisor Central then search for Advisory taks=ADDM. I have pasted the information below
Database Instance: ABC > Advisor Central > Automatic Database Diagnostic Monitor (ADDM) > Logged in As XYZ
Performance Finding Details
Database Time (minutes) 96.7
Period Start Time May 14, 2008 3:00:35 PM EDT
Period Duration (minutes) 60.2
Task Owner SYS
Task Name ADDM:3526010935_1_2887
Average Active Sessions 1.6
Finding Waits on event "log file sync" while performing COMMIT and ROLLBACK operations were consuming significant database time.
Impact (minutes) 2
Impact (%) 2
Action Investigate the possibility of improving the performance of I/O to the online redo log files.
Rationale The average size of writes to the online redo log files was 16 K and the average time per write was 1 milliseconds.
A few more point,
Wed May 14 14:31:00 2008The error basically means, you are switching logfile too fast, Oracle can't prep the previous logfile file group fast enough to be recycled. You might consider add few more redo logfile group. Or resize your redo logfile to bigger size, as you can see the log switch interval is merely 28 seconds.
Thread 1 cannot allocate new log, sequence 4233
Private strand flush not complete
Current log# 3 seq# 4232 mem# 0: /u04/oradata/abc/redo3.log
Thread 1 advanced to log sequence 4233
Current log# 2 seq# 4233 mem# 0: /u03/oradata/abc/redo2.log
Wed May 14 14:31:28 2008
Thread 1 cannot allocate new log, sequence 4234
Of course all these are related, if you increase redo logfile size Oracle will not switch log as often then it will have more time to flush the changes in older redo logfile groups.
As Jonathan has pointed out, the problem is most likely not because your redo files disk speed. More likely you had too many transactions at the same time, a code review should be considered. Check top SQL sections.
The impact is 2% - so I wouldn't get too worried about this.
Your 'average active sessions' is only 1.6, though, which points in the direction of my comment of a batch-like process committing every row. I would check for some pl/sql (or other language) loop inserting and updating a lot of data in small steps.
If you can extract the AWR report for the interval (in text form) and past the Top 5 and Load Profile sections, that might show more clues. Note - you can use tags (see the FAQ near the top right of the screen) to make things like AWR reports show up in fixed width fonts, that make them readable.