Problem: Poor multi-version SQL performance
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.
Solution or Workaround
Lower the HWM after you have performed a compress operation and you have verified that the compress returned the instance to state 0.
1. Verify that the ArcSDE instance is at state zero by executing the following statement:
SELECT * FROM sde.states;
Only one row should be returned and the state_id should equal zero, and be owned by the SDE user. This is important because the next step will remove all rows in the table.
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;
This command will also remove every extent beyond the initial segment extent.