oracle RAC 环境解决quence 不一致问题 amg是什么意思
===========================================================
Sequences in Oracle 10g RAC
Just recently I got a call from a developer. He had a table with a primary key populated by a quence, a timestamp column with the current date and some other columns. He had a specific t of data that, when ordered by the primary key had out of order timestamps. He was puzzled how this could be. This is a RAC databa and the quence was created with the default values.
Not only the quences cache was the default of 20, but it was “noordered”. Being “noordered” Oracle will not guarantee the order in which numbers are generated. 歌舞青春 歌曲
Example of “noorder” quence in 10g RAC:
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 quence cache is in the shared pool, therefore ssions on the same node can share the cached entry, but ssions on different nodes cannot. I wonder why Oracle doesnt make “ordered” the default for quences.
So I explained to the developer how quences work in RAC and how each node has its own “cache”.
We changed the quence to “ordered” and incread the cache to 1000. Now lecting on either node gets the next number as he expected. I warned him that there would be some performance implications due to cluster synchronization. Him been a responsive developer, asked me what would be the impact, so I tested it out.
How does RAC synchronize quences?
In Oracle 10g RAC, if you specify the “ordered” clau for a quence, then a global lock is allocated by the node when you access the quence.
This lock acquisition happens only at the first quence access for the node (A), and subquent us of the quence do not wait on this lock. If another node (B) lects from that quence, it requests the same global lock and once acquired it returns the quences next value.
The wait event associated with this activity is recorded as “events in waitclass Other” when looked in gv$system_event. So much for event groups, it couldnt be more obscure. That view shows overall statistics for the ssion.
However if you look in the gv$ssion_wait_history it shows as “DFS lock handle” with the “p1″ parameter been the object_id of the quence. This cond view has a sample of the last 10 wait events for a ssion.
In a SQL_TRACE with waitevents (10046 trace) it will be a “DFS lock handle” but in AWR or statspack reports it will be “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 -> 102cellox
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
smell(more lects)
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 RAC synchronization implications. When the cached entri
es for the quence are exhausted, the quence object needs to be updated. This usually caus a remote CR (current read) over the interconnect for the block that has the specific quence object. So a bit more activity here.
Test ca:
create quence test_rac;
declare
dummy number;
begin大学生英语四级成绩查询>好奇心英文
for i in 1..50000 loop
lect val into dummy from dual;
end loop;
end;back out
/
Results:
50 000 loops with cache = 20 (default)英语四级成绩
1 node = 5 conds
2 nodes at same time = 14 conds
2 nodes at same time ordered = 30 conds
50 000 loops with cache = 1000
1 node = 1.5 conds
2 nodes at same time = 1.8 conds
少儿英语课程顾问2 nodes at same time ordered = 20 conds
俄语翻译机With a smaller cache, the “noordered” still has as significant impact as every 10 fetches (cache 20 divided by 2 nodes fetching) it has to synchronize between the 2 nodes
The conclusion
By default quences in 10g RAC are created without ordering. Beware of using applications that rely on quences to be ordered and using it in a RAC environment.
Consider changing all ur quences to “ordered” as a precaution and increasing the cache size.
The default cache value is still very low and even not-ordered quences will cau contention in a highly-active quence even in non-RAC and causing an additional block exchange every 20 values in RAC.
