We have a versioned arcsde geodatabase (arcgis 9.3.1 on oracle 10g) with a fairly complex data model that includes about 100 featureclasses and non spatial tables, a geometric network and many relationship classes.
The data is edited daily by 5 or 6 arcmap users utilizing sde versioning. In addition versions are created by automatic services that interface with other business systems to perform edits in the geodatabase. Query performance degenerates noticeably during the course of the day, so we have implemented a nightly script to achieve a full compress. On occasions when a relatively large number of edits are performed, the system can become unusable until after a full compress.
Its been suggested that oracle as configured can not come up with decent execution plans when confronted with these volatile delta tables. Is this a reasonable explanation? What approach should be taken to resolve it?
Update in response to comments
- By end of day, the state tree is very linear, with only a little branching.
- We compress nightly (get a full compress by deleting all versions).
- Business tables are analyzed regularly.
- Delta tables are not analyzed. They are locked (Attempt to analyze returns error "ORA-20005 object statistics are locked"). Neither are the volatile tables in the sde schema - STATES, STATE_LINEAGES.