keeping sequence in shared pool
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: keeping sequence in shared pool

  1. #1
    Join Date
    Apr 2001
    Posts
    257
    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?

    Thanks

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    There seems to be an error in your 'Oracle Material'.
    Which material are we talking about?

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Mar 2002
    Posts
    171
    I second Raminder here. Doesn't sound good.

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    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.

  5. #5
    Join Date
    Mar 2002
    Posts
    171
    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.


  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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.

    Sanjay

  7. #7
    Join Date
    Feb 2002
    Posts
    27
    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 .






  8. #8
    Join Date
    Sep 2000
    Posts
    41
    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.

  9. #9
    Join Date
    Feb 2002
    Posts
    27
    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.

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width