朋友公司的某铁路集团医保系统出现性能问题业务不能正常办理,下面是出现性能问题时的awr报告

从等待事件来看主要是出现了多处锁竞争.其中enq: TX - allocate ITL entry等待事件是由于缺省情况下创建的表的INITRANS参数为1,索引的INITRANS参数值为2.当有太多的并发DML操作同时操作相同的数据块或索引块就会出现这个等待事件,可以通过查看Segments by ITL Waits部分的信息来了解出现大量并发DML操作的对象

从下面的信息可以看出消耗时间最长的语句都是数据更新操作

Enq:TX – row lock contention 等待事件,主要是由于要修改的记录已经被其它会话所持有排他锁产生的.通过查看Segments by Row Lock Waits信息可以看出主要是由sys_serial表产生的.

这是一个手工维护各业务表的主键序列的表.而不是选择由序列生成器来生成主键造成的.语句为
select cur_num_no from SYS_SERIAL where serial_type=:"SYS_B_0" for update

由上面的信息可知产生enq: TX - allocate ITL entry 和Enq:TX – row lock contention等待的主要对象是:
表:
KC19
KC21
KC40
KCE1
KC24
kcd9
kcd4
索引:
PK_KC24
IDX_KC40
PK_KC19
IDX_KC21
PK_KCE1
PK_KC21

知道主要原因了就有解决方法:
对于enq: TX - allocate ITL entry等待事件对主要竞争对象执行以下语句来修改INITRANS参数值:
Alter table kc19 initrans 50;
Alter table kc21 initrans 50;
Alter table kc40 initrans 50;
Alter table kce1 initrans 50;
Alter table kc24 initrans 50;
Alter table kcd9 initrans 50;
Alter table kcd4 initrans 50;

Alter table kc19 move;
Alter table kc21 move;
Alter table kc40 move;
Alter table kce1 move;
Alter table kc24 move;
Alter table kcd9 move;
Alter table kcd4 move;

Alter index pk_kc24 rebuild initrans 50 online;
Alter index idx_kc40 rebuild initrans 50 online;
Alter index pk_kc19 rebuild initrans 50 online;
Alter index idx_kc21 rebuild initrans 50 online;
Alter index pk_kce1 rebuild initrans 50 online;
Alter index pk_kc21 rebuild initrans 50 online;
对于Enq:TX – row lock contention等待事件主要是由
select cur_num_no from SYS_SERIAL where serial_type=:"SYS_B_0" for update
所以要解决这个问题需要修改各业表主键的生成方式不要使用上面的这种方法.

下面的图表是在执行上面的修改操作之后下午业务高峰期的awr报告如下

现在主要的就是enq: TX – row lock contention等待的这个需要修改程序结构

下面的图表是在执行上面的修改操作之后第二天上午业务高峰期8点到9点的awr报告如下

下面的图表是在执行上面的修改操作之后第二天上午业务高峰期9点到10点的awr报告如下

下面的图表是在执行上面的修改操作之后第二天上午业务高峰期10点到11点的awr报告如下

现在数据库运行较好.

问题原因有以下两个
1.由于并发更新的对象的initrans参数过小当出现大量并发插入,更新操作时出现了enq: TX - allocate ITL entry等待事件.
2.由于各业务表的主键序列值是由程序SYS_SERIAL表来维护的,每次生成主键值是都要执行
select cur_num_no from SYS_SERIAL where serial_type=:"SYS_B_0" for update
产生了Enq:TX – row lock contention等待事件

解决方法:
1.对enq: TX - allocate ITL entry等待事件对主要竞争对象执行以下语句来TRANS参数值
2.所以要解决Enq:TX – row lock contention这个问题需要修改各业表主键的生成方式比如使用序列生成器.