Re: SAP Reorgs

From: Tim Gorman <tim_at_sagelogix.com>
Date: Fri, 18 Jun 2004 12:11:58 -0600
Message-ID: <BCF88C8E.16CF0%tim@sagelogix.com>

I¹m kind of confused about why there is any discussion about this topic... Reorgs are bad, bad, bad. You need downtime to accomplish them, else a non-trivial amount of preparation and cleverness (or an expensive 3rd-party product) to avoid that downtime. As someone remarked earlier in this thread, we wouldn¹t be having a discussion on reorgs if they were painless. But they are painful (by varying degrees), so it is prudent to quantify the benefits before expending the effort and incurring the risk, right? They certainly do not fall into the realm of ³best practice², I think we can agree?

So, while quantifying the benefits, I¹ve found that using reorgs generally to enhance performance and save storage is much like using liposuction as a general method of weight loss. I can see (maybe!) doing liposuction once in a lifetime for a small number of problems, but one would expect the patient to learn the necessary lessons and prevent a repeat. I can¹t imagine a reputable doctor recommending yearly liposuctions.

Of course, if regular liposuction surgery can be justified, then I¹m sure similar justifications can be posed for regular reorgs... :-)

on 6/15/04 4:03 PM, Jared.Still_at_radisys.com at Jared.Still_at_radisys.com wrote:

>> > To my opinion reorganizing is OK when , as a result of it, a table or index
>> > occupies less data blocks.
>> > This will, in general, not only cause less LIO for this segment.

>
> Rebuilding a B*Tree index to conserve space can have detrimental effects on
> performance.
>
> If the index sees a lot of insert activity, your newly rebuilt index will
> undergo block splits, and
> soon be back to the size it was previously.
>
> FFS and Range Scans may benefit from a rebuild, but it would probably be best
> to
> quantify the benefit.
>
> This goes for tables too, dependent on whether or not a table sees many FTS,
> and the
> access patterns. If straight OLTP, rebuilding to save space may not buy much
> performance.
> It may take less space in the block buffers, but then again, previously cool
> blocks may
> become hot.
>
> There are no silver bullets.
>
> See Richard Foote's paper on index internals, it is very informative.
>
> I'm sure he will correct me if I have mis-spoken on any of this. ;)
>
> Jared
>
>
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jun 18 2004 - 14:16:07 CDT