Home » RDBMS Server » Performance Tuning » Redo Log Tuning (Oracle 10.2.0.3)
Redo Log Tuning [message #375806] Sun, 14 December 2008 22:52 Go to next message
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 #375816 is a reply to message #375806] Mon, 15 December 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
From your previous posts:
Mohammad Taj wrote on Tue, 04 December 2007 14:39
Please read http://www.orafaq.com/forum/t/88153/0/

Michel Cadot wrote on Tue, 04 December 2007 12:45
Once again read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel


Michel Cadot wrote on Tue, 04 December 2007 12:37
Format your post! This is unreadable and so useless.

Regards
Michel


Re: Redo Log Tuning [message #375833 is a reply to message #375816] Mon, 15 December 2008 02:00 Go to previous message
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.
Previous Topic: How SELECT affects remote database from which data is selected
Next Topic: Simple query taking too long - looking for Index Suggestion
Goto Forum:
  


Current Time: Thu Dec 05 12:31:26 CST 2024