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


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

 BLOG文档结构图

wpsE272.tmp 

 前言部分

2.1  导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~

① enq: TX - row lock contention等待事件的解决

② 一般等待事件的解决办法

③ 队列等待的基本知识

④ ADDM的使用

⑤ 如何获取历史执行计划

⑥ 查询绑定变量的具体值

⑦ 很多有用的查询性能的SQL语句

  Tips:

① 本文在ITpubhttp://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和微信公众号(xiaomaimiaolhr)有同步更新

② 文章中用到的所有代码,相关软件,相关资料请前往小麦苗的云盘下载(http://blog.itpub.net/26736162/viewspace-1624453/

③ 若文章代码格式有错乱,推荐使用搜狗360或QQ浏览器,也可以下载pdf格式的文档来查看,pdf文档下载地址:http://blog.itpub.net/26736162/viewspace-1624453/,另外itpub格式显示有问题,可以去博客园地址阅读

④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方;而命令一般使用黄色背景和红色字体注;对代码或代码输出部分的注释一般采用蓝色字体表示

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

====》2097152*512/1024/1024/1024=1G 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

 

 

 故障分析及解决过程

 

3.1  故障环境介绍

 

项目

source db

db 类型

RAC

db version

11.2.0.4.0

db 存储

ASM

OS版本及kernel版本

AIX 64位 7.1.0.0

 

3.2  故障发生现象及报错信息

早上同事过来跟我说昨天有一套数据库做压力测试的时候,CPU利用率很高,他已经抓取当时的AWR,让我帮忙分析分析,下边我们来看看AWR中的数据:

wpsE273.tmp 

AWR报告的头部可以分析得到,数据库为RAC库,11.2.0.4版本,AIX64位系统,有32CPU,共48G内存,收集了40分钟内的AWR报告,但是DB Time15180分钟,约为15180/40=379倍,说明这段时间内系统的负载异常的大。

如果关注数据库的性能,那么当拿到一份AWR报告的时候,最想知道的第一件事情可能就是系统资源的利用情况了,而首当其冲的,就是CPU。而细分起来,CPU可能指的是

l OS级的User%, Sys%, Idle%

l DB所占OS CPU资源的Busy%

l DB CPU又可以分为前台所消耗的CPU和后台所消耗的CPU

我们分析以下主机CPU的情况:

wpsE274.tmp 

分析上面的主机图片,我们可以得出下面的结论:

v OS 级的 User%Sys%Idle%

OS 级的%User 为 2.9,%Sys 为 2.3,%Idle 为 94.8,所以%Busy应该是 100-94.8=5.2

v DB 所占 OS CPU 资源的 Busy%

DB 占了 OS CPU 资源的 2.2,%Busy CPU 则可以通过上面的数据得到:

%Busy CPU = %Total CPU/(%Busy) * 100 = 2.2/5.2* 100 = 42.3,和报告的42.2相吻合。

接下来我们看看Load Profile部分:

wpsE275.tmp 

可以看到,每秒的事务数大约为358,非常大,接下来看看等待事件:

wpsE276.tmp 

其它的项目就不列出了,从等待事件中可以很明显的看出enq: TX - row lock contention这个等待事件异常。Top 10 Foreground Events by Total Wait Time这个部分也是AWR报告中非常重要的部分,从这里可以看出等待时间在前10位的是什么事件,基本上就可以判断出性能瓶颈在什么地方。通常,在没有问题的数据库中,CPU time总是列在第一个。在这里,enq: TX - row lock contention等待了 3,813,533次,等待时间为855,777秒,平均等待时间为855777/3813533=224毫秒,占DB Time94%,等待类别为Application上的等待问题。

3.3  故障分析

根据AWR报告的内容,我们知道只要解决了enq: TX - row lock contention个等待事件即可解决问题。那么我们首先来了解一些关于这个等待事件的知识。

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

SELECT * FROM V$EVENT_NAME WHERE NAME = 'enq: TX - row lock contention';

wpsE287.tmp 

SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('TX');

wpsE288.tmp 

SELECT D.EQ_NAME, D.EQ_TYPE, D.REQ_REASON, D.REQ_DESCRIPTION

  FROM V$ENQUEUE_STATISTICS D

 WHERE D.EQ_TYPE IN ('TX')

 AND D.REQ_REASON='row lock contention';

wpsE289.tmp 

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

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

Oracle enqueue 包含以下模式:

模式代码

解释

1

Null mode

2

Sub-Share

3

Sub-Exclusive

4

Share

5

Share/Sub-Exclusive

6

Exclusive

 

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

(一)第一种情况,是真正的业务逻辑上的行锁冲突,如一条记录被多个人同时修改。这种锁对应的请求模式是6(Waits for TX in mode 6 会话持有row level lockB会话等待这个lock释放。)。不同的session更新或删除同一个记录。(This occurs when one application is updating or deleting a row that another session is also trying to update or delete. 

解决办法:持有锁的会话commit或者rollback

(二)第二种情况,是唯一键冲突(In mode 4,唯一索引),如主键字段相同的多条记录同时插入。这种锁对应的请求模式是4。这也是应用逻辑问题。表上存在唯一索引,A会话插入一个值(未提交),B会话随后也插入同样的值;A会话提交后,enq: TX - row lock contention消失。

解决办法:持有锁的会话commit或者rollback

(三)第三种情况,是bitmap索引的更新冲突(in mode 4 :bitmap,就是多个会话同时更新bitmap索引的同一个数据块。源于bitmap的特性:位图索引的一个键值,会指向多行记录,所以更新一行就会把该键值指向的所有行锁定。此时会话请求锁的对应的请求模式是4bitmap索引的物理结构和普通索引一样,也是 B-tree 结构,但它存储的数据记录的逻辑结构为"key_value,start_rowid,end_rowid,bitmap"

其内容类似这样:"‘8088,00000000000,10000034441,1001000100001111000"

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

解决办法:持有锁的会话commit或者rollback

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

(四)其他原因

It could be a primary key problem;  a trigger firing attempting to insert, delete, or update a row; a problem with initrans; waiting for an index split to complete; problems with bitmap indexes;updating a row already updated by another session; or something else.

https://forums.oracle.com/forums/thread.jspa?threadID=860488

 

 

如果数据库一出现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

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
       ORA-01408:such column list already indexed问题的原因是当新建一个索引时,索引对应的字段和字段顺序和已经存在的索引相同。        最近一个需求,是将主键索引转成HASH分区的主键索引,需要新建一个索引,然后将现有的主键索引去掉。如果直接新建一个字段相同且字段顺序相同的索引,就会报ORA-01408。当然,可以将现有的主键索引先删掉,但这样在新索引建立之前,无法保证数据的唯一性,并且按主键的更新删除操作会因为无索引而变的很慢。        我们可
继续上次分析的一个问题,一个简单的SQL语句执行计划有些奇怪,明明可以走唯一性索引但是却走了另外一个索引。 当然了,最后逐步定位,发现是在直方图的地方有一些差别。取消直方图之后,执行计划立刻恢复了正常。 当然问题来了,这个是为什么呢,收集统计信息中的auto选项是什么含义呢。为什么两个数据类型一样的(varchar2(64))的列,境遇却大大不同。 我们来看看一些统计信息的数据。 为了跟进一步验证数据的分布律和选取代价,我们查询它的直方图信息。 SQL   select to_char(endpoint_v
首先说明_fairness_threshold参数仅在RAC环境下才有意义,让我对这个参数引起关注是在某一次查询v$cr_block_server时,发现该视图有一个名为FAIRNESS_DOWN_CONVERTS的字段,官方文档里将其解释为:Number of times an instance receiving a request has down-converted an X lock on a block because it was not modifying the block 该参数相关性
    今天看到有一个网友提了一个问题,描述很简短     测试DG时,主库不能宕机,如何测试failover?     其实这个需求从业务层面来说是合理的,一个数据量很大的核心数据库,如果需要做灾难演练,就希望在备库上做一下演练工作,而这个演练其实又不想影响到目前的主库,而且又希望能够尽可能模拟真实的情况,我想这样对于运维部门来说是最具有考核力度,而对于开发业务部门来说是最受欢迎的,因为他们什么都不需要改动。 而从技术角度来看,似乎有一些地方需要考量,如果备库Failover为主库,那么这个主库肯定是可以

简单分析percona-zabbix-templates - 2016-09-01 04:09:15

    当Zabbix和Percona两者相遇,会擦出不少的开源火花来,众人拾柴火焰高,最终受益的还是大部分运维人员。     我很早就用过Percona提供的MySQL监控模板,但是却没有刨根问底,只是简单使用而已,自从定制了Orabbix之后,我还是信心满满,MySQL的数据字典相对要少很多,监控起来可能想必Oracle要少很多,不过关于Percona的这个插件,我还是带着好奇之心,内部是否有很多独门秘籍,我想好好学学那些监控项对应的SQL,好好弥补我对于MySQL监控的一些空缺,所以简单分析这个模板就
问题描述 : 近期的 rman 备份中,归档日志的备份没有被删除, rman 的脚本和策略都没变过,归档的备份一直保留,每过一段时间就要物理删除备份,很是奇怪。 rman的 configure 如下 RMAN show all; RMAN configuration parameters for database withdb_unique_name HUBSRAC are: CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE BACKUP OPTI
一 前言  纳西姆.尼古拉斯.塔勒布的经典著作《黑天鹅》中对“黑天鹅现象”的定义是 - 不可预测,人们事前往往低估其发生的可能性 - 造成极大影响 - 事后回头再看,又觉得此事发生的有理 二 分析   稳定性 是一项衡量基础系统是否永续服务的绝对指标,作为资深DBA从业人员,相信大多数公司运维团队都会制定稳定性的SLA指标达到N个9,为用户提供Full-Time 服务。然而前一段时间各种"黑天鹅”式的因素导致一系列的系统故障,严重影响了C端B端的用户的使用体验。 故 障 是数据库系统或者说业务系统的“脆弱
最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落在了DB这边,希望DB能够给点意见,是否存在一些性能瓶颈。     我们从开发同学那里得到的一个基本的SQL语句,根据关键字从v$sql中做了提取,发现对应的SQL语句的执行时间还是OK的。 得到的SQL语句如下: SQL_ID        SQL_FULLTEXT ------------- ---------------------------

Oracle之不可见索引 - 2016-08-27 17:08:09

Oracle 之不可见索引 1    BLOG 文档结构图   2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  Oracle 不可见索引的使用   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lhrbest )和微信公众号( xiaomaimiaolhr )有同步更新
【故障处理】序列 cache 值过小导致 CPU 利用率过高 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① enq: SQ - contention 等待事件的解决 ② 一般等待事件的解决办法 ③  DFS lock handle 等待事件 ④ 与序列有关的等待事件   Tips: ① 本文在 ITpub ( http://blog.itpub.