Results 1 to 6 of 6

Thread: sequence cache?

  1. #1
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34

    Unanswered: sequence cache?

    Hi y'all,

    I am taking over for another DBA and I had a question about sequences. In speaking with some people here, the previous DBA created sequences with varying CACHE values (0, 20, or 50).

    The DB is very large and has 885 +/- tables with 544 sequences. It will eventually be 100+ TB of data.

    My question is: why would you set the cache to 20 or 50? Is this for performance reasons? And, why have different sequences caches set at different values? Is there somewhere I can find out more about sequences and performance?

    Any input would be appreciated.

    Thanks,
    Rip
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Different cache values depend on how the SEQUENCE is being used and the frequency of needing the next value. By caching, you are already reserving x number of sequence numbers and placing them into the cache. This is done for performance issues dealing with large transactional databases. The only downside to caching the sequences is that on a database crash, you have LOST the numbers that were cached... In some cases, that can be an issue, ie: accounting dept uses sequences for batch numbers ... they can LIVE with a skipped sequence number... The WORLD must stop until the numbers are retrieved...

    HTH
    Gregg

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi Rip,

    When a CACHE option is used to create the sequence, Oracle pre-allocates a series of numbers equal to the CACHE value into the memory. So it helps in performance because orcle does not have to generates the sequence number every time it insert a row into the table Instead it uses the generated number from the memory (CACHE). When the last number from the memory is used then it again generates a next series of numbers equal to the CACHE value and stores them into the memory.

    It is up to you what value you want to set for the CACHE option. Oracle stores the nuumbers into the memory equal to the number specified by this value.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi greeg, Sorry but I did not know that you have already replied for this post. I guess by the time I was writting my reply, you already replied him.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Hings ... NO problem ... Multiple options are what makes these forums work !!!

  6. #6
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    Thanks for the quick responses.
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

Posting Permissions

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