RE: index contention in RAC

  • From: tonions@xxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 24 Apr 2006 12:31:59 +0100

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

This is what I got that impression from:


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
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â

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

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
If you are not the intended recipient of this e-mail, promptly delete it
and all attachments.

             "Bobak, Mark"                                                 
   >                                                To 
             Sent by:                  <mwf@xxxxxxxx>,                     
             oracle-l-bounce@f         <johan.eriksson@xxxxxxxxxxxx>,      
             21/04/2006 16:33                                      Subject 
                                       RE: index contention in RAC         
             Please respond to                                             

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


GIF image

GIF image

GIF image

GIF image

Other related posts: