Alter Oracle sequence cache sizes for specific SDE repository sequences

Summary

There are three sequences used by ArcSDE for Oracle that are frequently accessed and should not have a cache size of 0. These sequences are:
· state_id_generator_nc
· connection_id_generator
· version_id_generator

The cache size for these can be increased to 1000.

Background infomation

An Oracle sequence is a database object that provides unique integer values. The sequence cache size determines how many values Oracle preallocates in memory, in the Shared Pool. By preallocating values, Oracle returns the next unique value from memory providing faster access to the information.

Potential drawbacks

Setting a cache size larger then 0 can result in the loss of sequence values if the system is shutdown abruptly. When the system fails, the values that were preserved in memory are lost to the sequence.

For example, assume a sequence has been created with a cache size of 100. The first time the sequence is used, Oracle caches values 1 - 100 in memory. Subsequently, Oracle sessions use the cached information and use values 1 - 45. At this point the Oracle shuts down abruptly. When the sequence is used after start up, Oracle caches values 101 - 200 in memory, resulting in values 46 - 100 being lost.

Additionally, sequence values that have been cached in the Shared Pool can be aged out of memory depending on the activity of the database. To avoid aging out a sequence, pin the sequence using the DMBS_SHARED_POOL.KEEP procedure.

Procedure

1. Log in to SQL Plus as the SDE user and alter the sequences using the following commands:
   
   

   Code:
   ALTER SEQUENCE sde.connection_id_generator CACHE 1000
   
   ALTER SEQUENCE sde.state_id_generator_nc CACHE 1000
   
   ALTER SEQUENCE sde.version_id_generator CACHE 1000

   
   
2. Log in to SQL Plus as the SYS user and pin the sequences in the Shared Pool:
   
   

   Code:
   exec sys.DBMS_SHARED_POOL.KEEP('sde.connection_id_generator', 'Q')
   
   exec sys.DBMS_SHARED_POOL.KEEP('sde.state_id_generator_nc', 'Q')
   
   exec sys.DBMS_SHARED_POOL.KEEP('sde.version_id_generator', 'Q')