oracle中一个会话,一个Oracle会话严重阻塞的案例

环境：

os rhel 5.3

dbms 三节点 Oracle 10g rac

ver  10.2.0.4

现象：

某些工作站死机或网络异常后，特定的收费人员在ZLHIS中收费时，点击确定后，程序无响应．将会话kill后，重新登录ZLHIS,再次收费现象依旧．无论普通病人，还是医保病人都是同样现象．1-2小时后，ZLHIS自动恢复正常．

分析与解决过程：

1.分析会话的状态：

通过查询找出会话的等待事件：

SQL> select  event,sid,serial# ,blocking_session from gv$session where username='YB040';

EVENT                                                                   SID    SERIAL# BLOCKING_SESSION

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

SQL*Net message from client                                             972      48797

SQL*Net message from client                                            1069      61575

SQL*Net message from client                                            1111      12404

enq: TX - row lock contention                                          1171      55482             1069

enq: TX - row lock contention                                          1113      42042             1069

可以看到是1069阻塞了1171与1113会话，等待事件为enq: TX - row lock contention，典型的tx行级锁，查看1069会话锁定的对象：

分析一下业务，只有锁定了人员缴款余额，才有会这种现象，因为指定收费员的特定结算方式占用一行数据,而每次收费时都需要更新这一行数据，这一行数据就容易形成tx事务锁，一旦一个会话持有这一行的tx锁，其他会话将无法继续收费。

一般情况下，tx等级锁，只有事务提交或回退，或者kill掉会话，事务也会自动回退．但这个案例中，kill掉会话也不行。

取出这个时段的awr报告，发现tx锁的等待排在首位：

平均等待时间也达到489ms,已经非常严重了。查询了一下这个表的物理占用情况,一个只有471行的表，居然占用了24个数据块：

2.分析表的数据分布

这是不正常的．Dump这个表的所有数据块,使用如下命令：

alter system dump datafile 7 block min 577033 block max  577057

发现一些问题：

a. 数据分布不均匀，大多数数据块只存储了10-33行数据．而大多数行存储在两个主要的块中,这通过nrow可以看到：

data_block_dump,data header at 0x13045264

===============

tsiz: 0x1f98

hsiz: 0x34

pbl: 0x13045264

bdba: 0x01c8ce0c

76543210

flag=--------

ntab=1

nrow=17

frre=-1

fsbo=0x34

fseo=0x12d5

avsp=0x1d9d

tosp=0x1d9d

较密的块：

data_block_dump,data header at 0x130453b4

===============

tsiz: 0x1e48

hsiz: 0x254

pbl: 0x130453b4

bdba: 0x01c8ce0e

76543210

flag=--------

ntab=1

nrow=289

frre=193

fsbo=0x254

fseo=0x261

avsp=0xd

tosp=0xd

标注为红色的nrow项目，表示这一个数据块中包括多少行数据，在缴款余额这张表上，存在大量的update，并发事务也非常高，如果数据集中在少数的数据块上，必然加剧热点块的并发争用。

b.  出现了行链接现象：

行头的内容：

tab 0, row 74, @0x16ff

tl: 9 fb: --H----- lb: 0x0  cc: 0

nrid:  0x01c8ce0d.1f

行尾的内容：

tab 0, row 31, @0x7f0

tl: 32 fb: ----FL-- lb: 0x2  cc: 4

hrid: 0x01c8ce0e.4a

dump文件中，项目fb是一串标志位, --H-----表示只有行头; ----FL—表示有行的第一片段和最后一片段,完整的fb标志列的说明如下(来源于Oracle的DSI文档)：

这样一个小表出现这种情况是不正常的,因为平均行长很小，只能是大量的update导致了这样的现象。

c.数据密度较大的块上的空闲空间不足,以上块为例：avsp=0xd,项目avsp表示块中的可用空闲空间，这里转换成10进制表求只有13个字节可用。

再来看看itl的情况：

Block header dump:  0x01c8ce0e

Object id on Block? Y

seg/obj: 0x13485  csc: 0x00.2bcd2f5e  itc: 16  flg: E  typ: 1 - DATA

brn: 0  bdba: 0x1c8ce09 ver: 0x01 opc: 0

inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0025.00a.00004f48  0x09819c74.0218.33  C---    0  scn 0x0000.2bcd26e6

0x02   0x000a.016.0002e06b  0x008015d3.614f.07  C---    0  scn 0x0000.2bcd297e

0x03   0x0009.003.0000fb75  0x00800602.2faf.06  C---    0  scn 0x0000.2bcd253f

0x04   0x0001.02a.0000bd22  0x0080230b.28d3.48  --U-    1  fsc 0x0000.2bcd2fce

0x05   0x0005.00b.0000bd6a  0x00801d86.2831.35  C---    0  scn 0x0000.2bcd224a

0x06   0x0036.017.000236cf  0x09c23f51.04e2.2c  C---    0  scn 0x0000.2bcd25c7

0x07   0x0026.011.00004eef  0x0980083f.023c.4b  C---    0  scn 0x0000.2bcd2dcc

0x08   0x0036.01d.000236d5  0x09c23f5b.04e2.31  C---    0  scn 0x0000.2bcd2a26

0x09   0x0026.004.00004ef3  0x09800839.023c.06  C---    0  scn 0x0000.2bcd280d

0x0a   0x000a.01d.0002e058  0x008015d2.614f.3a  C---    0  scn 0x0000.2bcd2579

0x0b   0x000a.009.0002e054  0x008015ce.614f.41  C---    0  scn 0x0000.2bcd23b8

0x0c   0x000a.00f.0002e063  0x008015d3.614f.33  C---    0  scn 0x0000.2bcd2c20

0x0d   0x002d.004.00004cef  0x09c2237c.0266.0c  C---    0  scn 0x0000.2bcd2d07

0x0e   0x0031.00b.00004cf6  0x09c23ffe.0296.04  C---    0  scn 0x0000.2bcd2336

0x0f   0x0028.007.00004fa5  0x0981d616.0216.3d  --U-    1  fsc 0x0000.2bcd319b

0x10   0x0036.00f.000236cb  0x09c23f63.04e2.2b  --U-    1  fsc 0x0000.2bcd2f72

扩展一个itl需要大约23字节的空间,目前数据块已经扩展到16个itl槽，而初始的initrans为10,可见由于空闲空间缺乏，导致事务一直无法提交或回退，只有等到整个并发事务下来了，能够取得itl的时候就可以继续事务了，由于医院的高峰期要的高并发状态要持续一段时间，导致会话一直无法取得itl，只一直等下去。Itl不足是导致这个现象的一个原因。我们需要重建缴款余额这个表，使用move表的方法，但需要注意的是move表后，需要重建表上的索引：

alter table 人员缴款余额 pctfee 20 inittrans  40 ;

alter table  人员缴款余额 move;

alter index 人员缴款余额_PK rebuild pctfree 20 initrans 40 ;

4. 但还有一个问题，为什么kill掉会话，会话也不释放tx事务锁资源呢?Oracle由PMON进程来释放僵尸会话的锁、变量等资源，但这需要一个时间；这就是后面开启的会话等待１-2个小时，自动复活的原因。Oracle 有一个Dead Connection Detection(DCD 僵尸进程检测)的功能,启用这个功能后，Oracle会在指定的空闲间隔时间内，发送一个10个字节的探测包，如果客户端进程无响应则会启用PMON后台进程对这个进程的所占用的相关资源进程清理操作，包括内存资源(如PGA,UGA)、变量、锁等进行释放。

我在测试的rac环境，实验过这个方案。具体实施方法也非常简单：

A  .在每个服务器节点的sqlnet.ora文件中添加这个参数：

SQLNET.EXPIRE_TIME=

这个参数就是指定的空闲时间，也就是探测包发送的空闲间隔时间，以分钟为单位，可根据实际情况，设置一个时间。.

B. 设置这个参数后，要求重启或reload监听程序，由于重启监听程序不影响已经连接的用户，可以直接重启;由于是rac环境，需要重启所有节点的监听程序;如果是RAC环境，在任一节点执行如下命令即可：

srvctl stop listener –n 节点名

srvctl start listener –n 节点名

进行了这些调整后，系统运行了2个星期，没有再出现这个问题。