Maintaining SQL performance with versioned queries when having a large number of edits and long lineages.
ArcSDE analyzes the SDE.STATE_LINEAGES table when the table is created during ArcSDE installation or during an upgrade. From that point on, ArcSDE does not analyze the table; the DBA is responsible of it.
Once the database is in a production environment, the number of rows in the table can grow significantly.
If the database statistics for the table are not current and accurate, the instance's optimizer might choose a sub-optimal execution plan for fetching rows. This can have a negative affect on all version queries, because the query performs a selection from the table to detect a version's lineage.
ANALYZE TABLE state_lineages COMPUTE STATISTICS;