For basic concepts on Oracle Sequences please read :
http://www.oraclepassport.com/Sequence.htmlAn Oracle sequence is database objects which provides unique integer values.The Sequence cache size determines how many values Oracle preallocates in memory ie Shared Pool.By finding values in shared pool , Oracle returns the next value from memory by providing quicker access.
Some describe Oracle cache as :Cache a sequence means hold just two values in memory, no matter the size of cache,
So when any session ask for new value the Oracle simple increase the memory count in one, until it reach the cache number.
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.
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.example :
exec sys.DBMS_SHARED_POOL.KEEP('SEQ_NAME', 'Q');
exec sys.DBMS_SHARED_POOL.KEEP('SEQ_NAME1', 'Q');
What should be optimum Cache value for SEQUENCES in oracle ?
If no cache is used , how does it will impact OLTP systems?
Regards,
Ashish
Author