RE: performance impact of archivelog

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "czeiler@xxxxxxxxxx" <czeiler@xxxxxxxxxx>, Renato M <renatomelogomes@xxxxxxxxx>, "david.h.roberts@xxxxxxxxxx" <david.h.roberts@xxxxxxxxxx>
  • Date: Mon, 17 Nov 2008 10:40:55 -0500

It seems to me, the decision as to whether or not archivelog mode is required 
is a business decision.  Factors that drive that decision are:
1.)  Can downtime be tolerated for taking backups?  No=must use archivelog mode
2.)  Do you want to be able to do point in time recoveries and incomplete 
recoveries?  Yes=must use archivelog mode.

And there are probably other questions to ask, but those are two big ones.  
Now, assuming the business decision has been made that archivelog mode is 
required, the question becomes, "How do I create as small an impact on my 
running production database as possible, when I implement archivelog mode?"

This answer is purely technical, as opposed to the previous questions, which 
answered business requirement questions.  Here, the first thing to look at is, 
do you have enough I/O capacity to support the extra reads/writes to support 
logging of archives?  Next, what about CPU capacity (probably not a large 
requirement, but, if you're already on the edge in terms of CPU and/or memory 
capacity, it could be a problem.)

Next, how often do you see a log switch?  How large are your log files 
currently?  I like to target a log switch every 20 minutes or so, at peak load. 
 So, size your archive logs appropriately.

In a reasonably healthy production system, where you're not already maxing out 
I/O, CPU, or memory, I would not expect that enabling archivelog mode would 
cause any performance degradation or problems.  If your system is already 
living on the edge, in terms of capacity, you should probably proceed w/ 
caution.

Ultimately, in my view, all production databases should be in archivelog mode.  
End of story.  If your system lacks the capacity to support archivelog mode, 
it's time to buy a bigger system.  Archivelog mode is NOT an option for a 
production system.

HTH,

-Mark

--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxx
www.proquest.com
www.csa.com

ProQuest...Start here.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Claudia Zeiler
Sent: Monday, November 17, 2008 10:21 AM
To: Renato M; david.h.roberts@xxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: performance impact of archivelog

Renato,
So far I have no more info.

In my thinking, 180G of redo log generation is already the strain on the 
system, and 180g of archivelog generation is just a background cleanup process 
that shouldn't affect the database.  But I don't know facts at all.  Maybe my 
assumption will cause someone to tell us that I am totally wrong, and "here's 
why.".  That would be a start in trying to figure this out.   I suspect that 
the answer involves a table with a name that starts like 'k$xz...'.

Please let me know if you learn anything.  I have the same issue as you.
Thanks,
Claudia Zeiler

________________________________________
From: Renato M [renatomelogomes@xxxxxxxxx]
Sent: Monday, November 17, 2008 7:18 AM
To: david.h.roberts@xxxxxxxxxx
Cc: Claudia Zeiler; oracle-l@xxxxxxxxxxxxx
Subject: Re: performance impact of archivelog

Hi, list.

I'm with the same doubt...
We have a Data Warehouse database in "no archivelog" mode with 180GB/day of 
redo generation.

because of a new backup solution/ strategie, we need put it in "archivelog ON".

Claudia,
Have you any other information about it ?

Thanks a lot!

Renato Gomes
Eds, an HP Company.



On Mon, Nov 17, 2008 at 9:09 AM, Roberts, David (GSD - UK) 
<david.h.roberts@xxxxxxxxxx<mailto:david.h.roberts@xxxxxxxxxx>> wrote:
1. Performance cost of running in archivelog mode.

We take our database out of archive log mode for a yearly batch process.
Very roughly the dry run (which is run on a different but similarly
specked machine in archive log mode) takes about 20% longer.

Obviously as the difference is going to be dependent on how much change
is going on, your mileage may vary.


David Roberts
www.logica.com<http://www.logica.com>

Logica UK Limited
Registered in England and Wales (registered number 947968)
Registered office: Stephenson House, 75 Hampstead Road, London NW1 2PL,
United Kingdom

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On 
Behalf Of Claudia Zeiler
Sent: 15 November 2008 21:27
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: performance impact of archivelog

Has anyone on the list seen a discussion of

1. performance cost of running in archivelog mode.
2. overhead of using partitions
3. time calculation to perform statistics
4. time savings of using statistics

I always see these things discussed in terms of "more"   but never in
terms of "how much".

Thanks for any information that you can point me to.


Claudia Zeiler

--
//www.freelists.org/webpage/oracle-l




This e-mail and any attachment is for authorised use by the intended 
recipient(s) only. It may contain proprietary material, confidential 
information and/or be subject to legal privilege. It should not be copied, 
disclosed to, retained or used by, any other party. If you are not an intended 
recipient then please promptly delete this e-mail and any attachment and all 
copies and inform the sender. Thank you.


--
//www.freelists.org/webpage/oracle-l



--
//www.freelists.org/webpage/oracle-l




--
//www.freelists.org/webpage/oracle-l


Other related posts: