Poor compress performance

Description

Poor performance while performing a compress operation.

Cause

The compress operation is extremely data manipulation language (DML) intensive. The process executes the following:

· Delete and insert statements on each multi-versioned business table.
· Delete and update statements on each multi-versioned table's delta tables.
· Delete statements against a layer's feature and spatial tables.

Depending on the magnitude of data changes for all the multi-versioned tables in the instance, the process issues a significant amount of DML statements that can affect performance and the time it requires to perform the operation.

A DML statement requires the row's prior image be preserved in case the transaction needs to be rolled back, to provide read consistency for a second process, or to recover from instance failure.

Before the row is modified by the DML statement, Oracle writes the row to the instances rollback segment. When a transaction affects many rows, which is the case when compress is executed, all the rows have to be written to one rollback segment. If the rollback segment's extents are small and the volume of data changes large, then multiple extents are required. Creating and extending rollback segments is an expensive process that affects performance. Typically, rollback segment extents are created relatively small to support the average DML statement, but when a transaction modifies a significant number of rows, it is optimal to have a rollback segment with one large extent to improve performance.

Solution or Workaround

• To improve performance, create a rollback segment with an extremely large extent. The size depends on the magnitude of changes that have occurred on any multi-versioned table. A 500M extent is not uncommon.
  
  The commit frequency for compress is defined at the table and layer to guarantee data consistency. A commit will occur when all rows for the business table, adds table, and deletes table have been modified. Then commits will occur after modifications to the feature table and after modifications to the spatial index table.
  
  
• To improve performance by limiting the transaction to one rollback segment, set, in the ArcSDE instance's dbtune file, the name of the rollback segment in the DEFAULT keyword's parameter compress_rollback_segment. This informs the compress process to use the named rollback segment for each DML statement.
  
  It is also recommended to keep large rollback segments off-line until they are needed for the specific compress operation. Turn rollback segments on and off line by using the Oracle ALTER ROLLBACK SEGMENT command, for example:
  
  

  Code:
  ALTER ROLLBACK SEGMENT <name> OFFLINE; 

  
  
  

  Note:
  If using an instance of ArcSDE prior to 8.1, there is still a benefit in using a large rollback segment. Simply set all active rollback segments to off-line. Then set the large rollback segment to on-line for the compress operation. When finished, turn off the large rollback segment and turn all other rollback segments to on-line.