Sequence虽然一直有用过,但是对于其内部体系和所伴随的rac等系统问题还是有所欠缺,序列的创建和使用可以说是非常基础的问题,其中的cache和order可以具体分析一下所带来的性能影响。
默认创建序列时只会cache 20,而这个数值对于数据的并发量比较大时cache 20往往是完全不够的,就跟oracle 10g 11g默认的提供的50M的3组redo而言,往往就会对系统的性能造成较大的压力。
Cache的测试:
SQL> create sequence seq01
2 start with 1
3 increment by 1
4 maxvalue 999999
5 nocache
6 ;
Sequence created
Executed in 0.063 seconds
SQL>
SQL> declare
2 v_time number;
3 begin
4 for i in 1..50000 loop
5 select seq01.nextval into v_time from dual;
6 end loop;
7 end;
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
2 start with 1
3 increment by 1
4 maxvalue 999999
5 ;
Sequence created
Executed in 0 seconds
SQL> declare
2 v_time number;
3 begin
4 for i in 1..50000 loop
5 select seq01.nextval into v_time from dual;
6 end loop;
7 end;
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
2 start with 1
3 increment by 1
4 maxvalue 999999
5 cache 10000;
Sequence created
Executed in 0.062 seconds
SQL> declare
2 v_time number;
3 begin
4 for i in 1..50000 loop
5 select seq01.nextval into v_time from dual;
6 end loop;
7 end;
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是:
Create sequence sequ001
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
oracle 402 1 0 Jul02 ? 00:00:00 ora_q001_benguo2
root 5763 5634 0 15:00 pts/1 00:00:00 su – oracle
skip...
oracle 31737 1 0 Jul02 ? 00:00:01 ora_pmon_benguo2
oracle 31739 1 0 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 ;
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ ---------- ---------- ----------
enq: SQ - contention name|mode object # 0
sequence的等待事件摘要http://blog.csdn.net/tianlesoftware/article/details/6534886
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25362835/viewspace-1058705/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25362835/viewspace-1058705/