Re: Sequence Numbers Fast ?

I have just added a Word document on sequences to my Web-site. There may be something there to help you.

--

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

Phil Maechling wrote in message <7sga6c$6u0_at_gap.cco.caltech.edu>...
>I have an application that uses the Oracle sequence number feature.
>We call nextval(dual) to get a sequence number.
>
>However measurements show that getting sequence numbers is very
>slow. To get 80 sequence numbers, it is taking 20 seconds.
>
>I've tried raising the cache on the sequence number involved, but
>that doesn't seem to improve it to as much as I need.
>
>Is there any way to get sequence numbers out of the system faster
>than calling nextval.
>
>Are any of the following ideas worth pursuing ?
>
>* Even bigger cache (its currently set to 2xMAX number of entries
> I expect (1000))
>
>* Is there some what to ask for a group of numbers at a time ? Currently I
> have to call nextval 300 times if I need 300 numbers.
>
>* Implement a stored procedure which can retrieve a set of numbers
> on a single call. If this procedure is based on calling nextval,
> it will still need to call nextval 300 times, but it may be
> faster than 300 application program calls.
>
>Any ideas, comments or suggestions would be appreciated.
>
>Thanks,
>Phil Maechling
>phil_at_gps.caltech.edu
Received on Fri Sep 24 1999 - 13:01:37 CDT