`
mikixiyou
  • 浏览: 1098050 次
  • 性别: Icon_minigender_1
  • 来自: 南京
博客专栏
C3c8d188-c0ab-3396-821d-b68331e21226
Oracle管理和开发
浏览量:352847
社区版块
存档分类
最新评论

等待事件enq TX row lock contention分析

阅读更多

在Oracle数据库性能报告AWRRPT分析时,发现top 5等待事件第一位的是enq: TX - row lock contention。这个等待事件消耗了绝大多数的CPU资源,导致系统整理性能下降。有些查询以前只要几个毫秒,现在变成了数分钟。CPU使用率长时间维持在100%。

 声明一下,数据库版本是10g for linux x86 64bit。

在AWRRPT中看到的top 5等待如下:

Top 5 Timed Events
Event    Waits    Time(s)    Avg Wait(ms)    % Total Call Time    Wait Class
enq: TX - row lock contention    27,190    62,674    2,305    63.1    Application
CPU time         36,227         36.5     
log file sync    133,744    52    0    .1    Commit
log file parallel write    143,992    29    0    .0    System I/O
db file sequential read    49,403    23    0    .0    User I/O

 

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1771648)

 

通常情况下,Oracle数据库的等待事件enq: TX - row lock contention会在下列三种情况下会出现。

第一种情况,是真正的业务逻辑上的行锁冲突,如一条记录被多个人同时修改。这种锁对应的请求模式是6

第二种情况,是唯一键冲突,如主键字段相同的多条记录同时插入。这种锁对应的请求模式是4。这也是应用逻辑问题。

第三种情况,是bitmap索引的更新冲突,就是多个会话同时更新bitmap索引的同一个数据块。此时会话请求锁的对应的请求模式是4

bitmap索引的物理结构和普通索引一样,也是 B-tree 结构。但它存储的数据记录的逻辑结构为"key_value,start_rowid,end_rowid,bitmap"

其内容类似这样:

"8088’,00000000000,10000034441,1001000100001111000"

Bitmap是一个二进制,表示 START_ROWID END_ROWID 的记录, 1 表示等于 key_value 即‘8088’ ROWID 记录, 0 则表示不是这个记录。

在了解bitmap索引的结构之后,我们就能理解同时插入多条记录到拥有bitmap索引的表时,就会同时更新bitmap索引中一个块中的记录,等于某一个记录被同时更新,自然就会出现行锁等待。插入并发量越大,等待越严重。

等待事件enq: TX - row lock contention中的enqenquence的简写。enquence是协调访问数据库资源的内部锁。

所有以“enq:”打头的等待事件都表示这个会话正在等待另一个会话持有的内部锁释放,它的名称格式是enq:enqueue_type - related_details。这里的enqueue_typeTXrelated_detailsrow lock contention。数据库动态性能视图v$event_name提供所有以“enq:”开头的等待事件的列表。

虽然在awrrpt中看到大量enq: TX - row lock contention的等待,但这些是事后看到的信息。根据AWRRPT,我们无法只能该等待事件的请求模式是什么,是6还是4

如果数据库一出现enq: TX - row lock contention等待,可以去看v$sessionv$session_wait等视图。

 

v$sessionv$session_wait中,如果看到的event列是enq: TX - row lock contention的,就表示这个会话正处于行锁等待。该等待事件的请求模式可以从v$sessionv$session_waitp1列中得到。

select sid,

       chr(bitand(p1, -16777216) / 16777215) ||

       chr(bitand(p1, 16711680) / 65535) "Name",

       (bitand(p1, 65535)) "Mode"

  from v$session_wait

 where event like 'enq%';

通过这个SQL可以将p1转换为易阅读的文字。



我针对这三种情况,分别进行测试。

首先,我准备一下测试表和数据。
--创建测试表和数据
create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;
alter table T_ALL_OBJS  add constraint pk_t_all_objs primary key (OBJECT_ID);
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011701,'test1');
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011702,'test2');
commit;

第一种情况,不同会话同时更新同一条记录
--test1
--sid=1015
update t_all_objs set object_name='test11' where object_id=2013011701;

--sid=1065
update t_all_objs set object_name='test101' where object_id=2013011701;

--第三个会话中查询
select sid,sql_id,blocking_session,event,p1text,p1,wait_class,state from v$session where sid in (1015, 1065);
select * from v$session_wait where sid in (1015, 1065);

       SID    SQL_ID    BLOCKING_SESSION    EVENT    P1TEXT    P1    WAIT_CLASS    STATE
1    1015            SQL*Net message from client    driver id    1413697536    Idle    WAITING
2    1065    6y6u0gx4qa5v5    1015    enq: TX - row lock contention    name|mode    1415053318    Application    WAITING

       SID    SEQ#    EVENT    P1TEXT    P1    P1RAW    P2TEXT    P2    P2RAW    P3TEXT    P3    P3RAW    WAIT_CLASS_ID    WAIT_CLASS#    WAIT_CLASS    WAIT_TIME    SECONDS_IN_WAIT    STATE
1    1015    44    SQL*Net message from client    driver id    1413697536    54435000    #bytes    1    00000001        0    00    2723168908    6    Idle    0    1306    WAITING
2    1065    41    enq: TX - row lock contention    name|mode    1415053318    54580006    usn<<16 | slot    655407    000A002F    sequence    92680    00016A08    4217450380    1    Application    0    1291    WAITING

--得到sql_id值,查询出SQL信息。
select sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = '6y6u0gx4qa5v5';

       SQL_TEXT    EXECUTIONS    S.ELAPSED_TIME/1000000
1     update t_all_objs set object_name='test11' where object_id=2013011701     0    1286.977282

--再查询锁信息
select * from v$lock where sid in (1015, 1065) order by sid, type;

       ADDR    KADDR    SID    TYPE    ID1    ID2    LMODE    REQUEST    CTIME    BLOCK
1    31AA5BC4    31AA5BDC    1015    TM    191499    0    3    0    1306    0
2    30D135B0    30D136CC    1015    TX    655407    92680    6    0    1306    1
3    31AA5C88    31AA5CA0    1065    TM    191499    0    3    0    1291    0
4    32034928    3203493C    1065    TX    655407    92680    0    6    1291    0

第二种情况,不同会话中同时插入主键字段相同的记录
--test2
--sid=1015
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test1');

--sid=1065
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');

--第三个会话中查询
select sid,sql_id,blocking_session,event,p1text,p1,wait_class,state from v$session where sid in (1015, 1065);
select * from v$session_wait where sid in (1015, 1065);

       SID    SQL_ID    BLOCKING_SESSION    EVENT    P1TEXT    P1    WAIT_CLASS    STATE
1    1015            SQL*Net message from client    driver id    1413697536    Idle    WAITING
2    1065    bmv0jcs53zkad    1015    enq: TX - row lock contention    name|mode    1415053316    Application    WAITING

       SID    SEQ#    EVENT    P1TEXT    P1    P1RAW    P2TEXT    P2    P2RAW    P3TEXT    P3    P3RAW    WAIT_CLASS_ID    WAIT_CLASS#    WAIT_CLASS    WAIT_TIME    SECONDS_IN_WAIT    STATE
1    1015    59    SQL*Net message from client    driver id    1413697536    54435000    #bytes    1    00000001        0    00    2723168908    6    Idle    0    69    WAITING
2    1065    69    enq: TX - row lock contention    name|mode    1415053316    54580004    usn<<16 | slot    458790    00070026    sequence    92434    00016912    4217450380    1    Application    0    12    WAITING


--得到sql_id值,查询出SQL信息。
select sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'bmv0jcs53zkad';

       SQL_TEXT    EXECUTIONS    S.ELAPSED_TIME/1000000
1     insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11')     0    11.992511


--再查询锁信息
select * from v$lock where sid in (1015, 1065) order by sid, type;

       ADDR    KADDR    SID    TYPE    ID1    ID2    LMODE    REQUEST    CTIME    BLOCK
1    31AA5BC4    31AA5BDC    1015    TM    191499    0    3    0    69    0
2    30D135B0    30D136CC    1015    TX    458790    92434    6    0    69    1
3    31AA5C88    31AA5CA0    1065    TM    191499    0    3    0    12    0
4    32034928    3203493C    1065    TX    458790    92434    0    4    12    0
5    30E30A50    30E30B6C    1065    TX    589865    106712    6    0    12    0


第三种情况,不同会话中同时bitmap索引列值相同的记录

--test3
--创建一个bitmap索引
create bitmap index ind_T_ALL_OBJS on T_ALL_OBJS (owner);

--sid=1015
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');

--sid=1065
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test12');


--第三个会话中查询
select sid,sql_id,blocking_session,event,p1text,p1,wait_class,state from v$session where sid in (1015, 1065);
select * from v$session_wait where sid in (1015, 1065);

       SID    SQL_ID    BLOCKING_SESSION    EVENT    P1TEXT    P1    WAIT_CLASS    STATE
1    1015            SQL*Net message from client    driver id    1413697536    Idle    WAITING
2    1065    gsf39j000n6ys    1015    enq: TX - row lock contention    name|mode    1415053316    Application    WAITING

       SID    SEQ#    EVENT    P1TEXT    P1    P1RAW    P2TEXT    P2    P2RAW    P3TEXT    P3    P3RAW    WAIT_CLASS_ID    WAIT_CLASS#    WAIT_CLASS    WAIT_TIME    SECONDS_IN_WAIT    STATE
1    1015    74    SQL*Net message from client    driver id    1413697536    54435000    #bytes    1    00000001        0    00    2723168908    6    Idle    0    24    WAITING
2    1065    85    enq: TX - row lock contention    name|mode    1415053316    54580004    usn<<16 | slot    524294    00080006    sequence    106695    0001A0C7    4217450380    1    Application    0    15    WAITING


--得到sql_id值,查询出SQL信息。
select sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'gsf39j000n6ys';

       SQL_TEXT    EXECUTIONS    S.ELAPSED_TIME/1000000
1        0    11.988711


--再查询锁信息
select * from v$lock where sid in (1015, 1065) order by sid, type;

       ADDR    KADDR    SID    TYPE    ID1    ID2    LMODE    REQUEST    CTIME    BLOCK
1    31AA5BC4    31AA5BDC    1015    TM    191499    0    3    0    24    0
2    30D135B0    30D136CC    1015    TX    524294    106695    6    0    24    1
3    31AA5C88    31AA5CA0    1065    TM    191499    0    3    0    15    0
4    32034928    3203493C    1065    TX    524294    106695    0    4    15    0
5    30E30A50    30E30B6C    1065    TX    458798    92446    6    0    15    0

在数据库的awrrpt中我还看到关于某个表的大量insert操作,每次操作执行时间都很长,这要么是应用逻辑问题,要么就是bitmap索引记录更新等待。开发人员证实逻辑问题不可能,查询表的索引状况,确实有bitmap索引。

分享到:
评论

相关推荐

    BLOG_【故障处理】Oracle_lhr_队列等待之TX - row lock contention

    ### 故障处理:Oracle_lhr_队列等待之TX - row lock contention #### 一、概述 在Oracle数据库管理中,“enq:TX-rowlockcontention”是一种常见的队列等待事件,通常与行级别的锁定冲突有关。这种冲突可能会导致...

    Oracle10g数据库最佳新特性:等待界面 (2)

    以SID为269的会话为例,如果它正等待enq:TX — row lock contention事件,意味着它在等待另一个会话释放锁。在Oracle 10g之前,找出阻塞会话可能需要编写资源密集型查询,但在10g中,只需简单查询v$session就能找到...

    Oracle10g数据库最佳新特性:等待界面 (1)

    例如,对于"enq:TX - row lock contention"这类等待事件,可能需要考虑事务的并发控制策略或锁的粒度。 总之,Oracle 10g的等待界面改进极大地增强了DBAs在性能监控和问题诊断方面的能力。结合ADDM的自动化分析和...

    oraclev$sessionv$session_wait用途详解

    1. **等待事件类型**:`EVENT`字段列出了具体的等待事件名称,如“db file sequential read”表示顺序读取数据库文件,“enq: TX - row lock contention”表示行级锁竞争。 2. **等待时长**:`TIME_WAITED`字段提供...

    orcale死锁查杀PB版

    - 使用`v$session_wait`视图查找等待类型的`ENQ: TX - row lock contention`或`ENQ: TX - deadlock`。 - 查询`v$deadlock`和`v$deadlock_monitor`视图获取死锁信息。 - 执行`DBMS_LOCK.MONITOR`过程来监控死锁...

    中国银行ORACLE优化培训方案

    - **enq:TX-rowlock contention**:行级锁争用,表示两个或多个会话尝试在同一行上执行不兼容的操作。 - **latch free**:闩锁等待,表示进程正在等待一个当前被其他进程占用的闩锁。 #### 三、使用与查看数据库...

    EBS服务器升级PPT课件.pptx

    数据库性能分析显示,主要的性能瓶颈在于用户I/O,尤其是db file sequential read和db file scattered read事件,以及enq: TX - row lock contention,这些都可能导致新Session无法启动或需要更多内存来处理更多的...

    People soft on exadata

    - **行锁争用(enq:TX – row lock contention)**:在并发事务处理中,行锁争用是常见的问题之一。当多个事务尝试同时修改同一数据行时,就可能发生此类等待事件。 - **用户I/O**: - 单块读取(single block read...

    oracledb-exporter.0.2.9-ora18.5.linux-amd64.tar.gz

    - 锁等待情况(如ENQ: TX - ROW LOCK contention) Prometheus会定期抓取oracledb-exporter提供的这些指标,并根据预设的规则进行报警和可视化展示。你可以利用Prometheus的Alertmanager设置告警规则,当特定指标...

    oracle DBA 例行维护内容

    WHERE event LIKE 'enq: TX - row lock contention' OR event LIKE '%wait%'; -- 杀死会话 ALTER SYSTEM KILL SESSION 'sid,serial#'; ``` ##### 2.4 检查系统空间占用 - **工具选择**:使用操作系统自带的...

Global site tag (gtag.js) - Google Analytics