Oracle 序列值未排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13513936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Sequence value are not ordered
提问by Dinesh Sachdev 108
Possible Duplicate:
Oracle RAC and sequences
可能重复：
Oracle RAC 和序列
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 ).
我在本地环境中配置了 Oracle RAC。我用Sequnce分析了一个问题，nextVal生成的数字没有排序。假设第一次我获得的值为 1 ，第二次获得的值为 21 （我已将序列配置为默认的 CACHE 20 和 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) CACHE and ORDER
1) 缓存和排序
2) NOCACHE and ORDER
2）NOCACHE和订单
I want to know which one of the above is better option and why?
我想知道以上哪一个是更好的选择，为什么？
Secondly, Can I achieve the ordering if I alter the sequence to be NOCACHE irrespective of ORDER/NOORDER.
其次，如果我将序列更改为 NOCACHE，而不管 ORDER/NOORDER，我能实现排序吗？
Thanks
谢谢
回答by DazzaL
Secondly, Can I achieve the ordering if I alter the sequence to be NOCACHE irrespective of ORDER/NOORDER.
其次，如果我将序列更改为 NOCACHE，而不管 ORDER/NOORDER，我能实现排序吗？
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.
是的，因为 NOCACHE 是有效的顺序，因为您在每个增量上都强制写入 sys.seq$ 表，这也必须在节点上进行序列化。
--
——
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.
我会在可能的重复中对接受的答案提出异议。RAC 中的 CACHE + ORDER 和 NOCACHE 存在巨大差异。你不是用 ORDER 否定 CACHE；只是降低其有效性。我个人看到中间层应用程序的性能急剧下降，因为它们在序列上使用 NOCACHE 并同时访问多个节点。我们将他们的顺序切换到 ORDER CACHE（因为他们想要一个跨种族的顺序）。和性能大大提高。
in summary: The sequence speed will be from fastest to slowest as "CACHE NOORDER"->"CACHE ORDER" and way way WAY behind "NOCACHE".
总结：序列速度将从最快到最慢，为“CACHE NOORDER”->“CACHE ORDER”，并且落后于“NOCACHE”。
This is easily testable too:
这也很容易测试：
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 with no order. Now we fire up two sessions. I'm using a 4 node RAC database 10.2.0.4 in this test:
即无顺序缓存。现在我们启动两个会话。我在此测试中使用 4 节点 RAC 数据库 10.2.0.4：
my test script is simply
我的测试脚本很简单
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;
/
/
now we run the first test (CACHE NOORDER):
现在我们运行第一个测试（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.
so 7-8 seconds to select 100,000 iterations of the sequence.
所以 7-8 秒来选择序列的 100,000 次迭代。
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).
现在让我们尝试 NOCACHE（ORDER vs NOORDER 与此无关，因为我们强制每次调用序列都写入 seq$）。
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.
so we've jumped from 8 seconds to 8 MINUTES for the same work set.
所以对于相同的工作集，我们已经从 8 秒跳到了 8 分钟。
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.
so in summary for 100,000 single call fetches CACHE NOORDER = 8 seconds NOCACHE = 8 minutes CACHE ORDER = 25 seconds
所以总而言之，100,000 次单次调用获取 CACHE NOORDER = 8 秒 NOCACHE = 8 分钟 CACHE ORDER = 25 秒
for cache order, oracle does do a lot of pinging between the RAC nodes , but it DOESNThave to write stuff back to seq$ until the cache size is used up, as its all done in memory.
对于缓存顺序，oracle 确实在 RAC 节点之间执行了大量 ping 操作，但它不必将内容写回 seq$，直到缓存大小用完为止，因为这一切都在内存中完成。
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.
如果我是你，我会设置一个适当的缓存大小（ps 高缓存大小不会给盒子内存带来负载，因为 oracle 不会将所有数字存储在 RAM 中；只有当前 + 最终数字）并考虑如果需要，请订购。
