Re: One sequence or many sequences?

From: joel garry <>
Date: Mon, 30 Jun 2008 09:56:36 -0700 (PDT)
Message-ID: <>

On Jun 28, 4:24 pm, "gym dot scuba dot kennedy at gmail" <> wrote:
> "William Robertson" <> wrote in message
> > On Jun 27, 5:18 pm, Mark D Powell <> 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

Yes, it is as William said, the something in memory is the "high water mark" of the sequence, see metalink Note:378302.1 (just the current high value of the cache not any segment hwm thing). The performance issue comes when you write the value to sys.seq$, which happens when you hit the high value.

I would guess, without knowing anything about it, that the RAC issue comes from having the sequence in the other instance from the one being loaded, treating you to pinging problems, or whatever it is called these days when you overburden inter-instance communication.


-- is bogus.
Received on Mon Jun 30 2008 - 11:56:36 CDT

Original text of this message