end:TX-row lock contention 等待事件分析过程与调优方法案例一则

5月20号上午9:00到10:00之间,某项目组进行数据质检作业。效率异常低下,几乎处于停滞状态。果断收集了9-10点的AWR报告,现在跟着我,通过AWR报告来发现问题。

拣关键的信息看,自然先看TOP 5 EVENTS:

        

排第一位的是end:TX-row lock contention。这是一个行级锁的争用,一般在以下三种情况下会产生该事件:

第一种情况:当两个会话对同一行进行更新时,ORACLE为了保证数据库的一致性,加了一个TX锁,这时另一个或多个会话必须等待第一个会话commit或rollback,否则会一等待下去,这是最为常见的一种模式!

第二种情况:两个或多个会话向具有唯一主健索引的表中插入或更新相同的数据行,既然是唯一主健索引,那么先获得插入的session以TX排它锁模式进行添加,此时其它session只能等待,也是常见的一种情况

第三种情况:两个或多个会话插入或更新具有位图索引的列,跟据位图索引的特性,一个索引键值对应多个数据行的rowid值,此种情况下也是以TX模式访问,一般在OLTP系统中很少有位图索引,但不排除个别系统,本例中不存在位图索引。

该事件在1小时以内一共出现了100次,次数倒是不多。不过当看到每次要等待61832ms时,小伙伴表示惊呆了,一等就是一分多钟,100次就是100分钟。在60分钟的运行时间里,等待花了100分钟,这绝对是巨大的问题。

那如何找到始作俑者呢?即然是row lock等待,那我们直接去看哪些对象(可能是表,也可能是索引)上发生这种等待的次数最多,直接跳到segments by row lock wait:

Segments by row lock waits一共列出了4个segment,等待总次数为185次。185次?TOP 5 EVENTS中的end:TX-row lock contention不是只等待了100次么?事实上,end:TX-row lock contention中只列出了等待时间超过0.001s的等待,而Segments by row lock waits列出了所有的等待。在本例中,说明有85次的row lock waits小于0.001s。

等待次数最多的segment为TBCHECK_CHECKITEM,是一个表对象。

排第二次的segment为SYS_IL0000086962C00006$$,是一个索引对象。通过名称可以判断,这是一个lobindex。我们去数据库里查询,该lobindex位于哪个表的哪个字段之上:

select table_name,column_name,index_name,segment_name from user_lobs t where index_name in ('SYS_IL0000086962C00006$$')

查询结果如下:

仍然是TBCHECK_CHECKITEM表。另外可知,该表的f_ruleitem字段为LOB字段。

到目前为止,我们知道了造成end:TX-row lock contention是由哪个对象引起的。但我们仍然不知道是在对该对象做什么操作时引起的。我们继续分析。

在AWR报告的Time Model Statistics部分,我们分析各项statistic name耗时占比,如下图:

SQL执行用了92.78%的时间,而有效的DB CPU时间只占4.67%,说明SQL执行大多数时间都用在了等待上。既然是SQL执行用了最多的时间,接下来,我们就去分析最耗时的SQL语句。

此处还要注意,解析时间用了181s,这其中硬解析占了169s。虽然相对于SQL执行时间来说,这点儿时间不算什么,但这里仍然需要警惕。要分析过高的硬解析是否是由于SQL复用率过低导致的。通过观察”Execute to Parse %”以及” % SQL with executions>1”的值,大致可以证明:SQL复用率过低。

还是回到主要矛盾上来,接下来,去看看最耗时的SQL语句:SQL ordered by Elapsed Time

选择第1-4,以及第6行,根据SQL_ID,查询到对应的SQL语句:

SQL_ID

SQL

4h0gfv3h27b95

UPDATE TBCHECK_CHECKITEM SET F_RULEITEM= :content WHERE ID = 1392

b39dw09mjdxqj

UPDATE TBCHECK_CHECKITEM SET F_RULEITEM= :content WHERE ID = 1394

33swqrzuv610c

UPDATE TBCHECK_CHECKITEM SET ID=1394, F_NAME='耕地后备资源图层属性一致性检查', F_DESC='', F_RULENAME='FeatValueHBTCCheck', F_KVSETTING='', F_ERRORCOUNT=0 WHERE ID = 1394

8g1g6tf3scb6f

UPDATE TBCHECK_CHECKITEM SET ID=1392, F_NAME='标准表格命名一致检查', F_DESC='', F_RULENAME='TableNameCheck', F_KVSETTING='', F_ERRORCOUNT=0 WHERE ID = 1392

03a4z4mhwup70

DELETE FROM GDHBSDE.HBTC WHERE F_XZQHDM = 130534        


       前4行都是对同一个表,即TBCHECK_CHECKITEM进行更新,仍然是TBCHECK_CHECKITEM,与之前对ROW LOCK WAIT结果吻合了。从前4行至少可以看出以下以个问题:

1)  1,2两行句式相同,仅字面量不同,导致SQL_ID不同,是不使用绑定变量导致SQL复用率低的典型案例;

2)  3,4两行名式相同,SQL_ID不同,同样是不使用绑定变量导致SQL复用率低的典型案例;

3)  1,4两行对同一条记录进行更新,极有可能导致ROW LOCK WAIT;

4)  2,3两行对同一条记录进行更新,极有可能导致ROW LOCK WAIT

 

在作出调优计划之前,自然需要跟开发人员沟通。问一下,为什么要这样设计?这些SQL语句在执行什么样的业务?

沟通结果让人大吃一惊。这些SQL原来仅仅是埋在代码里的调试代码,是最开始用于测试用的,后来发版本时忘了注释掉。其实TBCHECK_CHECKITEM是一个检查项的字典表,根本没有更新的必要,这些UPDATE语句更新后的值仍然是更新前的值。但就是这些UPDATE,不仅造成了并发时的ROW LOCK WAIT,还导致了并发时的enq: HW – contention,即TOP 5 EVENTS中排第二位的等待事件。

enq: HW – contention是为了防止多个进程同时修改表的HWM(即高水位线)而产生的等待事件。进程若想移动HWM的进程必须获得HW锁。Oracle高水位线标志着该线以下的block均被Oracle格式过,通俗一点讲就是该高水位线以下的block都被Oracle使用过。通常在执行INSERT操作时,有时在执行UPDATE操作时,当高水位线以下BLOCK不够用时,ORACLE将会推进高水位线。

查询近1小时之内的产生enq: HW – contention等待的SESSION。

select p1,p2,p3 from v$active_session_history where event='enq: HW - contention'

通过P3,可以用DBMS_UTILITY将其转换为文件和BLOCK;

select dbms_utility.data_block_address_block(140003563) file,dbms_utility.data_block_address_file(140003563) block from dual;

而后,通过file#和block#定位对象(借助dba_extents字典表),查到该对象为:“SYS_LOB0000086962C00006$$”。而通过上文对TBCHECK_CHECKITEM表的查询,可知该对象即为F_RULEITEM字段所对应的LOB段。

那为什么更新LOB段,会导致HWM的推进呢?这与LOB的更新机制有关。LOB实现读一致性的方式与其它信息所用UNDO实现读一致性的方式不同。LOBSEGMENT并不使用UNDO来记录其修改,而是直接在LOGSEGMENT本身中维护信息的版本。LOBINDEX会像其它段一样生成UNDO,但是LOGSEGMENT不会。相反,修改一个LOB时,ORACLE会分配一个新的CHUNK,并且仍保留原来的CHUNK。如果回滚了事务,对LOB索引所做的修改会回滚,索引将再次指向原来的CHUNK。正是由于修改LOB时,原来的数据保持不动,另外会创建新数据,创建新数据可能就会推进高水位线。当多个进程在同时创建新数据,同时推进高水位线时,争用就产生了。

 

说完了SQL语句的前4条,再来看第6条。第6条SQL语句单次执行的时间竟然达900多秒。通过与开发人员沟通,知道这一过程是删除分区表某个分区的所有数据。DML会产生大量的REDO与UNDO,因此对于整个分区的删除,可以采用直接删除分区的方式。

 

针对上文中提到的问题,给出针对性的解决办法:

1)       对于UPDATE TBCHECK_CHECKITEM表导致的等待,由于该SQL无实际作用,直接注释掉;

2)       对于大量硬解析的问题,再大量调整代码工作量过大,修改数据库参数,使其针对句式相同的SQL,强制转换成绑定变量

alter system set cursor_sharing='FORCE' scope=spfile;

         经调整之后,质检过程中TOP 5 EVENTS中排前两名的等待事件消失。硬解析比率大大降低。


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

转载于:http://blog.itpub.net/31340387/viewspace-2108429/

<think>好的,我现在需要帮助用户分析Oracle AWR报告,定位性能瓶颈并提供优化建议。用户提到具体的指标包括DB Time、Sessions、Cursors/Session等,版本是11.2.0.4.0,平台是Linux x86 64-bit RAC环境。首先,我需要回忆一下AWR报告的结构和关键指标,以及如何通过这些数据来识别问题。 首先,DB Time是一个非常重要的指标,它反映了数据库在处理请求所消耗的总时间。根据引用[3],DB Time的计算需要考虑DB CPU和各种非空闲等待事件的总和。如果DB Time远大于实际经过的时间,说明数据库存在性能瓶颈。比如用户例子中的DB Time是604.3分钟,而实际时间可能只有几分钟的话,就说明数据库在持续高负荷运行,需要进一步分析。 接下来,Sessions部分,用户提到需要关注活动会话数和等待事件。我需要检查AWR报告中的负载概要,查看每秒的事务数、逻辑读、物理读等指标,这些可以反映数据库的整体负载情况。如果逻辑读过高,可能说明存在大量全表扫描,需要优化SQL或索引。物理读高则可能意味着I/O问题,需要检查存储性能或增加缓存。 然后关于Cursors/Session,高数量的游标可能引发共享池争用。根据引用[5],执行次数多的SQL语句即使单条执行快,但次数多也会累积消耗资源。所以需要查看“SQL ordered by Executions”部分,找出高频SQL,检查是否有绑定变量使用不当导致硬解析过多,或者游标未共享的情况。 另外,等待事件部分,比如常见的db file sequential read(索引块读取)、db file scattered read(全表扫描)、latch: shared pool(共享池争用)等,这些等待事件的时间如果很高,就需要针对性优化。比如db file sequential read等待时间长,可能需要优化索引或整SQL以减少单块读取。 在RAC环境中,还需要关注全局缓存相关的等待事件,如gc cr block receive time,这可能意味着跨实例的数据块传输延迟,需要优化应用设计或整集群参数。 关于优化建议,可能需要从SQL优化、索引整、参数设置、硬件资源等方面入手。例如,如果发现某条SQL执行次数多且效率低,可以建议添加索引或重写SQL。如果共享池问题,可以整shared_pool_size或使用绑定变量。对于I/O问题,可能需要优化存储或增加buffer cache。 最后,生成相关问题需要考虑用户可能进一步想了解的内容,比如如何分析Top SQL,RAC环境特有的问题,或者如何整AWR配置等。 现在需要确保回答结构清晰,分步骤分析各个指标,并结合引用中的例子和用户的环境给出具体建议。同时,要使用正确的LaTeX格式,行内公式用$...$,独立公式用$$...$$。需要检查是否有遗漏的关键点,比如版本和平台相关的注意事项,比如11g和RAC环境下的特定参数或行为。</think>以下是通过Oracle AWR报告定位性能瓶颈及优化方案的逐步分析框架,结合您提供的11.2.0.4.0版本和RAC环境特性: --- ### 一、DB Time分析(核心指标) $$DB\ Time = DB\ CPU + Non-Idle\ Wait\ Time$$ 1. **计算逻辑**:参考引用[3]的公式,需验证`DB Time`是否`(DB CPU + Non-Idle Wait)/60`匹配。若差异大,可能存在隐藏问题 2. **判断标准**: - 当$DB\ Time \gg Elapsed\ Time$时,说明存在资源竞争或低效SQL - RAC环境中需关注`gc cr block receive`等全局缓存等待事件 3. **优化方向**:若DB CPU占比超过80%,优化CPU消耗高的SQL;若等待事件主导,则针对性解决锁争用或I/O问题[^3] --- ### 二、Sessions负载分析 1. **活动会话数**: - 检查`Active Sessions``Hard Parses/sec`,若$Hard\ Parses/sec > 100$,可能缺少绑定变量[^5] - RAC环境下需观察各节点会话分布是否均衡 2. **负载概要**: - 逻辑读过高(`Logical Reads/Transaction > 1000`)需检查全表扫描 - 物理读高时,关注`Buffer Cache Hit Ratio`(应>90%),否则需整内存或SQL --- ### 三、Cursors/SessionSQL分析 1. **游标问题**: - 若`Cursors/Session > 50`,可能引发共享池争用,检查`latch: shared pool`等待 - 执行`alter system set session_cached_cursors=100;`优化游标缓存 2. **Top SQL定位**: - 分析`SQL ordered by Elapsed Time`和`Executions` - 案例:某SQL执行$10^6$次且单次$CPU\ Time=0.1ms$,总耗时为$100s$(累积效应显著) --- ### 四、等待事件深度分析 | 等待事件类型 | 优化建议 | |-----------------------|--------------------------------------------------------------------------| | `db file sequential read` | 优化索引(检查$CLUSTERING\_FACTOR$)或减少单行访问 | | `enq: TX - row lock contention` | 检查事务提交频率,使用`SELECT * FROM DBA_BLOCKERS`定位锁源 | | `gc buffer busy` | RAC优化整`_lm_dd_interval`或使用服务分区[^2] | --- ### 五、RAC环境专项检查 1. **全局缓存效率**: - `Global Cache Blocks Lost%`应<2%,否则检查网络延迟 - 使用`gv$cr_block_server`分析块传输效率 2. **实例负载均衡**: - 对比各实例的`DB Time`,若偏差>30%,需整服务配置 --- ### 六、优化方案示例 1. **SQL优化**: ```sql -- 高频低效SQL添加绑定变量 SELECT /*+ INDEX(emp emp_name_idx) */ * FROM emp WHERE name=:v1; ``` 2. **参数整**: ```sql alter system set "_optim_peek_user_binds"=false; -- 避免绑定变量窥探副作用 ``` 3. **硬件建议**:若`Avg Read I/O Response Time > 20ms`,需升级存储或启用Flash Cache ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值