| 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
Richard,
Well at least we agree to disagree. I can see some of your point of view, reorg the problem child vs. the entire database. But then there I take the KISS principle. It's far simpler & therefore less likely to cause a problem to simply export an entire schema, drop all of the objects therein & import it back, than to try to surgically reorg just the bad apples. Especially if there are a lot of bad apples in the pile as is often the case with PeopleSoft.
Other applications, yes I do get away with only the odd bad apple every here and then, but with a third party application where the "vendor knows best" your likely to not have a choice.
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 10:38 AM
To: oracle-l_at_freelists.org
Subject: 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
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |