与序列相关的等待事件

与序列相关的等待事件：

1.row cache lock

调用sequence.nextvald的过程中，会修改数据字典信息，这是个串行操作，发生在shared pool中的dictionary cache中，如果并发量大的话cpu会被耗尽，有宕库的危险。nocache属性的sequence上会很容易发生这个争用。

2.SQ锁 -- enq: SQ - contention

在调用sequence.nextval期间会拥有exclusive锁。发生在cache+noorder的sequence上。enq:SQ-contention事件的P2值是sequence的object ID；

3.SV锁 -- DFS lock handle RAC上调用sequence.nextval，在cache+order属性的sequence上发生

模拟row cache lock：

创建一个序列：

SQL> create sequence mingseq1$seq start with 1 increment by 1 nocache noorder;

Sequence created.

1节点会话1：

set timing on

declare

seq1 number;

begin

for i in 1..200000 loop

select mingseq1$seq.nextval into seq1 from dual;

end loop;

end;

/

1节点会话2：

set timing on

declare

seq2 number;

begin

for i in 1..200000 loop

select mingseq1$seq.nextval into seq2 from dual;

end loop;

end;

/

1节点会话3：

set timing on

declare

seq3 number;

begin

for i in 1..200000 loop

select mingseq1$seq.nextval into seq3 from dual;

end loop;

end;

/

查询等待事件：

select inst_id, event#, event,count(*) from gv$session

where wait_class# <> 6

group by inst_id, event#,event

order by 1,4 desc;

INST_ID EVENT# EVENT COUNT(*)

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

1 270 row cache lock 3

1 143 db file sequential read 2

1 537 ges message buffer allocation 1

2 879 PX Deq: reap credit 1

2 143 db file sequential read 1

查询row cache lock的详细信息：

set line 300 pages 150

col username for a15

col machine for a15

col program for a40

col event for a30

select sid,serial#,username,machine,program,sql_id,event from v$session where event#='270'order by sql_id;

SID SERIAL# USERNAME MACHINE PROGRAM SQL_ID EVENT

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

41 758 SYS rac1 sqlplus@rac1 (TNS V1-V3) 67hj6wznrdm3r row cache lock

47 9111 SYS rac1 sqlplus@rac1 (TNS V1-V3) 67hj6wznrdm3r row cache lock

46 56 SYS rac1 sqlplus@rac1 (TNS V1-V3) 67hj6wznrdm3r row cache lock

查询sql_id为 67hj6wznrdm3r的具体sql：

SQL> select sql_text from v$sqltext where sql_id='67hj6wznrdm3r';

SQL_TEXT

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

SELECT MINGSEQ1$SEQ.NEXTVAL FROM DUAL

这样就找出了问题出在哪个序列上。

此时的cpu使用率：

[oracle@rac1 ~]$ top

top - 03:58:29 up 2:12, 5 users, load average: 3.54, 2.03, 1.22

Tasks: 256 total, 3 running, 253 sleeping, 0 stopped, 0 zombie

Cpu(s): 56.2%us, 28.6%sy, 0.0%ni, 0.0%id, 0.0%wa, 8.0%hi, 7.2%si, 0.0%st

正常情况下：

top - 04:11:47 up 2:26, 5 users, load average: 0.97, 3.18, 3.19

Tasks: 259 total, 1 running, 258 sleeping, 0 stopped, 0 zombie

Cpu(s): 3.6%us, 3.2%sy, 0.0%ni, 80.9%id, 11.9%wa, 0.0%hi, 0.4%si, 0.0%st

模拟enq: SQ - contention：

创建一个序列：

SQL> create sequence mingseq2$seq start with 1 increment by 1 cache 2 noorder;

Sequence created.

多开几个会话一起执行：

set timing on

declare

seq1 number;

begin

for i in 1..200000 loop

select mingseq2$seq.nextval into seq1 from dual;

end loop;

end;

/

INST_ID EVENT# EVENT COUNT(*)

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

1 256 enq: SQ - contention 4

1 131 log file parallel write 1

1 436 asynch descriptor resize 1

查询等待事件详细信息：

set line 300 pages 150

col username for a15

col machine for a15

col program for a40

col event for a30

select sid,serial#,username,machine,p2,program,sql_id,event from v$session where event#='256'order by sql_id;

SID SERIAL# USERNAME MACHINE P2 PROGRAM SQL_ID EVENT

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

31 17 SYS oggtest1 77760 sqlplus@oggtest1 (TNS V1-V3) 8sj4z5nyh98my enq: SQ - contention

49 5 SYS oggtest1 77760 sqlplus@oggtest1 (TNS V1-V3) 8sj4z5nyh98my enq: SQ - contention

47 6 SYS oggtest1 77760 sqlplus@oggtest1 (TNS V1-V3) 8sj4z5nyh98my enq: SQ - contention

SQL> select sql_text from v$sqltext where sql_id='8sj4z5nyh98my';

SQL_TEXT

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

SELECT MINGSEQ2$SEQ.NEXTVAL FROM DUAL

这里加入了p2的值，p2的值为77760，正好是序列的object_id：

select object_id from dba_objects where object_name='MINGSEQ2$SEQ';

SQL>

OBJECT_ID

----------

77760

模拟SV锁 ：

创建一个序列：

SQL> create sequence mingseq3$seq start with 1 increment by 1 cache 2 order;

Sequence created.

每个节点开3个会话：

set timing on

declare

seq1 number;

begin

for i in 1..200000 loop

select mingseq3$seq.nextval into seq1 from dual;

end loop;

end;

/

查询等待事件：

select inst_id, event#, event,count(*) from gv$session

where wait_class# <> 6

group by inst_id, event#,event

order by 1,4 desc;

INST_ID EVENT# EVENT COUNT(*)

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

1 478 DFS lock handle 3

1 879 PX Deq: reap credit 1

2 478 DFS lock handle 4

2 879 PX Deq: reap credit 1

select inst_id,sid,serial#,blocking_session bs,username,machine,program,sql_id,event from gv$session where event#='478' order by sql_id;

SID SERIAL# BS USERNAME MACHINE PROGRAM SQL_ID EVENT

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

46 56 47 SYS rac1 sqlplus@rac1 (TNS V1-V3) b7s2205rcdfd0 DFS lock handle

47 9111 54 SYS rac1 sqlplus@rac1 (TNS V1-V3) b7s2205rcdfd0 DFS lock handle

54 6795 SYS rac2 sqlplus@rac2 (TNS V1-V3) b7s2205rcdfd0 DFS lock handle

52 4828 51 SYS rac2 sqlplus@rac2 (TNS V1-V3) b7s2205rcdfd0 DFS lock handle

53 1624 52 SYS rac2 sqlplus@rac2 (TNS V1-V3) b7s2205rcdfd0 DFS lock handle

51 2542 SYS rac2 sqlplus@rac2 (TNS V1-V3) b7s2205rcdfd0 DFS lock handle

查询sql_id为 67hj6wznrdm3r的具体sql：

SQL> select sql_text from v$sqltext where sql_id='b7s2205rcdfd0';

SQL_TEXT

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

SELECT MINGSEQ3$SEQ.NEXTVAL FROM DUAL

这样就找出了问题出在哪个序列上。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2154244/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31480688/viewspace-2154244/