RE: SAP Reorgs
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Jun 2004 15:39:11 -0400
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@xxxxxxxxxxxxx]=20
Sent: Friday, June 18, 2004 2:12 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx at Jared.Still@xxxxxxxxxxx
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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
