How To: Report the Number of Versioned States Being Created Each Day in Oracle

Summary

Organizations sometimes require the ability to monitor and report the daily volume of activity occurring in their versioned geodatabase.

The following queries provide insight into how many states (edit operations) are occurring each day and the number of states in the geodatabase for each day.

This information can be used to graph the workload that the organization is performing and identify any inconsistencies in the organizations workflow. For example, if the number of states continues to increase from day to day, it could indicate that the compress command is not being executed or is encountering errors.

Procedure

The first query reports the number of states that exist in the geodatabase per day (EXISTING STATES), the number of states that were created for each day (STATES CREATED), and the specific day (DAY).

Versioned states are created for each edit operation and reconcile performed. Ideally, one would expect the number of states created from day to day to be nearly the same, but if the number of states being created suddenly increases for a day, this could be an indicator of odd behavior or a poorly designed application that is generating a significant number of edit operations. If the number of existing states increases, this could be an indicator that the compress operation is not able to compress as much as it has in previous executions (the number of versions in the database is possibly increasing or users were connected to the geodatabase when the compress operation was executed).

Code:
SQL> SELECT COUNT(ROUND(creation_time, 'DDD')) "EXISTING   STATES", 
  2  max(state_id) - min(state_id) "STATES CREATED", ROUND(creation_time, 'DDD') “DAY” 
  3  FROM sde.states 
  4  GROUP BY ROUND(creation_time, 'DDD')
  5  ORDER BY 3;

EXISTING STATES STATES CREATED DAY
--------------- -------------- ---------
             15           6582 17-JUN-08
             50           6750 18-JUN-08
            762           6800 19-JUN-08
           1099           7004 20-JUN-08
            748           3712 21-JUN-08

The next query reports the number of versioned states that exist for the current day (CURRENT ACTIVE STATES), the number of states that have been created (EDIT OPERATIONS TODAY), and the time the query was executed (MOMENT).

This query provides insight into how active the geodatabase is based upon the number of edit operations. If the number of edit operations significantly increases for any given day, it can be an indicator to something behaving differently than previous days.

Code:
SQL> SELECT COUNT(creation_time) "CURRENT ACTIVE STATES",
  2  max(state_id) - min(state_id) "EDIT OPERATIONS TODAY", 
  3  TO_CHAR(SYSDATE, 'MON-DD-YYYY HH:MI:SS PM') "MOMENT"
  4  FROM sde.states
  5  WHERE creation_time > ROUND (SYSDATE, 'DDD') - 1;

CURRENT ACTIVE STATES EDIT OPERATIONS TODAY MOMENT
--------------------- --------------------- -----------------------
                 3695                 28599 JUL-29-2008 05:25:15 PM

Article ID:000010192

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Report the number of versioned states being created each day in Oracle

Summary

Procedure

Discover more on this topic