Poor multi-version SQL performance after performing a compress operation.
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.
1. Verify that the ArcSDE instance is at state zero by executing the following statement:
SELECT * FROM sde.states;
2. Truncate all delta tables and update the table statistics. The only mechanism to lower a segment's HWM is the Oracle TRUNCATE TABLE command. For example,
TRUNCATE TABLE a128;
Get help from ArcGIS experts
Download the Esri Support App