9

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.
Improve this question
8
  • Have you examined the state tree using the Geodatabase Toolkit (GDBT)?
    – Kirk Kuykendall
    Commented Jan 20, 2012 at 16:26
  • No Kirk, what should I be looking for?
    – nef001
    Commented Jan 20, 2012 at 16:44
  • do you use a specific versioned workflow?
    – Ragi Yaser Burhum
    Commented Jan 20, 2012 at 17:17
  • 3
    About your Gdbt question, you are looking for funky state tree branches that look more linear and far away from SDE.DEFAULT as opposed to "bushy"
    – Ragi Yaser Burhum
    Commented Jan 20, 2012 at 17:18
  • All versions are created from default and reconciled and posted to default as seen fit by our users. They may create 3 or 4 a day each. We batch process service requests using arcobjects code running in an arcgis server context. Each batch creates a version, performs the edits, reconciles and post to default and deletes the version. Probably about a dozen times a day.
    – nef001
    Commented Jan 20, 2012 at 18:22

2 Answers 2

Reset to default
7

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.

Improve this answer
4

[First post apologies: This is meant to be a comment not a definitive answer.] If you have any edit versions that are relatively old and have not been posted they should be deleted, posted or reconciled. An old unreconciled version keeps an old view of default, which prevents delta records belonging to newer versions from being compressed into the base tables. There can be a huge number of these uncompressed delta records pinned to an old version and performance gets affected because all versions are views on the delta and base tables. System performance is related to the number of edits since each version was last reconciled (or created). So in short; if there are any versions that you cannot post then reconcile them regularly, and compress.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.