Report the length of a version lineage in Oracle

Summary

Instructions provided describe how to report the length of a version lineage in Oracle, and provides a SQL statement for returning information on the length of each version lineage.

Note:
ArcSDE software, including the application server, command tools, and SDK with C and Java APIs, was deprecated at ArcGIS 10.2.2 and is no longer distributed.

Each version references a specific state in a versioned geodatabase. A collection of states define a lineage. States are created in a hierarchical manner, where each state, excluding state 0, has a primary state.
As versions are edited, new states are created. When the version is saved, the version is updated to reference the last state in the edit session. Over time, as the version continues to be edited and saved, the length of the version's lineage grows.
When ArcGIS queries a versioned table for any given version, the version's lineage is used to derive the table's versioned representation (by joining the versioned table's adds, deletes and sde.state_lineages table). Performance can be impacted by the length of the version's lineage, because of the number of states that must be joined between the sde.state_lineages table and the delta tables. Based upon the number of states in the version's lineage, the Oracle optimizer may choose a hash join verses a nested loops join, when performing the join operation between the sde.state_lineages table and the delta tables.
To ensure optimal performance when working with versioned data, it is recommended to compress as frequently as possible. For example, compress every night. By compressing the versioned geodatabase it ensures that the length of every version's lineage is as short as possible.

Procedure

To generate a list of each version's lineage length, connect as the SDE user in SQL*Plus and execute the following SQL statement.

Code:
SQL> SELECT v.owner||'.'||v.name "VERSION NAME", COUNT(sl.lineage_id) "LINEAGE LENGTH"
  2  FROM sde.states s, sde.state_lineages sl, sde.versions v
  3  WHERE s.lineage_name = sl.lineage_name
  4  AND sl.lineage_id <= s.state_id
  5  AND v.state_id = s.state_id
  6  GROUP BY v.owner, v.name, sl.lineage_name
  7  ORDER BY "LINEAGE LENGTH";

VERSION NAME              LINEAGE LENGTH
------------------------- --------------
TOM.WORKORDER_I0C6531     21
TOM.EDITOR_TB             25
ERIK.WORKORDER_I910313    78
BRENT.DESIGN_F831         92
BRENT.DESIGN_F831_ALT_1   94
BRENT.DESIGN_F831_ALT_2   95
SDE.MAPPING_DEFAULT       138
SDE.LANDBASE              169
SDE.DEFAULT               249
ERIK.EDITOR_EH            275
STEVE.EDITOR_SM           289