第一章 适用范围

本案例中的问题现象发生于当前主流的ORACLE 11G环境。不排除随着后续版本的升级可能会有一定细微的差异表现。但问题的原因及解决方案，在所有版本中都是通用的。
数据库版本:ORACLE 11G

第二章 问题概述

应用人员反馈，当前数据库环境运行批处理作业效率异常。相同作业在DG库上均能较快完成。而当前为生产主库，主机性能远高于DG库。因此不应该出现跑批效率问题。据应用人员分析，怀疑为IO问题。
为此。需要排查当前数据库环境以确定问题根本原因。

第三章 问题排查步骤

1.首先查看当前活动会话：

select sid,
       serial#,
       username,
       sql_id,
       program,
       audsid,
       to_char(sql_exec_start, 'yyyymmdd_hh24miss') sql_exec_start,
       event,
       final_blocking_instance block_inst,
       final_blocking_session block_sid
  from v$session
 where username is not null
   and status = 'ACTIVE'
 order by logon_time;

复制

当前会话中存在一条低效SQL。且该SQL导致了其他两个会话行锁。

对应的SQL如下：

相应会话中：
3484为一条慢SQL。走直接路径读。
两条更新语句，被3484会话阻塞。导致行锁。

2.分析阻塞事务情况：

select t1.sql_id,
       t1.user_name,
       t2.executions,
       t1.saddr,
       round(t2.buffer_gets / decode(t2.executions, 0, -1, t2.executions),
             2) pcb,
       t2.BUFFER_GETS,
       t2.ELAPSED_TIME / power(10, 6),
       round(t2.elapsed_time / decode(t2.executions, 0, -1, t2.executions)/power(10, 6),2) pct,
       substr(t2.sql_text,1,40) sql_text
       --t2.*,
       --t1.*
  from v$open_cursor t1, v$sql t2
 where t1.sql_id = t2.sql_id
   and t1.sid = '&sid'
   and t1.user_name=upper('&uname')
 order by t2.ELAPSED_TIME / decode(t2.executions, 0, -1, t2.executions) desc;

复制

查询对应事务的SID，存在多条SQL信息，其中影响最大的为1jcysy3a5zkkr。也就是上面看到的阻塞会话正在执行的SQL。除此之外，当前事务中还包含了会话中的行锁SQL。也就是（bf4z87a6uf01x）。这里分析正是该事务中的相同SQL相同行的更新未提交，导致了其他会话的行锁现象。

进一步对比事务的开始时间。

select distinct t1.SID,
                 t1.SERIAL#,
                 nvl(t2.SQL_text, t4.SQL_text) SQL_TEXT,
                 t3.SQL_ID,t3.MODULE,t3.MACHINE,
				 round(t2.ELAPSED_TIME/1E6,1) AS els_s,to_char(t3.sql_exec_start, 'mm-dd hh24:mi') as start_time,t.start_time as START_vess
   from gv$transaction            t,
        gv$session                t1,
        gv$sql                    t2,
        gv$active_session_history t3,
        dba_hist_sqltext         t4
  where t.SES_ADDR = t1.SADDR and t.INST_ID=t1.INST_ID
    and t1.SID = t3.session_id
    and t1.SERIAL# = t3.session_serial# and t1.inst_id=t3.inst_id
    and t3.SQL_ID = t2.SQL_ID(+)
    and t3.SQL_ID = t4.SQL_ID(+)
	and t1.SID='3484'
	order by start_time;

复制

可以看到对应事务在前一日的0点就已经执行了。但是其中的一条查询1jcysy3a5zkkr执行较慢，且该事物中还包含对目标表的UPDATE更新（bf4z87a6uf01x）。整个事务执行不完，也就导致目标表的更新得不到提交。

此时后续会话再次运行目标表的更新时，造成行锁问题。这里分析清了问题根源。就是问题SQL导致的事务执行较长，进而导致DML语句无法提交。

第四章 解决方案

如下SQL文本：

select count(:"SYS_B_0")
from (select :"SYS_B_1" from t_bny,
     trc
where trc.case_id = t_bny.busi_id
  and t_bny.type_id = :"SYS_B_2"
  and trc.case_id = :"SYS_B_3"
union
select :"SYS_B_4"
from t_bn,
     trc
where trc.case_id = t_bn.busi_id
  and t_bn.type_id = :"SYS_B_5"
  and trc.case_id = :"SYS_B_6");

复制

执行计划如下：

其中执行较慢为T_BN的全表扫描步骤。但经过分析，根本原因是BUSI_ID列的隐式转换导致无法利用上相应索引。才出现的全表扫描问题。

关联列的类型不一致，就导致出现了隐式转换问题。

且这里该列上是存在索引的。

因此这里分析最好的优化方案是修改关联字段为相同类型。

但这里相应表上的数据量巨大，直接修改字段类型可能风险较大。为了尽快降低问题影响。与应用沟通，优先调整SQL，在关联列部分通过显示的TO_CHAR转换，来避免目标表上的转换发生在索引列上面，出现的索引不可用问题。

select count(:"SYS_B_0")
from (select :"SYS_B_1" from T_BNY,
     trc
where to_char(trc.case_id) = T_BNY.busi_id
  and T_BNY.type_id = :"SYS_B_2"
  and trc.case_id = :"SYS_B_3"
union
select :"SYS_B_4"
from t_bn,
     trc
where to_char(trc.case_id) = t_bn.busi_id
  and t_bn.type_id = :"SYS_B_5"
  and trc.case_id = :"SYS_B_6");
复制

调整后，不只避免了全表扫描大表的问题，还应用了更高效的索引。

避免了性能问题。

第五章 问题总结

本案例正是一条低效SQL，导致大事务一直执行较慢，其中的DML语句得不到即使提交，才造成的其余会话行锁问题。如果相应事务一直得不到处理，可能会引发更为严重的问题后果。