Re: Qestion about Sequence Object

In article <37565888.9AA4F8CE_at_nri.co.jp>,   Nobu <n-koduka_at_nri.co.jp> wrote:
> Hi,
> my question is easy to Oracle guru.
> I use sequence objects to get unique number in my oracle application.
> (You can see it by doing "SELECT * FROM USER_SEQUENCES ")
> Sequnece object has Cache Size parameter to speed up.
>
> My question is, "when this cache is flushed".
> I got a curious situation,
>
> I created a sequece object, its parameters were as follows;
>
> Start with : 100
> Increment by : 1
> Cache Size : 100
>
> when i first got a sequence, the number was 100,
> (I did, "Select MY_SEQUENCE.NEXTVAL from DUAL" and Result was 100.)
> and right after that, I did the same to get next sequence,
> the mumber was 101. I did it over and over again.The number was
> incremented by 1. The last number I got was 110.
>
> and then I did nothing and nobody else did nothing to my sequence
> object.
>
> But, 2hours later, when i got the next number,
> the number jumped to 200!
>
> why?
>
> So, i did some experiment. Yesterday, I created another sequence
> object.(The parameter was the same) and got the first number (it was
> 100) and left it alone all night.
> Tommorow morning, 12 hours later, i saw the next number was 101,
> This time, cache was not gone. Why?
> And, 24 hour later, the cache memory was gone and the next number
> was 200.
>
> I know "NO CACHE" option. But it doesnt clarify my question.
> I just want to know when the cache is cleared ?
>
> Does anybody help me out?
> I already read set of oracle manuals, but they didnt help.
>
> Thank you very much for your input.
>
> -Nobu
>

1) The sequence cache for a specific sequence is cleared when any of the following occur:

2) The last cached number has been given out and another has been requested for the sequence.

3) The next sequence number is requested for a sequence that is not in the sequence cache buffer, but which has the cache parameter set. Space is made in the buffer by flushing cached sequences using some type of LRU logic. The flushed sequence values are lost.

The instance is shutdown, either by command or crash.

To eliminate or at least reduce flushed you can set the init.ora parameter, sequence_cache_entries, to at least one for every sequence in sys.dba_sequences with the cache parameter set. This will size the cache buffer pool large enough to hold all your sequences.

You can help reduce flushing by reducing the size of the cache parameter for sequences that are only referenced a few times a day to a number less than or equal to the number of times referenced. This will leave more space in the cache buffer pool for the sequences that need it.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 03 1999 - 08:12:44 CDT