SAP Note 13655 - SAPDBA: Long runtimes: Exp./imp./reorganization

Symptom:

Long run times (for example, > 10 hours when importing tables ATAB, APQD, ...) are required when exporting or importing, primarily when reorganizing with SAPDBA.

Cause and prerequisites

1. expst, impst do not exist

2. LONG columns in table and "commit = Y" in shell script imp.sh

3. Data mirroring

4. ARCHIVELOG mode

Solution

1. (Caution: not valid for Windows NT)
(Caution2: Singletask versions are no longer supported as of Oracle 8
-> see Note 130345)
The single task versions, expst and impst from ORACLE, run much faster than exp and imp, therefore expst and impst are automatically used by SAPDBA if they exist in $ORACLE_HOME/bin.
However, if exp or imp are entered in the scripts $ORACLE_HOME/sapreorg//exp.sh, .../imp.sh, then expst and impst must be created (compiled).
To do this, do the following as user ora:
cd $ORACLE_HOME/bin
touch expst impst (generate "old" versions)
touch sqldbast
touch sqlldrst
cd ../rdbms/lib
make -f oracle.mk singletask (Oracle 7) or
make -f ins_rdbms.mk singletask (Oracle 8)

2. (also see Note 12621)
SAPDBA generally uses the parameter "commit=Y" during import, which means that a commit is triggered after every individual set of buffer contents (default buffer size in SAPDBA: 3000000), or a commit after enough data records. If, however, a table contains a LONG column, ORACLE processes only one record per buffer, regardless of record length and buffer size - for example, with "commit=Y", a commit occurs after every single record. When large tables such as ATAB, APQD, etc. (differs depending on the user), this can cause the long run times.

Help:
Before you start the SADBDA scripts, change the parameter in imp.sh to "commit=N", and make sure that the import uses a sufficiently large rollback segment. To do this, set all rollback segments in PSAPROLL to OFFLINE and generate a tablespace PSAPROLL2 (SIZE 300...500 MB) with one single rollback segment (INITIAL = NEXT = 8M, OPTIMAL 16M, PCTINCREASE 0). After the import, set this segment back to OFFLINE and the others to ONLINE.
Since this procedure is somewhat complicated, and does not significantly reduce run time when tables without LONG columns or only a few rows are involved, we recommend placing large tables like ATAB, APQD, etc. in a separate tablespace (for example, PSAPLONG) and using the procedure only for reorganizing that one tablespace.

3. Data mirroring (for example, hardware-supported disk mirroring) should not normally increase the time required for reorganization too much (around 15% at most). When long reorganizations are involved, however, it pays to deactivate a mirrored disk and remove it from the backup until the reorganization on the other disk is finished and has been copied to the mirror disk.

4. Archiving the online redo logs by the ORACLE archiver hardly affects the speed of the reorganization. However, the danger of an archiver stack occurs if the saparch file system fills up. Archiver stacks are often the reason for apparently long runtimes during reorganization. The following tips should help you avoid an archiver stack:

a) Before the reorganization, make sure enough space is available in the saparch file system (if necessary, use brarchive to back up the offline redo logs to tape and delete them in saparch, flag "-sd").

b) During the reorganization, run brarchive in parallel with flag "-sd -f" (brarchive will then wait for new OFFLINE redo logs and immediately back them up on tape).

c) Assuming that

• At least the online redo logs are saved after saparch before the reorganization ("alter system switch logfile" once (see below) and use "archive log list" to make sure all the redo logs except the "current" one have been saved) and that

• a full DB offline backup is performed after the reorganization, you can deactivate the ORACLE archiver during the reorganization:
  
  stopsap R3 (shut down R/3 System)
  
  sqldba lmode=y
  SQLDBA> connect internal
  SQLDBA> startup (if DB is shut down)
  SQLDBA> alter system switch logfile;
  SQLDBA> archive log list (-> check!)
  SQLDBA> shutdown
  SQLDBA> startup mount
  SQLDBA> alter database noarchivelog;
  SQLDBA> alter database open;
  
  sapdba ... (-> Reorganization)
  
  SQLDBA> shutdown
  SQLDBA> startup mount;
  SQLDBA> alter database archivelog;
  SQLDBA> shutdown
  SQLDBA> exit
  
  sapdba ... (Full DB offline backup)
  
  

d) Remark: The Oracle statement "Create ... as select"
does not work in the case of tables with LONG columns!
Using SQL fast loaders (as of SAPDBA 4.0) can
significantly reduce the runtime.

Additional key words

sapdba, exp, imp