SAP Evaluating Online Re-Organization Tools

Evaluating Online Re-Organization Tools

Content Author: Christophe Rabeau

We are evaluating online reorg tools for an Oracle SAP database which is soon to be of 1 Terra Bite size. I would like some guidance for online reorg.tools and experiences of using some of them.

I use SAP 4.6C under Oracle 9.2 under AIX 5.1 on Risc6000 medium-performance machines.

I experienced some reorganizations SAP online, due to the PRD system that must always be up.

Fisrt, consider that the entire reorg is not possible when SAP is online, because of permanent tables (system tables and so on) that could not be exported/imported while running.

An entire TABLESPACE reorganization is no more suitable because of the long runtime between 2 offline backups.

So you can reorg only some tables/indexes or single objects. Be sure that the objects should not be accessed during the reorg, so it will fail. For a particular case, for example a BIG table, I experienced serious perf problems :

JVTLFZUO table : 10 Gb data + 17 GB indexes = 37 GB to be reorganized.

When SAP is online, it took about 13 hours (4 procs, 8 GB RAM)
When SAP is not available for users, it took less than 5 hours !!!

So my advice is :
- don't reorg too big tables with SAP online.
- don't reorg system tables with SAP online.
- you'd better to perform a reorg by night or in a lower user activity.
- you'd rather to reorg with SAP down for perfs point of views.
- always perform an OFFLINE backup before reorg, because the reorg is not always successful.