Re: SAP Reorgs
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Jun 2004 12:11:58 -0600
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@xxxxxxxxxxx at Jared.Still@xxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
