RE: SAP Reorgs
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 14 Jun 2004 09:43:57 -0400
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. Guess 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,
You say that you re-org regularly. My specific question to you is why ? What
specific *performance* issue does a regular re-org specifically address (I
accept the fact that you choose to archive data and save disk but what are
your *performance* implications).
There has been much nonsense written regarding the requirement to regularly
re-org an Oracle database and when one actually looks under the covers to
determine the justification for such re-orgs, such justifications rarely
stand up to scrutiny.
Rather than your generalist claim that you see a "palpable return", can you
please provide a specific example of what your re-orgs achieve. For example,
what is a performance issue that you are addressing (say performance issue
"A"). What is the current metric that is unacceptable (say response time
"B"). What's the reason for this performance issue (say root cause "C").
How/why does the re-org fix this specific root cause (say explanation for
remedy "D"). What is the now acceptable metric (say improved response time
"E").
Like I said, how "specifically" does the re-org help a specific
*performance* issue.
Without "A", "B", "C", "D", "E", I remain sceptical of any such claims ...
Cheers
Richard
----- Original Message -----
From: "Vergara, Michael (TEM)" <mvergara@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, June 14, 2004 2:31 PM
Subject: RE: SAP Reorgs
Dan:
I am surprised at the responses you are receiving about doing
Reorgs. Everyone seems to think it's dumb and unnecessary.
We use SAP here at Guidant. It's our 900-lb gorilla application.
As goes SAP, so goes most if not all of the other systems and
databases.
We reorg regularly. We see a palpable return on several levels
from performing this activity. We archive data on a active
schedule, and reorging the tables/tablespaces that have just been
archived brings down the tablespace size, rebuilds the indexes
for improved performance, and allows us better management of our
disk space.
It is for performance and savings that we reorg. We do not need to
buy as much disk every year, and the performance remains acceptable
to the users. I know...everybody says disk is cheap; try telling
my manglement when we submit a purchase req for more EMC disk.
Cheap? Not from our budget!
With that off my chest, we use Quest Live Reorg to perform our
reorgs. Basically, LR does a CTAS, and then mines the redo
logs to keep the current table and the reorged table in sync
until 'cutover', which is when the new table becomes the main
table and the former main table becomes baggage. We cutover
late on Saturday night, when we can safely take SAP down without
impacting too many users - Quest claims to the contrary, SAP does
notice (and complains bitterly) when the cutover happens if you
try it with SAP up. But cutover only takes minutes, and we've
not yet (hear the sound of me knocking on wood, crossing myself,
and lighting a candle) had an issue with the cutover that was
not recoverable.
----------------------------------------------------------------
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:
