RE: SAP Reorgs

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Fri, 18 Jun 2004 15:39:11 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5C48@exchsen0a1ma>

Tim,

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 [mailto:tim_at_sagelogix.com]=20 Sent: Friday, June 18, 2004 2:12 PM
To: oracle-l_at_freelists.org
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
agree?

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, Jared.Still_at_radisys.com at Jared.Still_at_radisys.com wrote:

>=20
>> > 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.
>=20

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

>=20

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

>=20
> FFS and Range Scans may benefit from a rebuild, but it would probably =

> be best to quantify the benefit.

>=20

> 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, =
previously
cool
> blocks may=20
> become hot.=20

>=20

> There are no silver bullets.

>=20

> See Richard Foote's paper on index internals, it is very informative.

>=20

> I'm sure he will correct me if I have mis-spoken on any of this. ;)

>=20

> Jared

>=20
>=20
>=20



----------------------------------------------------------------

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
-----------------------------------------------------------------
----------------------------------------------------------------
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:36:48 CDT