Re: One sequence or many sequences?
Date: Sat, 28 Jun 2008 23:24:31 GMT
"William Robertson" <williamr2019_at_googlemail.com> wrote in message
> 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.
Jim Received on Sat Jun 28 2008 - 18:24:31 CDT