Database Actions Before Archiving

Archiving application data helps to prevent storage and performance bottlenecks. Since relocating data can, in some circumstances, in itself, impair performance – this is the case if you need to access archived data – you need to consider carefully what data to archive.

To determine whether or not you should archive data, consider the following questions:

?      If there are memory problems, can more memory be assigned to the table (MAXEXTENT, Tablespace)?

If the answer is yes, and if you will need to access the archived data and you have no performance problems, you should consider enlarging the table. This procedure is described in the SAP NetWeaver AS document SAP Database Guide: Oracle and Informix (BRSPACE for Oracle and SAPDBA for Informix) and the ABAP Dictionary, section Tablespaces and Extents (database-independent parameterization, MaxDB).

?      How likely is it that you will need to access the archived data again? How often?

If you need to access the archived data often and there are no performance problems, assign more memory to the tables in question (see above).

?      Is the data accessed using an optimal index?

An appropriate index may exist, but may not be used. This depends on the database optimizer.

You can check which index is actually used to access data by Performing an SQL Trace. For more information on indexes, refer to the SAP NetWeaver ASdocument ABAP Dictionary and search for appropriate sections using “index” as your search term.

?      Does the application perform a full table scan on the tables that contain the data to be archived?

If the answer is yes and the table is fragmented, it may help to reorganize the table before archiving so that new records and any records that remain in the database are physically contiguous.

Reorganization takes a long time and may need to be repeated after archiving.

Throughput during a reorganization:

?        With export/import:         approximately. 60-100 MByte/hour.

?        With unload/load:            approximately. 250-300 MByte/hour.

See also:

Oracle:

Space Management with BR*Tools

Segment Management with BR*Tools

Informix:

Dbspaces with SAPDBA

Reorganization with SAPDBA