performance-archivelog

Shall archive log enable affect the performance of the database..
Suppose if I am not enabling the archive log..means greater performance..
nobleitAsked:
Who is Participating?
 
johanntagleCommented:
Yes there will be always be a performance difference impact - the more you ask your database to do, the more load it has to manage.  HOWEVER, to quote Tom Kyte in his book Expert Oracle Database Architecture:

"Say you take a backup once a week on Saturday.  Now on a Friday afternoonm after you have generated hundreds of redo logs over the weekm your hard disk fails.  If you have not been running in ARCHIVELOG mode, the only choices you have right now are as follows:
 - Drop the tablespace(s) associated with the failed disk.  Any tablespace that had a file on that disk must be dropped, including the contents of that tablespace.  If the SYSTEM tablespace is affected, you cannot do this.
 - Restore last Saturday's data and lose all of the work you did that week.

....A database that is not in ARCHIVELOG mode will, some day, lose data.  It is inevitable; you will lose data if your database is not in ARCHIVELOG mode.....
.... Only a test or development system should execute in NOARCHIVELOG mode.  Don't let anyone talk you out of being in ARCHIVELOG mode.  You spent a long time developing your application, so you want people to trust it.  Losing their data will not instill confidence in your system."

Clearly, the performance impact of keeping ARCHIVELOG mode on is well worth it.
0
 
slightwv (䄆 Netminder) Commented:
There is a slight performance hit on disks when 'archiving' the last online redo log.  There is the 'read' of the last one and the 'write' of the archived one.

However this is normally worth it for the benefit of point-in-time recovery.

I suggest you spend some time in the docs to see how online redo logs are used and what archiving them does.
0
 
gvsbnarayanaCommented:
The performance penalty can be easily mitigated by placing the redo and archivelog area on different disks. More than performance, recoverability of my data till any media failure and this can not be sacrificed for performance.
HTH
Regards,
Badri
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
Agreed but there is still a small hit while reading the last online redo log while writing to the current one.

Not much but in some systems you need to be aware of it.
0
 
slightwv (䄆 Netminder) Commented:
>>Clearly, the performance impact of keeping ARCHIVELOG mode on is well worth it.

Thanks for all that but it deals mainly with recovery not the question about performance between archive and no archive log modes.

The cost of 'recovery' and an effective disaster recovery plan is a different question.

0
 
johanntagleCommented:
The cost of 'recovery' and an effective disaster recovery plan is a different question.

Agree, but I said that because you shouldn't make a decision on turning off ARCHIVELOG based on performance alone.  nobleit has to know what he is getting into if he turns it off.
0
 
slightwv (䄆 Netminder) Commented:
Agreed which is why I added "However this is normally worth it for the benefit of point-in-time recovery" to my first post.
0
 
johanntagleCommented:
Yup I just wanted to stress the point =)
0
 
slightwv (䄆 Netminder) Commented:
When confirming a previous post you might want to add an acknowledgment of the post.  That way we don't take it as an attempted point poaching tactic.
0
 
nobleitAuthor Commented:
Which has more priority as a dba?
for sure it is data.... I understand that ..
0
 
johanntagleCommented:
Yes sorry missed that, though I'm never really conscious about point accumulation except when I want to get my 3,000 quota for the month to keep my account going.  Most of the time I answer because I can =)
0
 
johanntagleCommented:
nobleit: I think data integrity is your number one key result area.  Performance would be among the top, but data integrity is tops.
0
 
johanntagleCommented:
Nobleit: Thanks but next time it would be better if you give credit to others who helped by accepting multiple solutions and spreading the points around.  Before I joined in slightwv and badri already gave you a fairly good answer that you need to take into consideration data integrity, not just performance.  I just expounded and stressed on it.
0
 
slightwv (䄆 Netminder) Commented:
0
 
johanntagleCommented:
I propose that the points be spread to slightwv, gvsbnarayana, and me.  The first two answered the question adequately, though I guess it took my expounding on the points made to really make it clear that turning off ARCHIVELOG is a bad idea.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.