Poor performance while performing a compress operation.
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.
ALTER ROLLBACK SEGMENT <name> OFFLINE;
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.