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.
