数据库性能优化3——Oracle SEQUENCE的概念、作用以及创建(使得并发插入主键唯一)

1. Oracle Sequence的概念

Oracle使用序列来生成唯一编号，而不是使用SQLServer所用的数据类型uniqueidentifier。无论是哪种情况，主要用途都是为主键列生成一系列唯一编号。与uniqueidentifier数据类型不同，序列是与将其用于主键值的一个或多个表无关的数据库对象。

Oracle序列是原子对象，并且是一致的。也就是说，一旦您访问一个序列号，Oracle将在处理下一个请求之前自动递增下一个编号，从而确保不会出现重复值。

2. Oracle Sequence的作用

sequence是用来在多用户环境下产生唯一整数的数据库对象。序列产生器顺序生成数字，它可用于自动生成主键值，并能协调多行或者多表的主键操作。没有sequence，顺序的值只能靠编写程序来生成。先找出最近产生的值然后自增长。这种方法要求一个事务级别的锁，这将导致在多用户并发操作的环境下，必须有人等待下一个主键值的产生。而且此方法很容易产生主键冲突的错误，如下图：
time a......trans1 begin.........................................................
|
取max value=5
|
time b......max value+1=6........trans2 begin.....................
| |
other actionmax value=5
| |
time c.....commit;...................max value+1=6................
|
commt(ora-00001)

如上图，事务2会报主键冲突的错误，而再刷新一下页面（再执行一边程序），可能就正常了。
还有一个问题，那就是完成生成主键的程序（一般情况包含plsql块）本身对于并发调用也是一个瓶颈，因为这样的程序段往往是提供给好多程序去调用，如果代码端写的不够优化（比如没有使用邦定变量等等），或者此代码段存在问题，那么它所影响的是系统的全局。我们应该提倡开发人员使用sequence。sequence消除了序列化问题，而且改善了应用的并发能力。

创建sequence

sequence的命名最重要的是要统一，命名规则是次要的。可以使用CREATESEQUENCE命令创建Oracle序列。该命令所带参数包括增量、起始值、最大值、循环和缓存。可使用NEXTVAL和CURRVAL关键字访问序列值。NEXTVAL返回序列中的下一个编号，而CURRVAL提供对当前值的访问。
CREATE SEQUENCE emp_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10

NOORDER;

大多数序列代码是不言自明的。NOCYCLE表示序列在达到最小值或最大值后将不再生成其他值。NOCACHE表示序列值在被请求之前不会进行分配；可使用预分配机制来改善性能。NOORDER表示在生成编号时，不能保证按照请求编号的顺序返回这些编号（order：保证序列号按请求顺序产生.如果你想以序列号作为timestamp(时间戳)类型的话,可以采用该选项.对于将序列用于生成主键来说,一般用noorder,约定顺序通常并不重要.）
这里需要重点说明的是cache参数，它是为了应对并发访问的。cache参数告诉oracle预先分配一个sequence numbers的集合，并且保留在内存中，以便sequence number能够被快速的访问。这个内存的大小就是cache所指定的大小，当多个用户同时访问一个sequence的时候，是在oracle SGA中读取sequence当前的合理数值，如果并发访问太大，cache的大小不够，那么就会产生sequence cache相关的等待（enq: SQ - contention），影响系统性能。
既然cache涉及到了内存，那么就会想到oracle实例恢复的问题。如果数据库shutdown abort，sequence会如何呢？当然会有问题，sequence number保存在内存里的但是没有被应用到表中的会丢失！

修改sequence

除了修改sequence的starting number，你什么都能改，如果想改starting number，只能先drop然后create。
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;
修改很有用，最典型的情况是“需要把sequence 的current value改大一点，避免程序报错！”。你就可以看看current value是多少，然后修改increment by 足够大的值，然后执行.nextval，最后别忘了再将increnent by改成原来的值，还要注意做这些工作的前提是当前没有人用此sequence。

使用 sequence

CURRVAL 和 NEXTVAL 能够在以下情况使用:
insert的values字句、select中的select列表、update中的set字句

CURRVAL 和 NEXTVAL 不能够在以下情况使用:
子查询、视图和实体化视图的查询、带distinct的select语句、带 group by和order by的select语句、带union或intersect或minus的select语句、select中的where字句、create table与alter table中的default值、check约束条件。

删除sequence

drop sequence seq_a;
当删除sequence后，对应它的同义词会被保留，但是引用时会报错。

oracle rac环境中的sequence

oracle为了在rac环境下为了sequence的一致性，使用了三种锁：row cache lock、SQ锁、SV锁。
row cache lock的目的是在sequence指定nocache的情况下调用sequence.nextval过程中保证序列的顺序性；
SQ锁是应用于指定了cache+noorder的情况下调用sequence.nextval过程中。
SV 锁（dfs lock handel) 是调用sequence.nextval期间拥有的锁。前提是创建sequence时指定了cache 和order属性 (cache+order)。order参数的目的是为了在RAC上节点之间生成sequence的顺序得到保障。

创建sequence赋予的cache值较小时，有enq:sq-contention等待增加的趋势。
cache的缺省值是20.因此创建并发访问多的sequence时，cacheh值应取大一些。否则会发生enq:sq-contention等待事件。

rac上创建sequence时，如果指定了cache大小而赋予noorder属性，则各节点将会把不同范围的sequence值cache到内存上。若两个节点之间都必须通过依次递增方式使用sequence，必须赋予如下的order属性（一般不需要这样做）"sql> create sequence seq_b cache 100 order"。如果是已赋予了cache+order属性的sequence，oracle使用SV锁进行同步。SV锁争用问题发生时的解决方法与sq锁的情况相同，就是将cache 值进行适当调整。

在RAC多节点环境下，Sequence的Cache属性对性能的影响很大。应该尽量赋予cache+noorder属性，并要给予足够的 cache值。如果需要保障顺序，必须赋予cache+order属性。但这时为了保障顺序，实例之间需要不断的交换数据。因此性能稍差。

3. Oracle Sequence的创建

Oracle创建序列是我们最常用的操作之一，下面就为您详细介绍Oracle创建序列及查询序列的语法知识，希望对您能够有所帮助。

Oracle创建序列

create sequence seq_a minvalue 1000 maxvalue 99999999 start with 1000 increment by 1 nocache; 

查询序列

selectseq_a.nextvalfromdual;

为每张表生成对应的序列

--创建存储过程

createorreplaceprocedurep_createseq(tablenameinvarchar2)
is
strsqlvarchar2(500);
begin
strsql:='createsequenceseq_'||tablename||'minvalue1000maxvalue99999999startwith1000incrementby1nocache';
executeimmediatestrsql;
endp_createseq;
/

--Oracle创建序列

execp_createseq('t_power');
execp_createseq('t_roler');
execp_createseq('t_roler_power');
execp_createseq('t_department');
execp_createseq('t_quarters');
execp_createseq('t_quarters_roler');
execp_createseq('t_emp');
execp_createseq('t_require_plan');
execp_createseq('t_require_minutia');
execp_createseq('t_require_audit');
execp_createseq('t_engage');
execp_createseq('t_home');
execp_createseq('t_education');
execp_createseq('t_works');
execp_createseq('t_skill');
execp_createseq('t_account');
execp_createseq('t_licence');
execp_createseq('t_title');
execp_createseq('t_remove');
execp_createseq('t_train');
execp_createseq('t_pact');
execp_createseq('t_assess');
execp_createseq('t_attendance');
execp_createseq('t_reward_punish');
execp_createseq('t_dimission');
execp_createseq('t_emp_roler');
execp_createseq('t_code');