Sequence Numbers Fast ?

From: Phil Maechling <phil_at_nickel.gps.caltech.edu>
Date: 24 Sep 1999 16:51:24 GMT
Message-ID: <7sga6c$6u0@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 - 11:51:24 CDT