Oracle Sequence值未排序 [英] Oracle Sequence value are not ordered
问题描述
可能重复:
Oracle RAC和序列
Possible Duplicate:
Oracle RAC and sequences
我在本地环境中配置了Oracle RAC.我分析了Sequnce的问题，即nextVal生成的数字未排序.假设第一次我得到的值是1，第二次我得到的值是21(我将序列配置为默认的CACHE 20和NOORDER).
I have a Oracle RAC configured in my local environment. I analyzed a problem with Sequnce that the number generated by nextVal are not ordered. Suppose First time I get value as 1 , the second time get get value as 21 (I have configured the sequence as with default CACHE 20 and NOORDER ).
在搜索中，我找到了解决方案，我需要订购该序列.我有一个疑问，哪个是更好的选择，
On searching I found the solution that, I need to Order the sequence. I have question which is better option to go with,
1)缓存和订购
2)NOCACHE和ORDER
2) NOCACHE and ORDER
我想知道以上哪个是更好的选择，为什么?
I want to know which one of the above is better option and why?
第二，如果不考虑ORDER/NOORDER而将序列更改为NOCACHE，是否可以实现排序?
Secondly, Can I achieve the ordering if I alter the sequence to be NOCACHE irrespective of ORDER/NOORDER.
谢谢
推荐答案
第二，如果我将顺序更改为 不管订购还是订购，都可以使用NOCACHE.
Secondly, Can I achieve the ordering if I alter the sequence to be NOCACHE irrespective of ORDER/NOORDER.
是的，因为您必须在每次增量上强制写入sys.seq $表，所以NOCACHE是有效的顺序，因此也必须在节点上进行序列化.
yes as NOCACHE is effectively order as you're forcing a write to the sys.seq$ table on each increment, which has to serialise over nodes too.
-
在可能的重复中，我会接受已接受的答案. RAC中的CACHE + ORDER和NOCACHE有很大的不同.您不是在用ORDER否定CACHE；只是降低其有效性.我个人看到中间层应用程序的性能急剧下降，因为他们在序列上使用NOCACHE并同时在多个节点上进行访问.我们将他们的顺序切换为ORDER CACHE(因为他们想要一个跨种族的订单).并大大提高了性能.
I would dispute the accepted answer in that possible duplicate. there is a huge difference in CACHE + ORDER and NOCACHE in RAC. You are not negating the CACHE with ORDER; just reducing its effectiveness. I've personally seen performance of a middle tier application degrade drastically as they used NOCACHE on a sequence and were accessing on multiple nodes at once. We switched their sequence to ORDER CACHE (as they wanted an cross-rac order). and performance drastically improved.
总结:顺序速度从最快到最慢为"CACHE NOORDER"->"CACHE ORDER"，以及"NOCACHE"后面的方式.
in summary: The sequence speed will be from fastest to slowest as "CACHE NOORDER"->"CACHE ORDER" and way way WAY behind "NOCACHE".
这也很容易测试:
所以我们从一个标准序列开始:
So we start with a standard sequence:
SQL> create sequence daz_test start with 1 increment by 1 cache 100 noorder;
Sequence created.
ie CACHE，无订单.现在，我们启动两个会话.在此测试中，我使用的是4节点RAC数据库10.2.0.4:
ie CACHE with no order. Now we fire up two sessions. I'm using a 4 node RAC database 10.2.0.4 in this test:
我的测试脚本很简单
select instance_number from v$instance;
set serverout on
declare
v_timer timestamp with time zone := systimestamp;
v_num number(22);
begin
for idx in 1..100000
loop
select daz_test.nextval into v_num from dual;
end loop;
dbms_output.put_line(systimestamp - v_timer);
end;
/
/
现在我们运行第一个测试(CACHE NOORDER):
now we run the first test (CACHE NOORDER):
SESSION 1 SESSION 2
SQL> @run_test SQL> @run_test
INSTANCE_NUMBER INSTANCE_NUMBER
--------------- ---------------
2 1
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
SQL> @run_test SQL> @run_test
INSTANCE_NUMBER INSTANCE_NUMBER
--------------- ---------------
2 1
+000000000 00:00:07.309916000 +000000000 00:00:07.966913000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
+000000000 00:00:08.430094000 +000000000 00:00:07.341760000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
大约7-8秒即可选择该序列的100,000次迭代.
so 7-8 seconds to select 100,000 iterations of the sequence.
现在让我们尝试NOCACHE(对此，ORDER与NOORDER是无关紧要的，因为我们在每次调用该序列时都强制对seq $进行写操作).
Now lets try NOCACHE (ORDER vs NOORDER is irrelavant for this, as we are forcing a write to seq$ for every call to the sequence).
SQL> alter sequence daz_test nocache;
Sequence altered.
SESSION 1 SESSION 2
SQL> @run_test SQL> @run_test
INSTANCE_NUMBER INSTANCE_NUMBER
--------------- ---------------
2 1
+000000000 00:08:20.040064000 +000000000 00:08:15.227200000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
+000000000 00:08:30.140277000 +000000000 00:08:35.063616000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
因此，对于相同的工作集，我们已经从8秒跳到8分钟.
so we've jumped from 8 seconds to 8 MINUTES for the same work set.
关于CACHE + ORDER呢?
what about CACHE + ORDER?
SQL> alter sequence daz_test cache 100 order;
Sequence altered.
SQL> @run_test SQL> @run_test
INSTANCE_NUMBER INSTANCE_NUMBER
--------------- ---------------
2 1
+000000000 00:00:25.549392000 +000000000 00:00:26.157107000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
+000000000 00:00:26.057346000 +000000000 00:00:25.919005000
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
因此，总共有100,000次单次调用提取 CACHE NOORDER = 8秒 NOCACHE = 8分钟 缓存订单= 25秒
so in summary for 100,000 single call fetches CACHE NOORDER = 8 seconds NOCACHE = 8 minutes CACHE ORDER = 25 seconds
对于缓存顺序，oracle确实在RAC节点之间执行了许多ping操作，但是 DOESNT 必须将内容写回到seq $，直到缓存大小用尽为止，因为所有操作都在记忆.
for cache order, oracle does do a lot of pinging between the RAC nodes , but it DOESNT have to write stuff back to seq$ until the cache size is used up, as its all done in memory.
如果我是您，我会设置一个适当的缓存大小(ps较高的缓存大小不会给存储盒内存带来负担，因为oracle不会将所有数字存储在RAM中；仅是当前+最终数字) )，并在需要时考虑订购.
i would if i were you, set an appropriate cache size (p.s. a high cache size doesn't put a load on the box memory, as oracle doesn't store all the numbers in RAM; only the current + final number) and consider ORDER if required.
这篇关于Oracle Sequence值未排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
