Here is a quote from Oracle material:
Which objects to keep in shared pool:
Sequences, since sequence numbers are lost when the sequence is aged out of the shared pool
Why are the sequences numbers lost? Doesn't DBA_SEQUENCES keep track of the next sequence number?
There seems to be an error in your 'Oracle Material'.
Which material are we talking about?
I second Raminder here. Doesn't sound good.
Sequences have a parameter called cache which is default 20.
If the SGA is too small , hten the sequences would be aged out of SGA and since sequences are incremented by the value of cache size and put in SGA , all those sequences which were there in SGA
due to cache size and not used would be lost.
So Oracle material is ok.
gpsingh: Thanks for the share of knowledge. Can you please explain what is the "cache" about sequences and how is it applicable in detail?
I was under the impression that the DICTIONARY gets updated whenever we created a sequence and whenever NEXTVAL was used. I was always thinking it to be a database operation. Never realized how SGA plays a role here.
Would appreciate if you could enlighten us with more details of how "cache" affects a sequence and how it relates to the DICTIONARY table ALL_SEQUENCES or DBA_SEQUENCES.
Awaiting your kind response.
gpsingh is right. The cached but unused values of the sequence will be lost if the sequence is aged out from the SGA. To avoid sequences to be aged out from SGA, you can set the initialisation parameter SEQUENCE_CACHE_ENTRIES to higher value.
Thanks Sanjay ,
To avoiad the age out problem for unused values I was keeping cash_size to 0 for sequences.
I will try the initilization parameter SEQUENCE_CACHE_ENTRIES .
Can anybody tell me what is the advantage we get by using the cache option.
Because, I am used to creating sequences with the nocache option.
If ur application is such that many users intracts with the databse to generate the some types record which in turn use the sequence then u can use the cash_size option and it will give u performance benifits on the cost of aging of unused numbers.
sequence_cache_entries is not used in 8i and 9i.
The reason is that Oracle made sequences to be handled just like procedures for example. They just get aged out of the shared_pool by the normal LRU algorith.
If you reall what a sequence in the shared pool (wonder why) then use dbms_pool.keep.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
Click Here to Expand Forum to Full Width