| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: index contention in RAC
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."
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.proquest.com>
"Bobak, Mark" <Mark.Bobak_at_il.proquest.com>
Sent by: oracle-l-bounce_at_freelists.org
21/04/2006 16:33
Please respond to
Mark.Bobak_at_il.proquest.com
To
<mwf_at_rsiz.com>, <johan.eriksson_at_bossmedia.se>, <oracle-l_at_freelists.org>
cc
Subject
RE: index contention in RAC
Also, don't forget, for sequences in a RAC database, 'ORDERED' implies 'NOCACHE'.
-- -- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 24 2006 - 15:10:00 CDT
 |
 |