Re: SAP Reorgs
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 14 Jun 2004 10:00:57 +0100
Comments in line.
On Sun, 13 Jun 2004 21:31:30 -0700, Vergara, Michael (TEM)
<mvergara@xxxxxxxxxxx> 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 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORANETUSER 14-JUN-2004 09:26@oranet>select count(*) from t1;
COUNT(*)
----------
200000
1 row selected.
ORANETUSER 14-JUN-2004 09:26@oranet>exec
dbms_stats.gather_table_stats(user,'T1',cascade=> true);
PL/SQL procedure successfully completed.
ORANETUSER 14-JUN-2004 09:50@oranet>select num_rows,blocks
2 from user_tables
3 where table_name='T1';
NUM_ROWS BLOCKS
---------- ----------
200000 496
1 row selected.
ORANETUSER 14-JUN-2004 09:52@oranet>DELETE T1 WHERE C1 <50001;
100000 rows deleted.
ORANETUSER 14-JUN-2004 09:53@oranet>COMMIT;
Commit complete.
<snip mistyping the character i :( ) >
ORANETUSER 14-JUN-2004 09:55@oranet>ED
Wrote file afiedt.buf
1 BEGIN
2 FOR I IN 100001..200000 LOOP
3 INSERT INTO T1 VALUES(I);
4 END LOOP;
5* END;
ORANETUSER 14-JUN-2004 09:55@oranet>/
PL/SQL procedure successfully completed.
ORANETUSER 14-JUN-2004 09:55@oranet>COMMIT;
Commit complete.
ORANETUSER 14-JUN-2004 09:55@oranet>exec
dbms_stats.gather_table_stats(user,'T1',cascade=> true);
PL/SQL procedure successfully completed.
ORANETUSER 14-JUN-2004 09:55@oranet>select num_rows,blocks
2 from user_tables
3 WHERE TABLE_NAME='T1';
NUM_ROWS BLOCKS
---------- ----------
200000 496
1 row selected.
ORANETUSER 14-JUN-2004 09:56@oranet>
In this, admittedly somewhat trivial case - all my space was reused.
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
----------------------------------------------------------------
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:
