Re: OPS Sequences: nocache == order ??

From: Thomas Day <tday6_at_csc.com>
Date: Wed, 04 Sep 2002 06:28:25 -0800
Message-ID: <F001.004C7568.20020904062825@fatcity.com>

A day late and a dollar short but here's my $.02

Order will give you the temporal sequencing. Nocache should but it's not certain.

Cached numbers are stored in the SYSTEM tablespace and can be retrieved in an atemporal order. I can't give you any specifics, but that's what Oracle says. Nocached numbers are generated at call time but that doesn't mean that they'll be stored in the database in temporal order. Order means that the number will be generated and stored in temporal order. As you can guess, this slows things up a bit. You will almost certainly see an increase in locking with ordered sequences. It can also happen with nocache.

We recently went through an exercise of looking at every sequence in our database, about 400 altogether, to see if they needed to be ordered and cached. None of them needed to be ordered. Your requirement is unusual. If the sequence was being hit once an hour or so we decided to nocache it
(save churning the SYSTEM tablespace). But our defaults are cache and
noorder.

HTH

                                                                                                                   
                    "Gogala,                                                                                       
                    Mladen"              To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    <MGogala             cc:                                                                       
                    @oxhp.com>           Subject:     OPS Sequences: nocache == order ??                           
                    Sent by: root                                                                                  
                                                                                                                   
                                                                                                                   
                    09/03/2002                                                                                     
                    06:00 PM                                                                                       
                    Please                                                                                         
                    respond to                                                                                     
                    ORACLE-L

I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1)
and I'm having an application dependency on a temporal order of sequence numbers.
With OPS that becomes a problem because each node caches a set of sequence numbers
(20 by default). Oracle has an option, specifically for that situation,
namely "ORDER".
My question is whether ORDER is the same thing as NOCACHE and whether it is possible
to have a NOCACHE sequence which will return numbers in an incorrect order
(larger number

before the smaller one).
Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you.
Mladen Gogala

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas Day
  INET: tday6_at_csc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Wed Sep 04 2002 - 09:28:25 CDT