Instance 2 SCOTT@ojndev42 ( SID= 998,21071,@2 ACTIVE ) is blocking SCOTT@ojndev42 ( SID=671 ) afphmw0pd08xq
Instance 2 SCOTT@ojndev42 ( SID= 998,21071,@2 ACTIVE ) is blocking SCOTT@ojndev41 ( SID=10 ) 1swzb3n1zd9vp
可以看到的是执行的第二个SQL语句
SELECT inst_id,sql_text,SQL_id,module FROM gv$sql WHERE sql_id = (SELECT NVL(prev_sql_id,'1') FROM gv$session WHERE sid=998 and inst_id=2);
INST_ID SQL_TEXT SQL_ID MODULE
2 delete from emp where empno=7839 9y17vzq06y7sz SQL*Plus
查杀试试
alter system kill session '998,21071,@2' immediate;
释放了其中一条，但是源头还是卡住，而且再用这个去查，其实是查不到了
网上的SQL
SELECT distinct status,
state,
event,
blocking_session BLS,
' alter system kill session ''' || b.sid || ',' ||
b.serial# ||',@' || b.inst_id || ''' immediate;',
c.cpu_time,
c.elapsed_TIME,
c.SQL_TEXT,
c.SQL_ID,
b.username,
b.sid,
b.serial#,
logon_time,
osuser OUSER,
machine,
LAST_ACTIVE_TIME,
b.*
FROM gv$session b, gv$sqlarea c
WHERE b.SQL_ID = c.SQL_ID
and b.inst_id = c.inst_id
--and EVENT = 'enq: TX - row lock contention'
--and upper(sql_text )like '%SYS_FLOW_TASK_SH%'
--and sid = '2545'
and username = 'CWBASE21_9999'
order by CPU_TIME desc, c.SQL_TEXT, b.logon_time;
null 通过ELAPSED_TIME判断锁定时间长度，然后根据锁定时间最长的那行，找到BLS，锁定会话源头，根据这个再进行查找锁源头。
oradebug
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /u01/app/oracle/diag/rdbms/oj4/oj42/trace/ojndev42_diag_27399.trc
*** 2025-04-11 11:40:53.174
REQUEST:custom dump [6] with parameters [51][56][1415053314][2], requested by (instance=0, osid=30184), summary=[ges process stack dump (kjdglblkrdm1)].
Performing custom dump
Dump is written to /u01/app/oracle/diag/rdbms/ojndev4/ojndev42/trace/ojndev42_diag_27399_3.trc
End of custom dump
*** 2025-04-14 09:21:26.438
DIAG sends out oradebug command to nodes [0,1]
****************************************************************
PORADEBUG BEGIN ORIGINATING INST:2 SERIAL:0 PID:12507
PORADATA COMMAND:hanganalyze 3
session (kjzddbx) switches to a new action
PORADATA TRACEFILE /u01/app/oracle/diag/rdbms/ojndev4/ojndev42/trace/ojndev42_diag_27399.trc
PORADEBUG END ORIGINATING INST:2 SERIAL:0 PID:12507
****************************************************************
*** 2025-04-14 09:21:26.932
===============================================================================
HANG ANALYSIS:
instances (db_name.oracle_sid): ojndev4.ojndev42, ojndev4.ojndev41
oradebug_node_dump_level: 3
analysis initiated by oradebug
os thread scheduling delay history: (sampling every 1.000000 secs)
0.000000 secs at [ 09:21:26 ]
NOTE: scheduling delay has not been sampled for 0.412849 secs 0.000000 secs from [ 09:21:22 - 09:21:27 ], 5 sec avg
0.000000 secs from [ 09:20:27 - 09:21:27 ], 1 min avg
0.000000 secs from [ 09:16:27 - 09:21:27 ], 5 min avg
vktm time drift history
===============================================================================
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x42598823 Chain 2 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
Chain 2 Signature Hash: 0x42598823
[c] Chain 3 Signature: <not in a wait>
Chain 3 Signature Hash: 0x673a0128
===============================================================================
Sessions in an involuntary wait or not in a wait:
-------------------------------------------------------------------------------
Chain 3:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (ojndev4.ojndev41)
os id: 14468
process id: 64, oracle@ojndev41 (J000)
session id: 6
session serial #: 959
}
is not in a wait:
{
last wait: 5 min 16 sec ago
blocking: 0 sessions
current sql: SELECT /*+ push_pred(v) */ U.NAME, OT.NAME, OT.SUBNAME, V.SPART, V.NAME, V.GLOB_ST, V.USER_ST, V.DISTCNT, V.DENSITY, V.SAMPLE_DISTCNT, V.SSIZE, V.NULL_CNT, V.TIMESTAMP#, V.AVGCLN, V.LOWVAL, V.HIVAL, V.MINIMUM, V.MAXIMUM, V.DMIN, V.DMAX, V.CCNT, V.BUCKET, V.ENDPOINT, V.EPVALUE, V.EAVS, V.FLAGS FROM SYS.USER$ U, SYS.OBJ
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-lxoCpStr()+55<-lxdprl()+643<-LdiDateToString()+4641<-pfrcvfc_format_conversion()+572<-pevm_CNVMSC()+34<-pfrinstr_CNVMSC()+50<-pfrrun_no_tool()+63<-pfrrun()+627<-plsql_run()+649<-peicnt()+302<-kkxexe()+503<-opiexe()+18001<-opiodr()+917<-rpidrus()+211<-skgmstack()+148<-rpiswu2()+690<-rpidrv()+1327<-rpiexe()+74<-kkjex1e()+8679<-kkjsexe()+711<-kkjrdp()+694<-opirip()+958<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain()+265<-main()+20
wait history:
1. event: 'latch: row cache objects'
time waited: 0.000010 sec
wait id: 19588317 p1: 'address'=0x8947cdfb8
p2: 'number'=0x135
p3: 'tries'=0x0
* time between wait #1 and #2: 9 min 0 sec
2. event: 'latch: row cache objects'
time waited: 0.000004 sec
wait id: 19588316 p1: 'address'=0x8947cdfb8
p2: 'number'=0x135
p3: 'tries'=0x0
* time between wait #2 and #3: 4 min 44 sec
3. event: 'latch: row cache objects'
time waited: 0.000004 sec
wait id: 19588315 p1: 'address'=0x8947cdfb8
p2: 'number'=0x135
p3: 'tries'=0x0
}
Chain 3 Signature: <not in a wait>
Chain 3 Signature Hash: 0x673a0128
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Chain 4:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (ojndev4.ojndev41)
os id: 20240
process id: 99, oracle@ojndev41
session id: 997
session serial #: 21303
}
is waiting for 'gc current request' with wait info:
{
p1: 'file#'=0xe
p2: 'block#'=0x63d15
p3: 'id#'=0x2010001
time in wait: 0.002025 sec
timeout after: never
wait id: 90569
blocking: 0 sessions
current sql: DELETE FROM REPORT_TEMP2
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-times()+12<-kcbchg1_main()+3755<-kcbchg1()+205<-ktuchg2()+1420<-ktbchg2()+281<-kddchg()+1080<-kdddrp()+2280<-kdddel()+853<-kaudel()+115<-delrow()+1496<-qerdlFetch()+1822<-delexe()+1001<-opiexe()+10315<-kpoal8()+2392<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245
wait history:
* time between current wait and wait #1: 0.000222 sec
1. event: 'gc current grant 2-way'
time waited: 0.004638 sec
wait id: 90568 p1: ''=0x3
p2: ''=0x5e4e5
p3: ''=0x2000054
* time between wait #1 and #2: 0.000361 sec
2. event: 'gc current grant 2-way'
time waited: 0.001437 sec
wait id: 90567 p1: ''=0x3
p2: ''=0x5e4e4
p3: ''=0x2000054
* time between wait #2 and #3: 0.000319 sec
3. event: 'gc current grant 2-way'
time waited: 0.000108 sec
wait id: 90566 p1: ''=0x3
p2: ''=0x5e4e3
p3: ''=0x2000054
}
-------------------------------------------------------------------------------
Chain 5:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (ojndev4.ojndev41)
os id: 30719
process id: 10, oracle@ojndev41 (DIA0)
session id: 3301
session serial #: 1
}
is not in a wait:
{
last wait: 0.006684 sec ago
blocking: 0 sessions
current sql: <none>
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-__poll()+16<-sskgxp_selectex()+392<-skgxpiwait()+4318<-skgxpwaiti()+1916<-skgxpwait()+162<-ksxpwait()+2501<-ksxprcv_int()+5363<-ksxprcvimd()+36<-kjzcwait()+302<-kjzcrcv()+409<-kjzfrcv()+479<-kjzgpoll()+753<-kjzdm_rac()+1003<-kjzdm()+1252<-ksbrdp()+1045<-opirip()+623<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245
wait history:
1. event: 'DIAG idle wait'
time waited: 0.010070 sec
wait id: 79886290 p1: 'component'=0x1
p2: 'where'=0x1
p3: 'wait time(millisec)'=0xa
* time between wait #1 and #2: 0.000046 sec
2. event: 'DIAG idle wait'
time waited: 0.184270 sec
wait id: 79886289 p1: 'component'=0x1
p2: 'where'=0x1
p3: 'wait time(millisec)'=0xb4
* time between wait #2 and #3: 0.000055 sec
3. event: 'DIAG idle wait'
time waited: 0.204301 sec
wait id: 79886288 p1: 'component'=0x1
p2: 'where'=0x1
p3: 'wait time(millisec)'=0xc8
}
Chain 5 Signature: <not in a wait>
Chain 5 Signature Hash: 0x673a0128
-------------------------------------------------------------------------------
从上往下推导到源头，chain代表一个链条，看到chain最后的一条即可
instance: 1 (ojndev4.ojndev41)
os id: 7465
process id: 89, oracle@ojndev41 (TNS V1-V3)
session id: 2984
session serial #: 37021
blocking: 3 sessions
current sql: <none>
快速理解锁的流程，配合开发定位问题，但是没法查到导致锁的语句是哪个。
Part4知识扩展
在锁的源头再加上一条查询语句
SQL> conn scott/scott
Connected.
SQL> select * from v$mystat where rownum =1;
SID STATISTIC# VALUE
---------- ---------- ----------
2984 0 0
SQL> update emp set sal=8000 where empno=7788;
1 row updated.
我再加上执行这个SQL
SQL> select * from v$mystat where rownum =1;
SID STATISTIC# VALUE
---------- ---------- ----------
2984 0 0
再查SQL就变成查询语句了，这就是大家的疑惑，查询SQL也能锁表
SELECT inst_id,sql_text,SQL_id,module FROM gv$sql WHERE sql_id = (SELECT NVL(prev_sql_id,'1') FROM gv$session WHERE sid=2984 and inst_id=1);
INST_ID SQL_TEXT SQL_ID MODULE
1 1 select * from v$mystat where rownum =1 df8hd7yrp3avs SQL*Plus
2 2 select * from v$mystat where rownum =1 df8hd7yrp3avs SQL*Plus
如果是在事后追寻，而且流程里边已经已经执行了新的SQL那就代表没用了，只能抓到这个锁的会话，却没有办法抓到锁的SQL。
Part5思考思路
其中一个方便理解的SQL
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,
'Number of waiters: '||num_waiters waiters,
'Final Blocking Process: '||decode(p.spid,null,'',
p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
'Program: '||p.program image,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
gv$session s,
gv$session bs,
gv$instance i,
gv$process p
WHERE wc.instance = i.instance_number (+)
AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
and wc.sess_serial# = s.serial# (+))
AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;
Part6SQL使用排序
SELECT distinct status,
state,
event,
blocking_session BLS,
' alter system kill session ''' || b.sid || ',' ||
b.serial# ||',@' || b.inst_id || ''' immediate;',
c.cpu_time,
c.elapsed_TIME,
c.SQL_TEXT,
c.SQL_ID,
b.username,
b.sid,
b.serial#,
logon_time,
osuser OUSER,
machine,
LAST_ACTIVE_TIME,
b.*
FROM gv$session b, gv$sqlarea c
WHERE b.SQL_ID = c.SQL_ID
and b.inst_id = c.inst_id
--and EVENT = 'enq: TX - row lock contention'
--and upper(sql_text )like '%SYS_FLOW_TASK_SH%'
--and sid = '2545'
and username = 'CWBASE21_9999'
order by CPU_TIME desc, c.SQL_TEXT, b.logon_time;
select a.inst_id,
a.process,
a.sid,
a.serial#,
a.sql_id,
a.event,
a.status,
a.program,
a.machine,
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);
SELECT 'Instance '||s1.INST_ID||' '|| s1.username || '@' || s1.machine
|| ' ( SID= ' || s1.sid || ','|| s1.serial#||',@'||s1.INST_ID||' '||s1.status|| ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' ||s2.sql_id
FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
WHERE s1.sid=l1.sid AND
s1.inst_id=l1.inst_id AND
s2.sid=l2.sid AND
s2.inst_id=l2.inst_id AND
l1.BLOCK=1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l2.id2 = l2.id2 ;
这三条下来基本能干掉的都干掉了