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.
Best Answer
The delta tables and state tree have a direct performance impact on your queries.
First, you need to understand versioning; I did a short explanation of the relationship of the state tree and version labels in a different answer. I think it would help you to go over it.
After reading that answer, you can then realize how a long state id branch (from root to state-id referred to by a label would impact performance. Why? Because you have more complex joins to recreate the "current" view of the version. Since the compress is trimming the tree, the inner joins become easier to process by the underlying db and your ArcMap sessions become faster.
Take a look at the Versioning Workflows document from ESRI that will teach you how to keep the version state tree under sane control. Use the GDBT to look at the state tree before and after so you can see how a good workflow affects the tree.
Second, if you can get away with not having to use the Geometric Network for most of your use cases, then do that. It will slow down the FeatureClasses that are involved because it uses complex messaging for every Row::store call (as opposed to just storing the row in the table and being done with it).
To update statistics, use the Data Management Tools' Analyze function (mark them all). It will know how to deal with delta tables (and any other tables) that are necessary.