Re: sequence numbers

From: Richard Kuhler <noone_at_nowhere.com>
Date: Tue, 21 May 2002 18:15:45 GMT
Message-ID: <l9wG8.764$wj7.181364@twister.socal.rr.com>

I would certainly agree that you should only be adding this complexity and overhead if there is a true requirement to provide these types of sequences. However, even Oracle sequences require serial access. They just do it more efficiently then you can manually (especially when caching in memory). I did this simple benchmark (very rough) which seems to indicate managing the sequence yourself is going to be about 3 or 4 times slower than using a standard Oracle sequence ...

create table t (id number);
insert into t values (1);

run this 10 way parallel:

declare

    next_id number;
begin

    for i in 1 .. 100000 loop

        update t set id = id + 1 returning id into next_id;
        commit;

    end loop;
end;
/

real 15m28.57s

create sequence s;

run this 10 way parallel:

declare

    next_s number;
begin

    for i in 1 .. 100000 loop

        select s.nextval into next_s from dual;     end loop;
end;
/

real 4m21.59s

Note 1: The test system only had 2 processors and was under additional load.
Note 2: The sequence could be made faster by increasing the cache size (20 by default).

Richard

Niall Litchfield wrote:
>
> "art" <art_teqNOSPAM_at_hotmail.com> wrote in message
> news:3ce8e945$1_1_at_nnrp1.news.uk.psi.net...
> > The fact that a process does a select/use/update on the NSN means that if
> > two processes are processing the same product then they can both SELECT
> > simultaneously and have the same NSN (which would be wrong and very bad
> for
> > the system). I want NSN to remain sequential per product, and so cannot
> use
> > an individual sequence for all products, and do not want a spearate
> sequence
> > for each product as users can add and remove products arbitrarily.
> >
> > I want a stored procedure with the following prototype which guarantees
> that
> > two processes sharing a database cannot establish the same NSN, and the
> NSN
> > remains sequential on a per product basis.
> >
> > number GetNextSequenceForProduct(product_number)
> > {
> > -- Atomically get and update the NSN for product_number
> > -- return established NSN
> > }
> >
> > I am very confused about how to do this in a process safe manner. Any
> > guidance would be much appreciated.
>
> Richard has given you what looks to be a good answer for your how do I
> achieve this question. I'd urge you if at all possble to relax the sequence
> numbers must be sequential requirement. This 'requirement' gurantees serial
> access to a resource and thus almost certainly guarantees a lack of
> scalability in the app (assuming any volume of insert activity on the
> database). No gap sequences are a Bad Thing TM.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
Received on Tue May 21 2002 - 13:15:45 CDT