| Redo Log Tuning [message #375806] |
Sun, 14 December 2008 22:52  |
rumman
Messages: 48
Registered: June 2007
Location: Bangladesh
|
Member |
|
|
Database in NoArchivelog mode,
v$LOG:
GROUP# BYTES MEMBERS
1 786432000.00 2
2 786432000.00 2
3 786432000.00 2
V$LOGFILE:
GROUP# MEMBER
1 /redolog1/pblprod/redo01.log
1 /redolog2/pblprod/redo01b.log
2 /redolog2/pblprod/redo02b.log
2 /redolog1/pblprod/redo02.log
3 /redolog2/pblprod/redo03b.log
3 /redolog1/pblprod/redo03.log
V$PARAMETER:
Name Value
log_buffer 268423168
sga_target 12499025920
V$SYSSTAT:
NAME VALUE
----------------------------- --------------
redo synch writes 11860294.00
redo synch time 3175958.00
redo blocks read for recovery 0.00
redo entries 111282304.00
redo size 94880055448.00
redo buffer allocation retrie 66919.00
redo wastage 2740770600.00
redo writer latching time 2041.00
redo writes 9628181.00
redo blocks written 196906871.00
redo write time 1218498.00
redo log space requests 69034.00 <<<<<
redo log space wait time 119936.00 <<<<<
redo log switch interrupts 0.00
redo ordering marks 2137306.00
redo subscn max counts 2982189.00
At this state, I found 2 redo groups in ACTIVE state and 1 in CURRENT state and hence wait for redo allocation during high DML in batch processing.
I reszie the Redo Log files to 1GB each and also add a new redo group and then I found a significant improvement in the system and no more crashes during my End-of-Day operation where high DMLs are occured.
At present, V$SYSSTAT:
NAME VALUE
redo synch writes 10884584
redo synch time 2651862
redo blocks read for recovery 0
redo entries 100416708
redo size 84530098088
redo buffer allocation retries 1312
redo wastage 2502277888
redo writer latching time 1667
redo writes 8996301
redo blocks written 175553454
redo write time 1088910
redo log space requests 1772 <<<<<
redo log space wait time 11125 <<<<<
redo log switch interrupts 0
redo ordering marks 1848916
redo subscn max counts 2552844
'redo log space requests' has not been nearly 0 yet.
However, I find in alert file:
-------------------------------------------------------------------
Mon Dec 15 09:32:49 2008
Thread 1 advanced to log sequence 75082
Current log# 1 seq# 75082 mem# 0: /redolog1/pblprod/redo01.log
Current log# 1 seq# 75082 mem# 1: /redolog2/pblprod/redo01b.log
Mon Dec 15 10:01:02 2008
Thread 1 advanced to log sequence 75083
Current log# 2 seq# 75083 mem# 0: /redolog1/pblprod/redo02.log
Current log# 2 seq# 75083 mem# 1: /redolog2/pblprod/redo02b.log
----------------------------------------------------------------
However, DBA_ADVSOR_FINDING shows
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations were consuming significant database time.
In this circumstances, what should be my next step?
|
|
|
|
|
|
| Re: Redo Log Tuning [message #375833 is a reply to message #375816] |
Mon, 15 December 2008 02:00  |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
Can you put your redo logs on separate or faster disks?
Also check who is using excessive redo and tune those processes. For example, use NOLOGGING if you can, commit in batches, use COMMIT with the NOWAIT option, etc.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
