oracle sequence order,sequence的cache和order

Sequence虽然一直有用过，但是对于其内部体系和所伴随的rac等系统问题还是有所欠缺，序列的创建和使用可以说是非常基础的问题，其中的cache和order可以具体分析一下所带来的性能影响。

默认创建序列时只会cache 20，而这个数值对于数据的并发量比较大时cache 20往往是完全不够的，就跟oracle 10g 11g默认的提供的50M的3组redo而言，往往就会对系统的性能造成较大的压力。

Cache的测试：

SQL> create sequence seq01

2start with 1

3increment by 1

4maxvalue 999999

5nocache

6;

Sequence created

Executed in 0.063 seconds

SQL>

SQL> declare

2v_time number;

3begin

4for i in 1..50000 loop

5select seq01.nextval into v_time from dual;

6end loop;

7end;

8/

PL/SQL procedure successfully completed

Executed in 37.893 seconds

SQL> drop sequence seq01;

Sequence dropped

Executed in 0.062 seconds

SQL> create sequence seq01

2start with 1

3increment by 1

4maxvalue 999999

5;

Sequence created

Executed in 0 seconds

SQL> declare

2v_time number;

3begin

4for i in 1..50000 loop

5select seq01.nextval into v_time from dual;

6end loop;

7end;

8/

PL/SQL procedure successfully completed

Executed in 4.165 seconds

SQL> drop sequence seq01;

Sequence dropped

Executed in 0.062 seconds

SQL>

SQL> create sequence seq01

2start with 1

3increment by 1

4maxvalue 999999

5cache 10000;

Sequence created

Executed in 0.062 seconds

SQL> declare

2v_time number;

3begin

4for i in 1..50000 loop

5select seq01.nextval into v_time from dual;

6end loop;

7end;

8/

PL/SQL procedure successfully completed

Executed in 2.043 seconds

Cache对于数据获取量比较大的系统还是可有有效的降低资源的竞争，但是跳号无法避免。

Cache的跳号：

Cache在rac和单实例下如果实例或者数据库意外关闭，往往就会造成cache意外丢失，出现所谓的sequence跳号。

单实例下模拟跳号很简单，只需要shutdown abort数据库意外关闭然后再次startup发现cache段被跳过了，这里就不列举了。

下面来模拟rac的cache和order的一些例子。

创建sequence的script是：

Createsequencesequ001

Start with 1

Increment by 1

Maxvalue 9999

Cache

Noorder;

[oracle@rac101 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 3 15:00:26 2012

Copyright (c) 1982, 2005, Oracle.All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select sequ001.nextval,instance_name from dual,v$instance;

NEXTVAL INSTANCE_NAME

---------- ----------------

1007 benguo1

Rac102节点

SQL> select sequ001.nextval,instance_name from dual,v$instance;

NEXTVAL INSTANCE_NAME

---------- ----------------

7 benguo2

Rac环境下可以kill掉一个节点rac102来模拟所谓的实例意外关闭的cache sequence的跳号

[oracle@rac102 ~]$ ps -ef|grep oracle

oracle40210 Jul02 ?00:00:00 ora_q001_benguo2

root576356340 15:00 pts/100:00:00 su – oracle

skip...

oracle3173710 Jul02 ?00:00:01 ora_pmon_benguo2

oracle3173910 Jul02 ?00:00:00 ora_diag_benguo2

...

[oracle@rac102 ~]$ kill -9 31737

Srvctl start instance –d benguo –I benguo2用srvctl来启动实例

SQL> select sequ001.nextval,instance_name from dual,v$instance;

NEXTVAL INSTANCE_NAME

---------- ----------------

2001 benguo2

Rac的跳号出现了。Rac环境下各节点各cache相应的sequence数值。

order是看node取数值是否按照sequence的数值顺序而来，noorder的cache下node取数据只会按照各node节点的cache来取数据。

如果是cache下的order，单实例下没有影响，而rac下多实例缓存相同的sequence，如果order的取大量sequence则会出现短暂的资源竞争(由于资源需要在多实例间传递)，性能要比noorder差很多。

尤其注意nocache order的sequence，即对于sequence大量征用，还需要在实例间传递竞争资源，严重的甚至导致系统直接hang住，对于rac的环境需要通过cache fusion和序列的机制认真分析来找到性能瓶颈的根本原因。

Rac的sequence伴随着等待事件enq：SQ – contention和row cache lock，row cache locks一般是nocache下的获取library cache的sequence导致，而SQ-contention多半是由于一下子获得cache的sequence数据而至，其中的parameter2 object也就是对应于dba_Objects视图。

SQL> col parameter1 for a10

SQL> col parameter2 for a10

SQL> col parameter3 for a10

SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name like '%enq: SQ%'

2;

NAMEPARAMETER1 PARAMETER2 PARAMETER3

------------------------------ ---------- ---------- ----------

enq: SQ - contentionname|modeobject #0

sequence的等待事件摘要http://blog.csdn.net/tianlesoftware/article/details/6534886

[@more@]