oracle RAC 环境解决sequence 不一致问题

oracle RAC 环境解决sequence 不一致问题

===========================================================

Sequences in Oracle 10g RAC
Just recently I got a call from a developer. He had a table with a primary keypopulated by a sequence, a timestamp column with the current date and someother columns. He had a specific set of data that, when ordered by the primarykey had out of order timestamps. He was puzzled how this could be. This is aRAC database and the sequence was created with the default values.

Not only the sequences cache was thedefault of 20, but it was “noordered”. Being “noordered” Oracle will notguarantee the order in which numbers are generated.

Example of “noorder” sequence in 10gRAC:

Session 1 on node-A: nextval -> 101
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 121
Session 1 on node-B: nextval -> 122
Session 1 on node-A: nextval -> 103
Session 1 on node-A: nextval -> 104

The sequence cache is in the sharedpool, therefore sessions on the same node can share the cached entry, butsessions on different nodes cannot. I wonder why Oracle doesnt make “ordered”the default for sequences.

So I explained to the developer howsequences work in RAC and how each node has its own “cache”.

We changed the sequence to “ordered” andincreased the cache to 1000. Now selecting on either node gets the next numberas he expected. I warned him that there would be some performance implicationsdue to cluster synchronization. Him been a responsive developer, asked me whatwould be the impact, so I tested it out.

How does RAC synchronize sequences?

In Oracle 10g RAC, if you specify the “ordered” clause for a sequence, then aglobal lock is allocated by the node when you access the sequence.

This lock acquisition happens only atthe first sequence access for the node (A), and subsequent uses of the sequencedo not wait on this lock. If another node (B) selects from that sequence, itrequests the same global lock and once acquired it returns the sequences nextvalue.

The wait event associated with thisactivity is recorded as “events in waitclass Other” when looked ingv$system_event. So much for event groups, it couldnt be more obscure. Thatview shows overall statistics for the session.

However if you look in thegv$session_wait_history it shows as “DFS lock handle” with the “p1″ parameterbeen the object_id of the sequence. This second view has a sample of the last10 wait events for a session.

In a SQL_TRACE with waitevents (10046trace) it will be a “DFS lock handle” but in AWR or statspack reports it willbe “events in waitclass Other”. So much for consistency.

How does that change our example?

Session 1 on node-A: nextval -> 101(DFS Lock handle) (CR read)
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 103 (DFS Lock handle)
Session 1 on node-B: nextval -> 104
Session 1 on node-A: nextval -> 105 (DFS Lock handle)
Session 1 on node-A: nextval -> 106
(more selects)
Session 1 on node-A: nextval -> 998
Session 1 on node-B: nextval -> 999 (DFS Lock handle)
Session 1 on node-B: nextval -> 1000 (CR read)

The cache size also has some RACsynchronization implications. When the cached entries for the sequence areexhausted, the sequence object needs to be updated. This usually causes aremote CR (current read) over the interconnect for the block that has thespecific sequence object. So a bit more activity here.

Test case:

create sequence test_rac;
declare
dummy number;
begin
for i in 1..50000 loop
select test_rac.nextval into dummy from dual;
end loop;
end;
/

Results:

50 000 loops with cache = 20 (default)
1 node = 5 seconds
2 nodes at same time = 14 seconds
2 nodes at same time ordered = 30 seconds

50 000 loops with cache = 1000

1 node = 1.5 seconds
2 nodes at same time = 1.8 seconds
2 nodes at same time ordered = 20 seconds

With a smaller cache, the “noordered”still has as significant impact as every 10 fetches (cache 20 divided by 2nodes fetching) it has to synchronize between the 2 nodes

The conclusion

By default sequences in 10g RAC arecreated without ordering. Beware of using applications that rely on sequencesto be ordered and using it in a RAC environment.

Consider changing all user sequences to“ordered” as a precaution and increasing the cache size.

The default cache value is still verylow and even not-ordered sequences will cause contention in a highly-activesequence even in non-RAC and causing an additional block exchange every 20values in RAC.

For high volume insert operations whereordering is not performed on the value returned from the sequence, considerleaving the sequence “noordered” but increasing the cache size significantly.

Either way, the sequence parametersshould be reviewed, as chances are, the defaults are not what you need.

I remember reading somewhere that inOracle 9i the “ordered” clause in RAC was equivalent to “nochache”. I cantimagine how bad that would be in concurrent selects from the same sequence.

It would be interesting if someonerunning 9i RAC performs the test case and I would appreciate if you post theresults in the comments.