Is This Content Helpful?
We're glad to know this article was helpful.
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.
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
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;
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.