Poor multi-version SQL performance

Description

Poor multi-version SQL performance after performing a compress operation.

Cause

After compressing the ArcSDE instance's state is set to zero. Multi-version SQL performance can be affected by the Oracle high water mark (HWM) for each add and delete of delta tables.

The HWM is the block address between used and unused space for the segment. As new blocks are requested for the segment and cannot be satisfied by the freelist, a new block is allocated and the HWM increased.

The HWM becomes an important factor for SQL performance during full table scans. When the Oracle optimizer determines a full table scan is the optimal execution plan, the process reads all blocks of the segment up to the HWM. Therefore, after a compress to state of zero, when all the delta tables are empty, the HWM still references a block address that does not reflect the true contents of the segment - zero rows.

This can lead to declining SQL performance because full table scans will have to read every block below the HWM for the segment, which in turn leads to increased disk I/O and poor performance.

Solution or Workaround

Code:
SELECT * FROM sde.states; 

Code:
TRUNCATE TABLE a128;