Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SAP Reorgs

Re: SAP Reorgs

From: Niall Litchfield <>
Date: Mon, 14 Jun 2004 10:00:57 +0100
Message-ID: <>

Comments in line.
On Sun, 13 Jun 2004 21:31:30 -0700, Vergara, Michael (TEM) <> wrote:
> Dan:
> I am surprised at the responses you are receiving about doing
> Reorgs. Everyone seems to think it's dumb and unnecessary.

I don't think that folk think that is is necessarily dumb, just that many of the rationales for it are misguided. If it were a zero cost operation people wouldn't object so heavily.  

> We use SAP here at Guidant. It's our 900-lb gorilla application.

nice image - I hope it never gets angry with you :)

> 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

Well there is a *probably* perfectly good reason to reorg - to save on space. It might be worth noting however that much if not all of the unused space you have just created can be reused by new data coming into the system.

, rebuilds the indexes
> for improved performance,

In *almost* no cases will rebuilding b*-tree indexes improve performance. - moving the index blocks out of the SGA will harm performance for a while. Now if you had permanently reduced the size of your tables such that your indexes dropped a couple of levels in height *permanently* then the couple of io/call that you save might add up to a worthwhile saving.

Bitmap indexes are a different story.

> 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!

What happens to disk usage if you do your archiving, but do not reorg? Have you tried this and experienced bad effects? I'd expect for the most part the new data just to reuse the data blocks that the old data has gone from.

> 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.

This of course is the downside of your reorg - you have to do it out of hours, you have to be prepared to recover if things go wrong, you have to have a tool to do the hard work for you, and you have to potentially deny service to some users. All this will have a cost - probably if you are a commercial entity a quantifiable dollar cost. The question is what benefit do you get for this cost?

Manageability is certainly a potential upside - but it sounds like you expect the new data to not reused recovered space from the deletes - I'd expect (in general) the opposite.

For example I have table here that just happens to have the values 1 to 100000 in it twice, lets see what happens if we clean out half the records - you''l have to trust me but there were inserted in 2 different operations whilst I was testing some .net code out and so they are in different parts of the table - then delete some brand new records with a higher range

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release - Production

ORANETUSER 14-JUN-2004 09:26_at_oranet>select count(*) from t1;



1 row selected.

ORANETUSER 14-JUN-2004 09:26_at_oranet>exec dbms_stats.gather_table_stats(user,'T1',cascade=> true);

PL/SQL procedure successfully completed.

ORANETUSER 14-JUN-2004 09:50_at_oranet>select num_rows,blocks   2 from user_tables
  3 where table_name='T1';

---------- ----------

    200000 496

1 row selected.

ORANETUSER 14-JUN-2004 09:52_at_oranet>DELETE T1 WHERE C1 <50001;

100000 rows deleted.

ORANETUSER 14-JUN-2004 09:53_at_oranet>COMMIT;

Commit complete.

<snip mistyping the character i :( ) >

ORANETUSER 14-JUN-2004 09:55_at_oranet>ED
Wrote file afiedt.buf

  2 FOR I IN 100001..200000 LOOP
  5* END;
ORANETUSER 14-JUN-2004 09:55_at_oranet>/

PL/SQL procedure successfully completed.

ORANETUSER 14-JUN-2004 09:55_at_oranet>COMMIT;

Commit complete.

ORANETUSER 14-JUN-2004 09:55_at_oranet>exec dbms_stats.gather_table_stats(user,'T1',cascade=> true);

PL/SQL procedure successfully completed.

ORANETUSER 14-JUN-2004 09:55_at_oranet>select num_rows,blocks   2 from user_tables
---------- ----------

    200000 496

1 row selected.

ORANETUSER 14-JUN-2004 09:56_at_oranet>

In this, admittedly somewhat trivial case - all my space was reused.

Niall Litchfield
Oracle DBA

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Jun 14 2004 - 03:58:33 CDT

Original text of this message