RE: Re[2]: Sequences in OPS/RAC

All,

Just wanted to point out that 'missing' invoice numbers caused by a variety of causes (even if they were not cached), can cause problems for Accounting/Finance Depts in certain countries. Basically, the Govt looks on this as being used for 'tax avoidance', unless proved otherwise. You *can* miss uncached sequences under certain conditions when the Db restarts or a short burst of SQL causes pressure on the DD cache... Had this occur once in an Apps database and had to apply patches to undo and put back the sequence...

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: tim_at_sagelogix.com [mailto:tim_at_sagelogix.com]
>Sent: Monday, November 03, 2003 10:29 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Re[2]: Sequences in OPS/RAC
>
>
>The problem is that the ORDER clause comes at the expense of
>CACHE. You can use SQL tracing to verify that each use of
>the sequence causes an update of SYS.SEQ$ when ORDER is set,
>effectively rendering the CACHE setting a no-op. So,
>especially in an OPS/RAC environment, the use of ORDERED
>sequences, especially heavily used ORDERED sequences, comes
>at a steep price.
>
>Think about it: is ORDERED *really* necessary? In some
>situations (i.e. check numbers), the ORDERED clause would be
>necessary, but unless you are pumping out thousands of
>checks an hour, perhaps a cached sequence shouldn't be used.
> But for system-generated keys, surrogate keys, etc, I don't
>think the semantics of ORDERED are necessary at all.
>
>
>
>> Hi,
>>
>> I have RAC and I always use ORDER when I create SEQUENCE.
>> The following information is from Oracle Manual:
>> ORDER is necessary only to guarantee ordered generation if
>> you are using Oracle with Real Application Clusters. If
>> you are using exclusive mode, sequence numbers are always
>> generated in order.
>> Muqthar Ahmed
>>
>> -----Original Message-----
>> Sent: Monday, November 03, 2003 12:04 PM
>> To: Multiple recipients of list ORACLE-L
>>
>>
>> Hello Hemant,
>>
>> Monday, November 3, 2003, 11:29:26 AM, you wrote:
>> HKC> However, the Builder.Com article quite explicity
>> asserts HKC> "Sequence generator numbers are guaranteed to
>> be unique only for a single HKC> instance, which is
>> unsuitable for use as a primary key in parallel or HKC>
>> remote environments, where a sequence in each environment
>> might generate HKC> the same number and result in
>> conflicts
>> Can you point us to the article? My guess is that the
>> author is not familiar with Oracle, and is basing the
>> above statement on his experience with some other database
>> (DB2 perhaps?). There is no problem with using sequence
>> numbers in a RAC. No conflicts will occur. I've never
>> heard of a problem in that regard.
>>
>> Best regards,
>>
>> Jonathan Gennick --- Brighten the corner where you are
>> http://Gennick.com * 906.387.1698 *
>> mailto:jonathan_at_gennick.com
>> Join the Oracle-article list and receive one
>> article on Oracle technologies per month by
>> email. To join, visit
>> http://four.pairlist.net/mailman/listinfo/oracle-article,
>> or send email to Oracle-article-request_at_gennick.com and
>> include the word "subscribe" in either the subject or
>> body.
>> --
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.net --
>> Author: Jonathan Gennick
>> INET: jonathan_at_gennick.com
>>
>> Fat City Network Services -- 858-538-5051
>> http://www.fatcity.com San Diego, California --
>> Mailing list and web hosting services
>> ----------------------------------------------------------
>> ----------- 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). --
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.net --
>> Author: Muqthar Ahmed
>> INET: Muqthar.Ahmed_at_decoratetoday.com
>>
>> Fat City Network Services -- 858-538-5051
>> http://www.fatcity.com San Diego, California --
>> Mailing list and web hosting services
>> ----------------------------------------------------------
>> ----------- 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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author:
> INET: tim_at_sagelogix.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).