Performing Processes in Parallel to Speed Up Reorganization (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Performing Processes in Parallel to Speed Up Reorganization

Use

You can improve the performance of an Oracle reorganization with SAPDBA by performing certain operations in parallel.

Note

Be aware that, if you set the degree of parallelism to a value greater than 1, Oracle creates objects with the corresponding number of extents. SAPDBA divides the extent size for the reorganized table by the number of extents to be created, so that the overall size is as expected.

Procedure

Perform the following operations in parallel:

Data transport, if you are using the parallel export and import method of reorganization

The export or import is performed in parallel by:

Setting the degree of parallelism to greater than 1 using one of the following:

The option Parallel in the SAPDBA menu for Export/import.

The profile parameter exp_imp_degree.

Distributing the tables to be exported to several different export processes

In SAPDBA, define multiple export dump destinations. To export in parallel to a single dump destination, you can enter the same destination multiple times.

A corresponding number of export dump files is generated. The different export processes can be processed either sequentially or in parallel.

The time taken for the data export or import using the Oracle tools Export or Import depends on the amount of data. These procedures take a large part of the total processing time within the reorganization process. Therefore, we recommend you to perform the export or import in parallel.

For more information, see the Oracle documentation.

Creating objects, if you are using the Create As Select reorganization method using SQL statements (that is, CREATE TABLE AS SELECT ) for tables

The creation of large tables and their indexes can take a long time. Therefore, we recommend you to run these operations in parallel. For more information, see the Oracle documentation.

SAPDBA supports the parallel creation of tables and indexes if you proceed as follows:

Set the degree of parallelism with the parameter index_degree in the profile init<DBSID>.dba (this is also for tables, not just indexes). For more information, see index_degree.

Set the degree of parallelism by setting the parameters Parallel, ParTable, or ParIndex in the SAPDBA reorganization menu.

Note

It does not make sense to perform the specified processes in parallel if you select a degree of parallelism that is significantly larger than the number of CPUs in your system. Make sure that the degree of parallelism is no larger than two or three times the number of CPUs in your system.