ALTER SEQUENCE CS.SESSION_ACTIVE_SEQ_ID CACHE 2000;
exec sys.DBMS_SHARED_POOL.KEEP(‘CS.SESSION_ACTIVE_SEQ_ID ‘, ‘Q’) =>> To pin the sequence using the DMBS_SHARED_POOL.KEEP procedure (IF NEEDED)
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
