| 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
Hi Dick,
A few comments embedded.
Richard,
I'm going to take some exception to your statements.
RF: That's fine so long as it's only "some exception" ;)
Periodic reorgs on tablespaces and tables that experience high transaction rates, specifically inserts & deletes, is just about mandatory for any database application.
RF: Disagree I'm afraid.
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.
RF: Having "very sparsely populated data blocks" is something that I avoid by appropriate proactive management, not by periodic re-orgs. And without one black art spell to boot ... Objects to exhibit such measurable performance problems would be (should be) very very rare exceptions.
Also indexes that have a lot of insert, update, delete activity against them become similarly off balanced and fragmented.
RF: Again, in general, I disagree. See
www.actoug.org.au/Downloads/oracle_index_internals.pdf for all the juicy
details ...
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. Guess which one is much more cost effective?
RF: Ummm option 3), ensuring you manage the database appropriately in the first place so that 1 and 2 become unnecessary :) Seriously !!
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.
RF: But what determines these timings. Does the database suddenly one day go plop, re-org required ? Or does the database slowly grind to a halt and when you can't take one more complaint, you decide a re-org is required ? And do these performance problems really happen database wide or does it start with just a few tables/indexes and then spread like some kinda disease to other tables/indexes ? Do *all* objects really need to be "vaccinated" or just the sickies ?
I'm sorry Dick but your experience in requiring a database re-org most certainly doesn't match mine. Again, I go back to my A, B, C, D, E... We re-org the odd *object* on a as needs basis and that's about it.
Just some thoughts :)
Richard
 |
 |