Re: View current seq. no.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 14 May 1999 09:05:57 +0100
Message-ID: <926669904.15373.1.nnrp-04.9e984b29@news.demon.co.uk>

For integrity reasons Oracle needs to write some form of highwater mark to the database BEFORE it gets there (so that a crash would not result in a set of sequence numbers being re-used). The size of the CACHE tells Oracle how far to bump this HWM each time it is reached.

Each time the HWM is reached, Oracle has to execute a recursive transaction to update the data dictionary table SEQ$ with the next value for the HWM.

In a high-speed, highly concurrent, OLTP system, the cost of doing this frequently can become a significant fraction of the cost of an end-user transaction.

The 'kernel' processing of a user call for a nextval is

    return cached nextval
    increment nextval to new value
    if new value = cached HWM then

        jump HWM by cache size
        write new HWM to sys.seq$

    end if

(it is possible that the test is actually made at the start of the call rather than the end of the next call)

It is very easy to demonstrate that Oracle does not work in the same way as your engine:

session 1

    SQL> create sequence test_seq;
    SQL> select cache_size from user_sequences where sequence_name = 'TEST_SEQ';
    CACHE_SIZE

---

                20

    SQL> select test_seq.nextval from dual;     NEXTVAL

---

                1

Session 2

    SQL> select cache_size from user_sequences where sequence_name = 'TEST_SEQ';
    NEXTVAL

---

                2

Session 1

    SQL> select cache_size from user_sequences where sequence_name = 'TEST_SEQ';
    NEXTVAL

---

                3

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

David Pattinson wrote in message <373B7326.7AC0FCA3_at_addease.com.au>...
>If there's a globally cached 'next' number, what is the point
>of setting chache to > 0? since there would always be
>contention for the 'next' value. I suspect that the globally
>cached next number is used to allocate a new set of values to a
>new sequence user. (The reason I suspect this is that we have
>built our own caching sequence engine using stored functions,
>and this is how it works). Thus if cache is set to 10, and
>global_next is 4, the first user will be allocated the sequence
>(4,..,13) and global_next will be bumped to 14, second user
>will get (14,...,23) and global_next gets bumped to 24. Until
>the first user runs out of id's, there will be no contention
>for the global_next as the range already granted is wholly
>'owned' by the first user. You can get around the problem of
>gaps by re-allocating partially used ranges where the
>connection has dropped to new users, (each range keeps it's own
>next value).
>I may be wrong, perhaps Oracle sequences do not work this way -
>but I'd like to find out either way :)
Received on Fri May 14 1999 - 03:05:57 CDT