RE: index contention in RAC

I was under the impression that this "limitation" had been lifted in (since 9iR2) - ie you can have ORDERED/CACHE and Oracle honours the setting. I did some trials a while back and satisfied myself at the time, maybe I missed something.

This is what I got that impression from:

from http://www.dizwell.com/html/autonumbering.html:

In a Real Application Clusters environment, Fred could request a sequence number from Instance 1
at 10.00am, and Barney request one on Instance 2 at 10.01am -yet, because of the way RAC works, you
might find that Fred gets sequence 20, and Barney gets sequence 10. If you specify “order” in a RAC,
however, then it is guaranteed that Fred would get sequence 10 and Barney would bring up the rear
with sequence 20.

Note that until 9i Release 2, you could not specify both the “cache” and “order” options.
Well, you could syntactically -but Oracle would just silently ignore the “order” clause!
If you truly wanted to guarantee the order of allocations in 9i Release 1, you had to explicitly
specify the “nocache” clause -and that could cause mammoth performance problems.
In 9i Release 2, however, it becomes possible to specify both clauses and have both acted upon.
Performance of a cached and ordered sequence in RAC is still not as good as a cached and nordered one,
because of the cross-instance co-ordination that has to take place to guarantee the “ordered” bit
of the syntax. But it’s still better than, for example, a “nocached” sequence.

eg
alter sequence MT_JOB_HISTORY_ID_SEQ cache 10 order;

Now when you select from each node in turn you get the sequence you desire.

Tim Onions
Head Of Oracle Development
Phone: +44 (0) 1684 312364 ext. 364
Cell: +44 (0) 7736 634556
www.medquist.com

This electronic mail transmission contains confidential information intended only for the person(s) named.
Any use, distribution, copying or disclosure by another person is strictly prohibited.
If you are not the intended recipient of this e-mail, promptly delete it and all attachments.

                                                                           
             "Bobak, Mark"                                                 
             <Mark.Bobak_at_il.pr                                             
             oquest.com>                                                To 
             Sent by:                  <mwf_at_rsiz.com>,                     
             oracle-l-bounce_at_f         <johan.eriksson_at_bossmedia.se>,      
             reelists.org              <oracle-l_at_freelists.org>            
                                                                        cc 
                                                                           
             21/04/2006 16:33                                      Subject 
                                       RE: index contention in RAC         
                                                                           
             Please respond to                                             
             Mark.Bobak_at_il.pro                                             
                 quest.com                                                 
                                                                           
                                                                           

Also, don't forget, for sequences in a RAC database, 'ORDERED' implies 'NOCACHE'. --

--
http://www.freelists.org/webpage/oracle-l