Re: SAP Reorgs

From: Kevin Fries <kfries_at_lart.com>
Date: Mon, 14 Jun 2004 06:45:24 -0500
Message-ID: <40CD8FD4.6090106@lart.com>

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:

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jun 14 2004 - 06:42:26 CDT