SAP Note 12621 - SAPDBA: speeding up a reorganization

Symptom:

Reorganization of PSAP00LD takes too long.

Cause and prerequisites

The reason for this is that ORACLE incorrectly interprets
parameter 'COMMIT = Y' when importing long records and, instead
of committing after each ArrayInsert with value buffer (exp_imp_buffer),
commits after every record.

Solution

Note: As of Release 3.0D, a beta version of the SAP unload/load or
ORACLE SQL*Loader is available. The procedure described below
does not apply anymore as the ORACLE imp/impst is not
accessed.

Import with 'COMMIT = N'. However, if you are going to do this, a larger rollback segment is required. This procedure can improve the speed by a factor of 8. If the temporary rollback tablespace or storage parameter of the only temporary segment is dimensioned too small, a rollback might occur (rollback the import with a considerable time loss).
As usual, you should have appropriate backups. Caution: there is no advantage for tables without long fields!
All the required steps are listed below
(SAPR/3 must have been stopped):

1. Determine the largest table of the tablespace affected.
sqldba> CONNECT INTERNAL
sqldba> SELECT MAX(BYTES) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME
= '' AND SEGMENT_TYPE = 'TABLE';
This is usually the ATAB table.

2. Place a rollback segment of at least this size in a separate tablespace (safety margin).

The directory .../rollbig_1 must be created manually beforehand.
sqldba> CONNECT INTERNAL
sqldba> CREATE TABLESPACE PSAPROLLBIG
DATAFILE '/oracle/C11/sapdata1/rollbig_1/rollbig.data1'
SIZE ;
sqldba> CREATE ROLLBACK SEGMENT rollbig TABLESPACE PSAPROLLBIG
STORAGE (INITIAL NEXT
OPTIMAL );
sqldba> ALTER ROLLBACK SEGMENT rollbig ONLINE;
The rollback segment 'rollbig' must be empty at the beginning of the reorganization. If it already exists (e.g. since a previous reorganization of the same type), this can be done by starting up the DB. In this case, it must be entered in the file init.ora.

3. Deactivate the old rollback segments (see init.ora to find out its name. The SYSTEM rollback segment must remain online.)
sqldba> CONNECT INTERNAL
sqldba> ALTER ROLLBACK SEGMENT OFFLINE; ...

4. Start SAPDBA normally up to:
"Choose one of the following options:
(1) - Start script(s) immediately
(2) - Start script(s) in batch
(3) - End (later restart is possible)"

Then edit the file imp.sh in directory $ORACLE_HOME/sapreorg/ and set the commit parameter to NO: COMMIT=N

5. Then continue in SAPDBA (option 1, Start script(s) immediately).

6. After the reorganization, activate the old rollback segments:
sqldba> CONNECT INTERNAL
sqldba> ALTER ROLLBACK SEGMENT ONLINE; ...
and drop the temporary segment:
sqldba> ALTER ROLLBACK SEGMENT rollbig OFFLINE;
sqldba> DROP ROLLBACK SEGMENT rollbig;
and then drop the associated tablespace:
sqldba> DROP TABLESPACE PSAPROLLBIG;
and then remove the associated file:
rm /oracle/C11/sapdata1/rollbig_1/rollbig.data1

7. When reorganizing PSAPPOOLD, you should consider the
possibility of separating table ATAB:

a) Define a suitably-sized tablespace PSAPATAB.

b) Using SAPDBA, select reorganization with data files and reduce the size of the target files of PSAPPOOLD by an appropriate amount.

c) Before starting the scripts, modify the script tabPOOLD.sql so that table ATAB is created in tablespace PSAPATAB and not in PSAPPOOLD.

Key word: SAPDBA