Re: SAP Reorgs

  • From: Kevin Fries <kfries@xxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 14 Jun 2004 06:45:24 -0500

Dan Hotka wrote:

Hi,

I have a gig where they have a rather large SAP database...they want to reorg 
it...then
periodically reorg it.  I'll find out this next week all the specifics.

I am wondering if anyone has any experience in doing reorgs on SAP databases.  
What I should
look for...what I should look out for...SAP specific things...which objects 
needs periodic
reorgs...which objects needs a better storage parameters...  Any help/comments 
would be most
appreciated.

Note: I started my reply yesterday so some of this might be redundant. I haven't caught up to the replies yet.


You haven't stated why the reorgs are desired, so I'll go with the
reasons of performance and space reclamation. I'm also assuming that the
tables were recently analysed. (Some tables in SAP are not analysed
purposely, so just be aware of that exception.) If they're extremely
volatile, stats may be worse than useless anyway. For tables that are
expected to just keep growing anyway (as opposed to "static" data) it's
usually pointless to reorg anyway.

Read my comments below acccordingly.

For performance:

The kicker is that if it's done for the sake of performance, you really
need to do this on a transaction by transaction basis. There will be
very few tables (if any) this will apply to. Do verify the table is not buffered since that'll negate whatever benefits the reorg might have for performance anyway.


What's really needed first is to "optimize" the application code or the way the transaction is used and keep the statistics current.

Be sure to benchmark the before and after performance of each action if
the object of this exercise is tuning. You'll be surprised how little it buys for the considerable amount of off-hours work and risk.


The ones you'll be looking for initially:

1. should have a high water mark well above the actual used space of the
table with little chance of growing to that mark in the future.

AND

2. you're also looking for large (and unbuffered) tables that are frequently accessed via the Horrors of Full Table Scans(tm).

PLUS:

3. look at tables with large numbers of multiple extents as a cross
check, not because there's a lot of extents, but simply because if
there's loads of extents and there's a lot of wasted space it indicates
something may have run amok, loaded a bunch of garbage and was cleaned
up, but only to the point of cleaning out the crap data in the tables.

This all assumes a fairly static size now and in the future. If there are any, there won't be many. The main reason I've seen for that is someone loaded the tables with bad data.

If the programs that access the tables are custom programs (start with z*), it's likely that the code needs to be addressed and not the tables.

For space reclamation purposes:

One example of one set of tables that would benefit from a reorg after
data loads during an implemetation are the APQI and the APQD tables
because there's probably loads of unused space at this point. (The data
is removed out of those tables after each load but it isn't uncommon to
have millions of entries in the table at one time in the past.) Most of
the time, any entries left in those 2 tables should be checked to see
if there's any reason to still be there on a production system and
purged if not. This assumes you used BDC to do the data loads in the past. If you're continuing to do them now, it's pointless.


It's not likely that anyone will be using those two tables at this point
so any performance improvement will probably not be measurable, but
that's one example of tables that will be extremely volatile and can have loads of empty space. You could reclaim space, that's why I chose that example. (See my opening remarks/caveat)


One example that might apply:
If they're initially using archiving of data on some of the tables on a
system and have just archived a large amount of data, you're pretty much
wasting your time after the first reorg of those tables. After that it
should find its own level barring any serious screwups. (Under SAP when
they archive data it's offloaded from the database and put somewhere else).

IOW you might make a case for tables that get 3 years worth of data moved off the DB the first time and you intend to archive data monthly.

In that case you're generally not going to get much benefit after the
first reorg on any table you do for that reason, so I'm at a loss to understand why you'd need to do it more than once under normal circumstances.


Periodic reorgs are usually a waste of time. IMO, separating hot tables
onto different disks/controllers is probably going to buy you more than
anything the rest of a reorg might accomplish from a performance
perspective and even that will vary by the types of disk storage. Since you can move them and reorg them (storage parameters) at the same time, I don't quite see a case for just a reorg. The SAPDBA program will move and reorg in one shot.


Reorgs are pretty much for truly exceptional cases, some seem to
think is that it's a process that's going to buy very much when used on a regular basis. It won't, especially where the tables are striped.


It's mostly a holdover from the 505 extents limit on Oracle 7.3 on SAP when it used an 8k blocksize. That went away on 8i. That's about all I ever used it for then and that was only for a few with the storage params set low for the table due to a vendor error. There's an OSS note or two on that you might run across.

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