RE: performance impact of archivelog

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Mon, 17 Nov 2008 10:40:55 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F083DD375@AAPQMAILBX02V.proque.st>

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_at_proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Claudia Zeiler Sent: Monday, November 17, 2008 10:21 AM To: Renato M; david.h.roberts_at_logica.com Cc: oracle-l_at_freelists.org
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_at_gmail.com] Sent: Monday, November 17, 2008 7:18 AM
To: david.h.roberts_at_logica.com
Cc: Claudia Zeiler; oracle-l_at_freelists.org 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_at_logica.com<mailto:david.h.roberts_at_logica.com>> 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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Claudia Zeiler Sent: 15 November 2008 21:27
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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

--

http://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.

--

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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Nov 17 2008 - 09:40:55 CST