Re: One sequence or many sequences?

"William Robertson" <williamr2019_at_googlemail.com> wrote in message news:8c969cca-b58f-478b-8f0b-966f26b8e2f4_at_y21g2000hsf.googlegroups.com...
> On Jun 27, 5:18 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>> Otherwise the low use sequences are flushed from the sequence pool before
>> reuse and more
>> numbers are thrown away by the flushing than are used.
>
> There's a sequence pool?
>
> The performance will depend on the number of updates of sys.seq$. If
> you expect a single sequence to get a lot of use you might want to
> increase the cache size. The term "cache" does not mean there is a
> list of numbers actually held in memory somewhere.

Actually I think what Mark means is that if you define a sequence with a cache size of lets say 10,000 then if you flush the shared pool or if the sequence gets flushed from the shared pool then you "throw away" the 10,000 cached sequences. I had a sequence that I had defined with a cache size of 1,000 and the test system had a small SGA. QA was upset that if they inserted a bunch of records waited 15 minutes and then inserted more records that the numbers "jumped" by 1,000. (they thought something was wrong with the code. The number didn't have any meaning, it purely arbitrary.) I had to explain that this can happen when the sequence is aged out. (or if you took the database down) So something is in memory. I have run tests where I loaded rows via sql loader and if the sequence had a cache of 10 it loaded a lot slower than a cache of 10,000 on a 300,000 record load. (minutes vs seconds) You can really see the impact on a RAC system if you specify nocache ordered. (ugh) You can almost see each record get inserted. (figuratively)
Jim Received on Sat Jun 28 2008 - 18:24:31 CDT