Oracle RAC 和序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4866959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle RAC and sequences
提问by odew
I have various database applications that use sequences, I′m migrating these applications to Oracle RAC from 10g without RAC to 11g with RAC. I need ordered sequences and gaps are tolerated.
我有各种使用序列的数据库应用程序，我正在将这些应用程序从没有 RAC 的 10g 迁移到带有 RAC 的 11g 的 Oracle RAC。我需要有序的序列并且可以容忍间隙。
I'm thinking in cache sequences with order, I don′t know what are the effect in performance. Do you think this is a good option? What are your experience with sequences and RAC?
我正在考虑有序的缓存序列，我不知道对性能有什么影响。您认为这是一个不错的选择吗？您对序列和 RAC 的体验如何？
Thanks,
谢谢，
回答by Justin Cave
Exactly what do you mean by "ordered" in this context?
在这种情况下，“有序”究竟是什么意思？
By default, each node in the cluster has a separate cache of sequence numbers. So node 1 may be handing out values 1-100 while node 2 is handing out values 101-200. The values returned from a single node are sequential, but session A on node 1 may get a value of 15 while session B on node 2 gets a value of 107 so the values returned across sessions appear out of order.
默认情况下，集群中的每个节点都有一个单独的序列号缓存。因此，节点 1 可能正在分发值 1-100，而节点 2 正在分发值 101-200。从单个节点返回的值是连续的，但是节点 1 上的会话 A 可能会得到值 15，而节点 2 上的会话 B 会得到值 107，因此跨会话返回的值似乎是乱序的。
If you specify that the sequence has to be ordered, you're basically defeating the purpose of the sequence cache because Oracle now has to communicate among nodes every time you request a new sequence value. That has the potential to create a decent amount of performance overhead. If you're using the sequence as a sort of timestamp, that overhead may be necessary but it's not generally desirable.
如果您指定必须对序列进行排序，那么您基本上就违背了序列缓存的目的，因为现在每次请求新的序列值时，Oracle 都必须在节点之间进行通信。这有可能产生相当数量的性能开销。如果您将序列用作一种时间戳，则可能需要这种开销，但通常并不理想。
The overhead difference in practical terms is going to be highly application dependent-- it will be unmeasurably small for some applications and a significant problem for others. The number of RAC nodes, the speed of the interconnect, and how much interconnect traffic there is will also contribute. And since this is primarily a scalability issue, the practical effect is going to limit how well your application scales up which is inherently non-linear. Doubling the transaction volume your application handles is going to far more than double the overhead.
实际上，开销差异将高度依赖于应用程序——它对于某些应用程序来说小得无法衡量，而对于其他应用程序来说则是一个重大问题。RAC 节点的数量、互连的速度以及互连的流量也将有所贡献。由于这主要是一个可扩展性问题，实际效果将限制您的应用程序扩展的程度，而这本质上是非线性的。将您的应用程序处理的事务量加倍将远远超过两倍的开销。
If you specify NOCACHE, the choice of ORDER or NOORDER is basically irrelevent. If you specify ORDER, the choice of CACHE or NOCACHE is basically irrelevent. So CACHE NOORDER is by far the most efficient, the other three are relatively interchangable. They are all going to involve inter-node coordination and network traffic every time you request a sequence value which is, obviously, a potential bottleneck.
如果指定NOCACHE，选择ORDER 或NOORDER 基本无关。如果指定ORDER，选择CACHE 还是NOCACHE 基本无关。所以CACHE NOORDER是目前效率最高的，其他三个相对可以互换。每次您请求序列值时，它们都将涉及节点间协调和网络流量，这显然是一个潜在的瓶颈。
It would generally be preferrable to add a TIMESTAMP column to the table to store the actual timestamp rather than relying on the sequence to provide a timestamp order.
通常最好将 TIMESTAMP 列添加到表中以存储实际时间戳，而不是依赖序列来提供时间戳顺序。
回答by Jon Heller
Summary
概括
CACHEcan significantly improve the performance of a sequence that uses ORDER, even on RAC.
CACHE可以显着提高使用 的序列的性能ORDER，即使在 RAC 上也是如此。
It's still not as fast as NOORDER, but it can be surprisingly close. Especially if the sequence is only used on one of the nodes at a time.
它仍然不如 快NOORDER，但可以惊人地接近。特别是如果该序列一次仅在其中一个节点上使用。
Test Case
测试用例
SQL> create sequence cache_order cache 20 order;
Sequence created.
SQL> create sequence cache_noorder cache 20 noorder;
Sequence created.
SQL> create sequence nocache_order nocache order;
Sequence created.
SQL> create sequence nocache_noorder nocache noorder;
Sequence created.
SQL> set timing on
SQL> declare
2 v_temp number;
3 begin
4 for i in 1 .. 100000 loop
5 v_temp := cache_order.nextval;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.44
SQL> declare
2 v_temp number;
3 begin
4 for i in 1 .. 100000 loop
5 v_temp := cache_noorder.nextval;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.46
SQL> declare
2 v_temp number;
3 begin
4 for i in 1 .. 100000 loop
5 v_temp := nocache_order.nextval;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:35.15
SQL> declare
2 v_temp number;
3 begin
4 for i in 1 .. 100000 loop
5 v_temp := nocache_noorder.nextval;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:35.10
Test Case Notes
测试用例说明
My results were obtained on a 2-node RAC. Only one result set is shown, but I ran the test case multiple times, on different databases, and obtained almost identical results.
我的结果是在 2 节点 RAC 上获得的。只显示了一个结果集，但我在不同的数据库上多次运行测试用例，并获得几乎相同的结果。
I also ran the tests concurrently, on different nodes. The CACHEstill significantly improves ORDER, although the CACHE NOORDERis more than twice as fast as CACHE ORDER.
我还在不同的节点上同时运行了测试。在CACHE仍然显著改善ORDER，虽然CACHE NOORDER是多快两倍CACHE ORDER。
I've also noticed similar behavior in other environments in the past, although I do not have any results for them.
过去我也注意到其他环境中的类似行为，尽管我没有任何结果。
Why?
为什么？
I don't understand why CACHEwould make so much of a difference when ORDERis used. The amount of time to generate a number should be irrelevant compared to the time to send data over a network. This makes me think that either Oracle is using a poor algorithm, or my test case is wrong. (If anyone can find a problem with my test case, please let me know.)
我不明白为什么CACHE在ORDER使用时会产生如此大的差异。与通过网络发送数据的时间相比，生成数字的时间量应该无关紧要。这让我觉得要么是 Oracle 使用了糟糕的算法，要么是我的测试用例是错误的。（如果有人能发现我的测试用例有问题，请告诉我。）
Also, this answer only discusses the time to generate the sequence. There may be other benefits of using NOORDER. For example, reduced index contention, as described here.
此外，此答案仅讨论生成序列的时间。使用NOORDER. 例如，减少的索引争用，如所描述这里。
回答by Doug Porter
Sequences are not designed to be ordered with meaning. Check out this link to a response by Tom Kyteand some of his followup responses in the same thread.
序列不是按意义排序的。查看Tom Kyte 回复的链接以及他在同一线程中的一些后续回复。
