I believe this to be correct - at 9iR2 and before, Oracle will not allow ordered, cached sequences. In 10g, it will. From http://download-west.oracle.com/docs/cd/B19306_01/rac.102/b14197/admcon.htm under "Additional Considerations and Features for Real Application Clusters": "Sequences-If you use sequence numbers, then always use CACHE with the NOORDER option for optimal sequence number generation performance. With the CACHE option, however, you may have gaps in the sequence numbers. If your environment cannot tolerate sequence number gaps, then use the NOCACHE option or consider pre-generating the sequence numbers. If your application requires sequence number ordering but can tolerate gaps, then use CACHE and ORDER to cache and order sequence numbers in RAC. If your application requires ordered sequence numbers without gaps, then use NOCACHE and ORDER. This combination has the most negative effect on performance compared to other caching and ordering combinations." ________________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of tonions@xxxxxxxxxxxx Sent: Monday, April 24, 2006 4:32 AM To: oracle-l@xxxxxxxxxxxxx Subject: 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@xxxxxxxxxxxxxxx> "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx> Sent by: oracle-l-bounce@xxxxxxxxxxxxx 21/04/2006 16:33 Please respond to Mark.Bobak@xxxxxxxxxxxxxxx To <mwf@xxxxxxxx>, <johan.eriksson@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx> cc Subject RE: index contention in RAC Also, don't forget, for sequences in a RAC database, 'ORDERED' implies 'NOCACHE'. -- -- //www.freelists.org/webpage/oracle-l