Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SAP Reorgs

RE: SAP Reorgs

From: Mercadante, Thomas F <>
Date: Fri, 18 Jun 2004 15:39:11 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5C48@exchsen0a1ma>


Reorgs do have a purpose. But just not as a regular part of the = business
cycle. I perform reorgs on six tables about every quarter. Why? = Because
they are constantly having records inserted and deleted every day. And = my
version of Oracle (8.1.7) does not do a good job of reusing the deleted space - I've seen the data files just growing and growing. So I = perform
"alter table move" commands to alternate tablespaces, followed by a = move
back to their original space to keep the files from gorwing out of proportion. Of course, I rebuild the indexes also.

So there is a valid reason for doing reorgs. But in my case, it is = because
the files are growing. But not for any other "efficiencies".

Have a nice weekend.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Tim Gorman []=20 Sent: Friday, June 18, 2004 2:12 PM
Subject: Re: SAP Reorgs

I=B9m 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=B9t 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 =B3best practice=B2, I think we = can

So, while quantifying the benefits, I=B9ve 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=B9t imagine = a
reputable doctor recommending yearly liposuctions.

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

on 6/15/04 4:03 PM, at 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=20

>> > cause less LIO for this segment.

> Rebuilding a B*Tree index to conserve space can have detrimental=20
> effects on performance.

> If the index sees a lot of insert activity, your newly rebuilt index=20
> will undergo block splits, and soon be back to the size it was=20
> 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=20
> many FTS, and the access patterns. If straight OLTP, rebuilding to=20
> save space may not buy much performance.
> It may take less space in the block buffers, but then again, =
> blocks may=20
> become hot.=20

> 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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Jun 18 2004 - 14:36:48 CDT

Original text of this message