Problem: Poor query performance on the sde.table_locks table
Poor query performance on the sde.table_locks table can occur as the number of users and the number of shared table locks increase.
ArcSDE executes various queries against the TABLE_LOCKS table to manage shared and exclusive table_locks for client applications. For example, shared locks are acquired by the geodatabase when a feature class or objectlcass is opened and released when the reference is closed. Exclusive table locks are acquired by the geodatabase when the schema is modified.
Large organizations which use complex ArcMap documents that contain more than a handful of layers can cause poor query performance on the sde.table_locks table. For each layer in the document there will be one corresponding shared lock in the sde.table_locks table. As the number of users and locks increase the number of rows in the table can grow significantly. Therefore, the queries ArcSDE executes against the table will result in reading the entire table, causing sub optimal performance and additional overhead in the DBMS. Under these conditions, an index is required to improve query performance.
The following two queries are examples of the queries which will benefit when an index is present.
'SELECT SDE_ID,LOCK_TYPE,REGISTRATION_ID FROM SDE.TABLE_LOCKS WHERE REGISTRATION_ID = :b1'
'DELETE FROM TABLE_LOCKS WHERE SDE_ID = :b1 AND REGISTRATION_ID = :b2'
Solution or Workaround
To improve ArcSDE query performance simply requires creating a composite index for the table on the sde_id and registration_id columns.
An example of how you would do this in Oracle is as follows:
1. Start SQL*Plus and connect as the SDE user.
2. Create the new index:
CREATE INDEX <index_name> ON table_locks
(sde_id, registration_id) TABLESPACE <tablespace_name> STORAGE (INITIAL 256K NEXT 256K FREELISTS 4);
The storage parameters are example values, but an initial and next extent of 256K should be sufficient based upon the number of expected rows.
For additional information on creating indexes see your specific DBMS documentation.