Oracle RAC and sequences

I have various database applica开发者_高级运维tions 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.

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?

Thanks,

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.

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.

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.

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.

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.

Summary

CACHE can significantly improve the performance of a sequence that uses ORDER, even on 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.

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.

I also ran the tests concurrently, on different nodes. The CACHE still significantly improves ORDER, although the CACHE NOORDER is more than twice as fast as 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 CACHE would make so much of a difference when ORDER is 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.)

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.

Sequences are not designed to be ordered with meaning. Check out this link to a response by Tom Kyte and some of his followup responses in the same thread.