Oracle RAC和序列 [英] Oracle RAC and sequences

问题描述

我有各种使用序列的数据库应用程序，我正在将这些应用程序从无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.

这篇关于Oracle RAC和序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！