Oracle RAC 和序列
我有各种使用序列的数据库应用程序，我正在将这些应用程序从没有 RAC 的 10g 迁移到带有 RAC 的 11g 的 Oracle RAC.我需要有序的序列并且可以容忍间隙.
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 的体验如何?
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?
谢谢，
推荐答案
在这种情况下，有序"到底是什么意思?
Exactly what do you mean by "ordered" in this context?
默认情况下，集群中的每个节点都有一个单独的序列号缓存.因此，节点 1 可能正在分发值 1-100，而节点 2 正在分发值 101-200.从单个节点返回的值是连续的，但节点 1 上的会话 A 可能会得到值 15，而节点 2 上的会话 B 会得到值 107，因此跨会话返回的值会出现乱序.
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.
如果您指定必须对序列进行排序，那么您基本上就违背了序列缓存的目的，因为现在每次请求新的序列值时，Oracle 都必须在节点之间进行通信.这有可能产生相当数量的性能开销.如果您将序列用作某种时间戳，那么这种开销可能是必要的，但通常并不理想.
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.
实际开销的差异将高度依赖于应用程序——对于某些应用程序来说它小得无法衡量，而对于其他应用程序来说则是一个重大问题.RAC 节点的数量、互连的速度以及互连的流量也将有所贡献.由于这主要是一个可扩展性问题，实际效果将限制您的应用程序扩展的程度，而这本质上是非线性的.将您的应用程序处理的事务量增加一倍将导致开销增加一倍以上.
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.
如果指定NOCACHE，选择ORDER还是NOORDER基本没有关系.如果指定ORDER，选择CACHE 还是NOCACHE 基本无关.所以CACHE NOORDER是目前效率最高的，其他三个相对可以互换.每次您请求序列值时，它们都将涉及节点间协调和网络流量，这显然是一个潜在的瓶颈.
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.
通常最好在表中添加一个 TIMESTAMP 列来存储实际时间戳，而不是依赖序列来提供时间戳顺序.
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.
相关文章