OraFAQ Forum: Performance Tuning » More archivelogs are generating.

Home » RDBMS Server » Performance Tuning » More archivelogs are generating. (Oracle 10.2.0.4, Windows 2003 server)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

More archivelogs are generating. [message #404377]

Thu, 21 May 2009 10:26

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.

Report message to a moderator

Re: More archivelogs are generating. [message #404383 is a reply to message #404377]

Thu, 21 May 2009 10:39

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: More archivelogs are generating. [message #404384 is a reply to message #404377]

Thu, 21 May 2009 10:40

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: More archivelogs are generating. [message #404391 is a reply to message #404377]

Thu, 21 May 2009 11:13

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.

Report message to a moderator

Re: More archivelogs are generating. [message #404593 is a reply to message #404391]

Fri, 22 May 2009 07:18

hristo
Messages: 258
Registered: May 2007

Senior Member

What kind of WAITS are you having in your DB?

Report message to a moderator

Re: More archivelogs are generating. [message #404669 is a reply to message #404593]

Fri, 22 May 2009 13:30

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??

Report message to a moderator

Re: More archivelogs are generating. [message #404672 is a reply to message #404669]

Fri, 22 May 2009 13:47

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Check ARCHIVE_LAG_TARGET parameter

Report message to a moderator

Re: More archivelogs are generating. [message #404674 is a reply to message #404669]

Fri, 22 May 2009 14:10

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: More archivelogs are generating. [message #404770 is a reply to message #404765]

Sat, 23 May 2009 09:49

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 13:34:59 CST 2024