| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Goulet, Dick
Sent: Monday, June 14, 2004 9:44 AM
To: oracle-l_at_freelists.org
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_at_bigpond.com]
Sent: Monday, June 14, 2004 4:49 AM
To: oracle-l_at_freelists.org
Subject: Re: SAP Reorgs
Hi Mike,
-- 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 Mon Jun 14 2004 - 09:12:50 CDT
 |
 |