Cache Oracle sequence to improve data dictionary resources

Summary

ArcSDE uses Oracle sequences for several purposes, such sequences ensure uniqueness and are defined to increment by a specific value. Some of these sequences are: - SDE_row_id, shape identification, connection, table, layer, locator, raster, logfile, version, and state identification generation.Oracle sequences can be cached in memory to improve performance when fetching the next value. When a sequence is present in memory, a range of values is available for client requests. The range of values in memory is defined by the cache size when the sequence is initially created or altered.ArcSDE creates all SDE_row_id, shape_id sequences with Oracle's default cache size equal to 20. If the sequence is frequently referenced, such as the state_id generator sequence or a registered table's SDE_row_id sequence, Oracle may use valuable resources populating the range in memory. You can avoid this by altering the sequence and increasing its cache size.

Procedure

To determine what sequences exist for a user and their current cache size, execute the following command in SQL*Plus:

SELECT sequence_name, cache_size
FROM user_sequences; 

ArcSDE generates SDE_row_id and shape_id sequence names by combining the SDE.table_registry registration_id, R<#> and SDE.layers layer_id values, I<#>; therefore, a sequence named R10 will be used for the table whose registration_id is equal to 10 in the SDE.table_registry table. This is important to understand because you will need to know which sequences are used by which tables and layers in your database that are frequently modified. New sequence values are only needed when new rows are generated.

Once you have determined the active tables and layers in your instance, you need to increase the cache size value. You can do this with the following command in SQL*Plus:

ALTER SEQUENCE r10 cache 1000; 

The next time the sequence is referenced by an application, Oracle will place in memory a range of 1000 values. This will avoid the need for SQL Oracle to do a recursive check against the Oracle data dictionary to detect the next sequence value, which saves resources.

We cannot recommend a cache size value for universal purposes; thus, we leave the cache size at the default Oracle value. Cache sizes of 1000 or greater are valid and may be good starting points. Further monitoring is required by the DBA to determine an optimal size.

The cache size you select does not have a significant impact on the memory required in Oracle's SGA to store this information. But you should keep in mind if the sequence is aged out of memory, or the instance is abruptly shutdown, the values will be lost. It is also recommended to avoid the sequence from being aged out of memory to pin the sequence in memory with the DBMS_SHARED_POOL procedure.

SYS.DBMS_SHARED_POOL.KEEP('R10','Q');