RE: performance impact of archivelog

I haven't seen a thread on items you listed nor measured them in a scientific manor myself. With that being said, in my experience the decision to use each one of those features is usually a no brainer, either you must use them or you don't need them; Rarely is there grey ground when it comes to using archive logging, partitioning and stats gathering.

1. As far as I am concerned performance with archive log mode is the baseline and not the other way around since I cannot live without hot backups, point in time recovery, block recovery etc.
2. Partitioning has overhead associated with it, but when used properly it improves performance so dramatically that the overhead is not even noticed (when used improperly performance suffers to such a degree the overhead doesn't matter either). I have seen up to 5 orders of magnitude reduction in resource utilization and up to 3 orders of magnitude reduction in elapsed time when partitioning is needed and implemented.
3. Gathering stats is such an implementation specific item, it can run from no time to seconds to hours, or even days. If most of your data is relatively static or not growing very quickly you could possibly go without stats collection for months or years, if growth, change, and skew is very high than weekly, daily etc. stats gathering for some objects maybe necessary or anywhere in between.
4. "Good" stats can take queries that will never complete and bring them down to milliseconds for a near infinite improvement with the converse being true as well.

Ken

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

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Claudia Zeiler
Sent: Saturday, November 15, 2008 4:27 PM To: 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

--

http://www.freelists.org/webpage/oracle-l Received on Sun Nov 16 2008 - 00:40:53 CST