-
Redo log configuration
We have been experiencing a lot of log sync waits so I am would like to try to move some re-do logs around in hopes of minimizing the waits.
Below is my current redo log configuration. We have added a new file system.
What would be the best way to spread out the redo-logs to minimize contention. I don’t necessarily need to have 6 groups with 2 members
in each group. I can change to any configuration I like.
If you recommend a configuration, can you please explain why this would
be a benefit
An example in the following format would be great....
logfile group1 (full path..
full path...)
logfile group2 (full path...
full path...)
This is a datamart system, and I would say on the avarage we switch logs
about every 12 minutes. Right now I dont want to change the size of
the files as I want to do one fix at a time to see what actually helps
1 /u05/oradata/redo_01a.rdo 500MB
1 /u06/oradata/redo_01b.rdo 500MB
2 /u06/oradata/redo_02a.rdo 500MB
2 /u07/oradata/redo_02b.rdo 500MB
3 /u05/oradata/redo_03a.rdo 500MB
3 /u07/oradata/redo_03b.rdo 500MB
4 /u05/oradata/redo_04a.rdo 500MB
4 /u06/oradata/redo_04b.rdo 500MB
5 /u06/oradata/redo_05a.rdo 500MB
5 /u07/oradata/redo_05b.rdo 500MB
6 /u05/oradata/redo_06a.rdo 500MB
6 /u07/oradata/redo_06b.rdo 500MB
Thanks to all who answer
-
You can have whatever setup you want and that will have nothing to do with this wait event. What causes this event?
1. Too many commits or short transactions - which have nothing to do with your setup.
2. Slow IO subsystem - also related to log file parallel write waits, which have nothing to do with your setup.
3. Oversized log buffer, which you may begin to guess by now, has nothing to do with your setup.
Plus, you can do research on your own.
WAITEVENT: "log file sync" Reference Note (Doc ID 34592.1)
Last edited by stecal; 04-20-2011 at 06:36 PM.
-
 Originally Posted by BeefStu
We have been experiencing a lot of log sync waits...
Would you mind in defining "a lot" in this particular scenario?
Is this the top wait event in your system? Would you mind in posting the five top wait events in your system?
Sharing Oracle version, RAC/non-RAC and O/S wouldn't hurt either.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Guys,
Thanks for your help. This is from an AWR report. The main problem I have
is that we are using a 3rd party tool for replication, which feeds into this
database. In order for the tool to ensure consistency, there are a lot of
short transactions, with commits, which I can't do anything about.
I was hoping that by spreading the redo logs across diff file systems and
possibly making the redo logs larger this may help.
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
log file sync 46,619,131 83,354 2 36.2 Commit
CPU time 78,722 34.2
db file sequential read 12,539,878 67,233 5 29.2 User I/O
log file parallel write 41,334,339 35,540 1 15.4 System I/O
direct path read 8,712,612 28,092 3 12.2 User I/O
-
You can see in the top 5 where log file sync is commit related. Along with that though, is the log file parallel write - and this is something you can adjust - place redo log files on fast spindles/drives/partitions.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|