RE: SAP Reorgs

>> I'm going to take some exception to your statements.  Periodic reorgs on
tablespaces and tables that experience high transaction rates, specifically
inserts & deletes, is just about mandatory for any database application. <<

Our shop is a high transaction rate environment (MRP II) and experience
tells me that for the great majority of tables there is no performance or
space reclaim benefit from periodic reorganizations.  We do however set
pctused to 95 minus pctfree so that space is reclaimed more aggressively
than the default pctused of 40% provides.  If you run with the defaults and
do not preallocate ITL work areas then there may be more benefit to a table
reorganization than for a shop that preconfigured the table parameters.

For indexes we have also not found any measurable performance benefit, but I
have numerous indexes that do shrink to using 1/2 to only 1/5 of their
pre-reorganization allocation.  We have yet to observe a steady state size
limit for some of these indexes.  Index reorganizations to reclaim space do
provide us with space reuse so that we do not have to allocate additional
files.  We perform these functions on a few times per year (2 - 4) basis
depending on the free space usage.

IMHO -- Mark D Powell --


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Goulet, Dick
Sent: Monday, June 14, 2004 9:44 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: SAP Reorgs


Richard,

        I'm going to take some exception to your statements.  Periodic
reorgs on tablespaces and tables that experience high transaction rates,
specifically inserts & deletes, is just about mandatory for any database
application.  The reason is that you'll end up with a lot of blocks on the
free block list that just won't hold another row of data for one reason or
the other.  It's regrettable that setting pctfree & pctused is such a black
art that is ignored 99% of the time.  The result is that Oracle will scan
the free block list, but only so far before allocating another block to the
table.  Therefore you end up with very sparsely populated data blocks and
resulting decrease in performance on all fronts.  Also indexes that have a
lot of insert, update, delete activity against them become similarly off
balanced and fragmented.  Therefore you end up with one of two possible
courses of action, 1) get more disk space & the previous poster is right EMC
disk ain't cheap,  2) reorg.    G
 uess which one is much more cost effective?  The trick, as has been
previously posted as well, is to determine WHEN a reorg is needed.  If
you've done a fairly good job of setting up the database you should be able
to get away with a fairly infrequent need.  For our PeopleSoft environment,
we typically reorg the tables once a year & the indexes quarterly, as
required of course.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
From: Richard Foote [mailto:richard.foote@xxxxxxxxxxx]
Sent: Monday, June 14, 2004 4:49 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: SAP Reorgs


Hi Mike,

----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: