Oracle 序列值未排序

2021-12-30 00:00:00 oracle11g oracle oracle10g

可能的重复:
Oracle RAC 和序列

我在本地环境中配置了 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?

其次,如果我将序列更改为 NOCACHE,而不管 ORDER/NOORDER 是否可以实现排序.

Secondly, Can I achieve the ordering if I alter the sequence to be NOCACHE irrespective of ORDER/NOORDER.

谢谢

推荐答案

其次,如果我改变顺序,我可以实现排序吗?NOCACHE 与 ORDER/NOORDER 无关.

Secondly, Can I achieve the ordering if I alter the sequence to be NOCACHE irrespective of ORDER/NOORDER.

是的,因为 NOCACHE 是有效排序的,因为您在每次增量时都强制写入 sys.seq$ 表,这也必须在节点上进行序列化.

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.

即没有顺序的缓存.现在我们启动两个会话.我在这个测试中使用了一个 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 vs 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.

缓存+订单怎么样?

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 次调用提取缓存 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 中;只有当前 + 最终数字) 并在需要时考虑 ORDER.

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.

相关文章