Home » RDBMS Server » Performance Tuning » More archivelogs are generating. (Oracle 10.2.0.4, Windows 2003 server)
More archivelogs are generating. [message #404377] Thu, 21 May 2009 10:26 Go to next message
padhuoracle
Messages: 26
Registered: March 2009
Location: Chennai
Junior Member
Hi,

More number of archivelog files are generating in the database.
due to SAP Application client deletion job(it deletes 29000 tables) the undo_retention value is set to 216000(60 hours).

Recently I added 6 redo log group to the database(Totally 10 redolog groups).

Kindly suggest me.

If i reduce the undo_retention will it improve the performance.

Is there any way to reduce archive log generation?

padhu.
Re: More archivelogs are generating. [message #404383 is a reply to message #404377] Thu, 21 May 2009 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If i reduce the undo_retention will it improve the performance.

No.

Quote:
Is there any way to reduce archive log generation?

Use truncate.

Regards
Michel
Re: More archivelogs are generating. [message #404384 is a reply to message #404377] Thu, 21 May 2009 10:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>If i reduce the undo_retention will it improve the performance.
You will be calling for more issues related to undo .
UNDO_RETENTION is supposed to be set to the longest running transaction time.
>>Is there any way to reduce archive log generation?
There are.
But, it will not help with recovery and I suppose you are not allowed to change the SAP DML behavior.
If it is not a selective delete, can you truncate instead of delete?
Is this a recurring operation or one time job?

>>improve the performance
Are you sure that archived log generation is causing the performance drop?
Had you configured your database properly, archived log issues
should be minimal.
Make sure archived logs are in a different set of disks/spindles.
Create larger redolog files.
Re: More archivelogs are generating. [message #404391 is a reply to message #404377] Thu, 21 May 2009 11:13 Go to previous messageGo to next message
padhuoracle
Messages: 26
Registered: March 2009
Location: Chennai
Junior Member
Hi,

Currently there are 10 redolog group with members 100mb size each.

It is running from SAP, i couldnot see the script which is running. they are using delete command only not the truncate.

padhu.
Re: More archivelogs are generating. [message #404593 is a reply to message #404391] Fri, 22 May 2009 07:18 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
What kind of WAITS are you having in your DB?
Re: More archivelogs are generating. [message #404669 is a reply to message #404593] Fri, 22 May 2009 13:30 Go to previous messageGo to next message
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
Junior Member

Increase the redo log size. may be to 300MB and check.

And if you have more transactions , u will have more redo ... and so on more archive will be generated. dont woory abt that. This is not a problem of generating more redo.As you cannot use truncate so go with it..

Check the filesystems of your redo/archive files. are you using raid 5 for this??
Re: More archivelogs are generating. [message #404672 is a reply to message #404669] Fri, 22 May 2009 13:47 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Check ARCHIVE_LAG_TARGET parameter
Re: More archivelogs are generating. [message #404674 is a reply to message #404669] Fri, 22 May 2009 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Increase the redo log size. may be to 300MB and check.

Why? And why 300M? Are you shooting in the dark? Or is there any rationale behind that?

And DON'T use IM speak.

Regards
Michel

[Updated on: Fri, 22 May 2009 14:11]

Report message to a moderator

Re: More archivelogs are generating. [message #404765 is a reply to message #404674] Sat, 23 May 2009 08:56 Go to previous messageGo to next message
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
Junior Member

Yes, my intention was to delay the log switch and archiving process a bit, as it is going for more archive ..i hope justification is exceotable.

and wht is this IM , wht i am not suppose to use
Re: More archivelogs are generating. [message #404770 is a reply to message #404765] Sat, 23 May 2009 09:49 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
IM is Instant Messaging: http://en.wikipedia.org/wiki/Instant_messaging, also called SMS (Short Message Service) speak: http://en.wikipedia.org/wiki/SMS.

"u", "abt", "wht"... are IM. Use whole words, this is not a social forum.

Regards
Michel

[Updated on: Sat, 23 May 2009 09:49]

Report message to a moderator

Previous Topic: Query tuning
Next Topic: Oracle 9i Cost very high
Goto Forum:
  


Current Time: Sat Dec 03 01:23:26 CST 2016

Total time taken to generate the page: 0.10366 seconds