ORACLE中行锁问题排查手段
原文：https://blog.51cto.com/u_13482808/6535486概念描述
行锁，对应等待事件’enq: TX - row lock contention’。是应用环境中经常碰到的故障现象。当发生行锁时，往往意味着大量业务会话被阻塞。造成业务功能无法进行。因此需要尽快排查出问题源头及原因。采取有效的处理措施。
关于行锁等待事件enq: TX - row lock contention ，通常是Application级别的问题。常见的TX锁等待原因：
1 应用代码逻辑层有问题，导致同时修改相同数据引发锁等待。
2 应用代码逻辑层有问题，导致事务不提交引发锁等待。
3 主键或者唯一键冲突引发锁等待。
4 位图索引维护引发锁等待。
5 事务回滚导致的锁等待。
6 慢SQL导致的锁等待。
根据经验，大多数行锁的产生都来自于事务未能及时提交、SQL低效等原因。当发生行锁问题时，对应用的影响是很大的，应用会报出无法完成正常事务。就需要快速的排查问题原因，并通过相应手段避免行锁持续的影响。
行锁问题排查：
为了演示发生行锁问题时如何快速排查。设计了如下脚本：
实验脚本：
create table t1 as select * from dba_objects;
--会话1：对目标行做更新，但不提交
update t1 set OBJECT_NAME=OBJECT_NAME||'1' where object_id=110;
--会话2：模拟其他应用对相同行的更新
begin
p_test_update;
end;
/
存储过程中的代码如下：
create or replace procedure sys.p_test_update
as
l_cnt number;
begin
update sys.t1 set OBJECT_NAME=OBJECT_NAME||'AAA' where object_id=110;
commit;
end;
--会话3：有用户视图编译该程序
alter procedure p_test_update compile;
--会话4：新的会话继续更新相同行
update t1 set OBJECT_NAME=OBJECT_NAME||'BBB' where object_id=110;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
通过上述脚本，模拟了两条阻塞链：
阻塞链1：由于会话1未提交导致的后续应用及编译动作均被阻塞；
阻塞链2：会话1未提交导致的其他会话相同行更新被阻塞。
如下展示如何快速的获得阻塞基本情况：
备份数据
由于行锁问题的影响较大，发生阻塞时为了降低对业务的影响。部分场景可能需要通过重启应用等方式来打破锁源头的持有。为了在事后能获得阻塞的相关信息。需要通过脚本，将容易丢失的会话数据备份到物理表中：
CREATE TABLE OPEN_TABLEBAK AS select * from gv$open_cursor;
CREATE TABLE ASH_TABLEBAK AS select * from gv$active_session_history;- 1.
- 2.
查询锁及当前SQL
当锁正在发生时，可以及时查看当前正在执行语句及锁定情况：
1.获取锁模式及相关对象：
col OWNER for a10
col OBJECT_NAME for a20
select a.inst_id,a.sid,a.type,a.id1,a.id2,b.owner,b.object_name,a.lmode,a.request
from gv$lock a,dba_objects b,gv$locked_object c
where a.type in ('TX')
and a.inst_id=c.inst_id(+)
and a.sid=c.SESSION_ID(+)
and c.object_id=b.object_id(+)
order by a.inst_id,a.sid;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
锁为6级排它锁。确定为更新相同数据导致。
2.查看阻塞对象及会话源头：
set lines 200 pages 40
col SID for 9999
col OWNER for a10
col OBJECT_NAME for a20
col event for a30
col b_sid for 9999
col f_b_sid for 9999
SELECT T2.SID,
T2.SERIAL#,
T3.OWNER,
T3.OBJECT_NAME,
t2.event,blocking_session as b_sid,final_blocking_session as f_b_sid,
t2.p1,t2.p2,t2.p3,round(t2.wait_time_micro/1E6,4) waittime,
T2.LOGON_TIME,t1.LOCKED_MODE
FROM GV$LOCKED_OBJECT T1, GV$SESSION T2, DBA_OBJECTS T3
WHERE T1.SESSION_ID = T2.SID and t1.INST_ID=t2.INST_ID
AND T1.OBJECT_ID = T3.OBJECT_ID
ORDER BY T2.LOGON_TIME;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
会话20/21，均被会话137锁阻塞。
3.查看正在执行语句：获取对应会话当前SQL。
col username format a13
col prog format a10 trunc
col sql_text format a60 trunc
col sid format a12
col sql_id format a16
col child for 99999
col execs format 9999999
col sqlprofile format a22
col avg_ela for 999999.99
col last_ela for 999999
col event format a15
select
sid||','||serial# sid,
substr(a.event,1,15) event,
b.sql_id||','||child_number sql_id,
a.inst_id,
plan_hash_value,
executions execs,
(elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_ela,
last_call_et last_ela,
sql_text
from gv$session a, gv$sql b
where status = 'ACTIVE'
and username is not null
and a.sql_id = b.sql_id
and a.sql_child_number = b.child_number
and a.inst_id=b.INST_ID
and sql_text not like '%from gv$session a, gv$sql b%'
and a.program not like '%(P%)';- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
当前正在执行3条语句。更新T1表时发生行锁。阻塞源头会话为137。但会话12等待事件为SQL*Net message from client。怀疑当前会话为空闲状态。
查询阻塞链
1.当锁正在发生时，通过gv$session查看阻塞链：
select *
from (select a.inst_id, a.sid, a.serial#,
a.sql_id,
a.event,
a.status,
connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
level as tree_level
from gv$session a
start with a.blocking_session is not null
connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
where isleaf = 1
order by tree_level asc;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
阻塞链情况：包含两条阻塞链
1节点的137会话，阻塞了20会话，20会话又阻塞了138会话。
1节点137会话，阻塞链1节点21会话。
2.如果锁已经不存在，需要通过ASH视图/ASH备份表来查询阻塞链：
col lock_chain for a75
col EVENT_CHAIN for a50
col first_seen for a18
col last_seen for a18
col BLOCKING_HEADER for a10
with ash as (
select *
from gv$active_session_history
where sample_time>=to_date('2023-06-23 14:30:52','yyyy-mm-dd hh24:mi:ss')
and sample_time< to_date('2022-06-23 15:30:52','yyyy-mm-dd hh24:mi:ss')),
ash2 as (
select sample_time,inst_id,session_id,session_serial#,sql_id,sql_opname,
event,blocking_inst_id,blocking_session,blocking_session_serial#,
level lv,
connect_by_isleaf isleaf,
sys_connect_by_path(inst_id||'_'||session_id||'_'||session_serial#||':'||sql_id||':'||sql_opname,'->') lock_chain,
sys_connect_by_path(EVENT,',') EVENT_CHAIN ,
connect_by_root(inst_id||'_'||session_id||'_'||session_serial#) root_sess
from ash
--start with event like 'enq: TX - row lock contention%'
start with blocking_session is not null
connect by nocycle
prior blocking_inst_id=inst_id
and prior blocking_session=session_id
and prior blocking_session_serial#=session_serial#
and prior sample_id=sample_id)
select lock_chain lock_chain,EVENT_CHAIN,
case when blocking_session is not null then blocking_inst_id||'_'||blocking_session||'_'||blocking_session_serial# else inst_id||'_'||session_id||'_'||session_serial# end blocking_header,
count(*) cnt,
TO_CHAR(min(sample_time),'YYYYMMDD HH24:MI:ss') first_seen,
TO_CHAR(max(sample_time),'YYYYMMDD HH24:MI:ss') last_seen
from ash2
where isleaf=1
group by lock_chain,EVENT_CHAIN,case when blocking_session is not null then blocking_inst_id||'_'||blocking_session||'_'||blocking_session_serial# else inst_id||'_'||session_id||'_'||session_serial# end
having count(*)>1
order by first_seen, cnt desc;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
带入发生行锁的时间段。
1节点的137会话，阻塞了20会话，20会话又阻塞了138会话。
1节点137会话，阻塞链1节点21会话。
除此之外还能看到对应的等待事件、SQL语句及其类型。3.还可以借助WAIT_CHAINS脚本，进一步帮助确定问题源头：
脚本内容较长，参见附件文件。
使用方法：1）将脚本放入当前目录；2）@wait_chains.sql
col "%This" for a10
col WAIT_CHAIN for a55
col FIRST_SEEN for a20
col last_seen for a20
WITH
bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat),
ash AS (SELECT /*+ INLINE QB_NAME(ash) LEADING(a) USE_HASH(u) SWAP_JOIN_INPUTS(u) */
a.*
, o.*
, u.username
, CASE WHEN a.session_type = 'BACKGROUND' AND a.program LIKE '%(DBW%)' THEN
'(DBWn)'
WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
ELSE
'('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END || ' ' program2
, NVL(a.event||CASE WHEN event like 'enq%' AND session_state = 'WAITING'
THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
THEN ' ['||NVL((SELECT class FROM bclass WHERE r = a.p3),'undo @bclass '||a.p3)||']' ELSE null END,'ON CPU')
|| ' ' event2
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
, CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END
||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END
||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END
||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END
||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END
||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END
||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END
||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END
||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
FROM
gv$active_session_history a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
WHERE
a.user_id = u.user_id (+)
AND a.current_obj# = o.object_id(+)
AND sample_time BETWEEN sysdate-1/24 AND sysdate
),
ash_samples AS (SELECT /*+ INLINE */ DISTINCT sample_id FROM ash),
ash_data AS (SELECT /*+ INLINE */ * FROM ash),
chains AS (
SELECT /*+ INLINE */
sample_time ts
, level lvl
, session_id sid
, REPLACE(SYS_CONNECT_BY_PATH(username||':'||program2||event2, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL THEN ' -> [idle blocker '||d.blocking_inst_id||','||d.blocking_session||','||d.blocking_session_serial#||(SELECT ' ('||s.program||')' FROM gv$session s WHERE (s.inst_id, s.sid , s.serial#) = ((d.blocking_inst_id,d.blocking_session,d.blocking_session_serial#)))||']' ELSE NULL END path -- there's a reason why I'm doing this
, CASE WHEN CONNECT_BY_ISLEAF = 1 THEN d.session_id ELSE NULL END sids
, CONNECT_BY_ISLEAF isleaf
, CONNECT_BY_ISCYCLE iscycle
, d.*
FROM
ash_samples s
, ash_data d
WHERE
s.sample_id = d.sample_id
AND d.sample_time BETWEEN sysdate-1/24 AND sysdate
CONNECT BY NOCYCLE
( PRIOR d.blocking_session = d.session_id
AND PRIOR d.blocking_inst_id = d.inst_id
AND PRIOR s.sample_id = d.sample_id
)
START WITH session_type='FOREGROUND'
)
SELECT * FROM (
SELECT
LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
, COUNT(*) seconds
/* , ROUND(COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-1/24 AS DATE)) * 86400), 1) AAS */
, COUNT(DISTINCT sids) distinct_sids
, path wait_chain
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
FROM
chains
WHERE
isleaf = 1
GROUP BY
username||':'||program2||event2
, path
ORDER BY
COUNT(*) DESC
)
WHERE
ROWNUM <= 30;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
- 120.
- 121.
- 122.
- 123.
- 124.
- 125.
- 126.
- 127.
- 128.
- 129.
- 130.
- 131.
- 132.
- 133.
- 134.
- 135.
- 136.
- 137.
- 138.
- 139.
- 140.
- 141.
- 142.
- 143.
- 144.
- 145.
- 146.
- 147.
- 148.
- 149.
- 150.
- 151.
- 152.
- 153.
- 154.
- 155.
- 156.
- 157.
- 158.
- 159.
- 160.
- 161.
- 162.
- 163.
- 164.
- 165.
- 166.
- 167.
- 168.
- 169.
- 170.
- 171.
- 172.
- 173.
- 174.
- 175.
- 176.
- 177.
- 178.
- 179.
- 180.
- 181.
- 182.
- 183.
- 184.
- 185.
- 186.
- 187.
- 188.
- 189.
- 190.
- 191.
- 192.
- 193.
- 194.
- 195.
- 196.
- 197.
- 198.
- 199.
- 200.
- 201.
- 202.
- 203.
- 204.
- 205.
- 206.
- 207.
默认查询1小时内的阻塞情况，如需调整时间可以修改脚本中的sample_time范围。
可以看到影响最大的是两条阻塞链。源头均为会话137。与前述查询的阻塞链条一致。
SECONDS：阻塞持续了多少秒；
DISTINCT_SIDS：该会话阻塞了多少会话数。
%This：会话阻塞占比程度。
查询源头SQL
除了找到上述阻塞链情况，有时候还希望找到对应的源头会话在执行什么SQL，为什么会产生阻塞。则需要通过下面脚本去排查。但能否找出原始SQL受到源头会话的SQL执行情况等影响。不一定能找到。
1.通过未提交事务查SQL信息：带入持有锁的会话ID
如果存在未提交的事务，查看该事务上的相关语句。分析是否存在未提交或部分低效语句导致整个事务未能提交。
col sql_text for a60
col MODULE for a20 trunc
col MACHINE for a10 trunc
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
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='137'
order by start_time;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
其中UPDATE为未提交语句，但同时后续还执行了多条查询类语句，其执行效率较低，也可能是整个事务未提交的原因。
2.通过游标找未提交SQL：带入持有锁的会话ID
col user_name for a15
col CURSOR_TYPE for a20 trunc
SELECT inst_id,sid,user_name,sql_id,sql_text,last_sql_active_time,sql_exec_id,cursor_type FROM GV$OPEN_CURSOR WHERE SID='137';- 1.
- 2.
- 3.
如果是未提交语句，且通过方法1没有找到SQL语句，可以看下游标中是否有记录。如果有备份。直接查询游标的备份表。
第一次查询找到了未提交更新语句。
后续该会话又执行了其他语句，重复查询时，可能找不到源头语句。再查询备份表
col user_name for a15
col CURSOR_TYPE for a20 trunc
SELECT inst_id,sid,user_name,sql_id,sql_text,last_sql_active_time,sql_exec_id,cursor_type FROM OPEN_TABLEBAK WHERE SID='137';- 1.
- 2.
- 3.
通过备份表还是可以查到源头信息的，因此备份游标表这步还是比较关键的。
3.通过ASH视图尝试获取SQL信息：带入持有锁的会话ID及查询时间段等
col SQL_TEXT for a75 trunc
col MODULE for a13 trunc
col MACHINE for a10 trunc
col OBJECT for a10 trunc
col start_time for a11
col exec_time for a9
col cnt for 9999
col INST for 9
select session_id SID,
t1.MODULE,
t1.MACHINE,
cnt,
T1.INST,
SQ.SQL_ID,
SQ.SQL_TEXT,
ob.object_name OBJECT,
start_time,
exec_time
from (select inst_id INST,
session_id,
SQL_ID,
SQL_EXEC_ID,
MODULE,
MACHINE,
CURRENT_OBJ#,
count(*) AS cnt,
to_char(sql_exec_start, 'mm-dd hh24:mi') as start_time,
SUBSTR(max(sample_time) - sql_exec_start, 11, 9) as exec_time
from gv$active_session_history
where session_id = '137'
and sample_time >=
to_date('2023-06-22 23:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by inst_id,
session_id,
SQL_ID,
SQL_EXEC_ID,
MODULE,
MACHINE,
CURRENT_OBJ#,
sql_exec_start) t1
left join gv$sql SQ
ON T1.SQL_ID = SQ.SQL_ID
and t1.INST = SQ.inst_id
left join dba_objects ob
on T1.CURRENT_OBJ# = ob.object_id
order by start_time;- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
该会话上执行过的SQL会记录，但如果未提交语句执行较快，则不一定能找到。
知识总结
通过上述演示，模拟了源头未提交语句的行锁阻塞分析过程。实际场景中可能等待情况会更加复杂，但上述查询步骤仍然有效。需要利用好ASH视图及gv$open_cursor。
标签：POWER,中行,SQL,col,排查,session,time,ORACLE,id From： https://www.cnblogs.com/redarmy/p/17685201.html