首页 > 代码库 > DFS lock handle & inactive transaction branch
DFS lock handle & inactive transaction branch
Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
DFS lock handle | 2,214 | 6,157 | 2781 | 66.08 | Other |
DB CPU |
| 2,107 |
| 22.62 |
|
inactive transaction branch | 1,046 | 1,046 | 1000 | 11.23 | Other |
cell smart table scan | 4,910 | 8 | 2 | 0.09 | User I/O |
SQL*Net more data to client | 356,561 | 7 | 0 | 0.07 | Network |
SQL> select session_id, user_id, sql_id, event,blocking_session ,count(event)
2 from v$active_session_history
3 where event = ‘DFS lock handle‘
4 group by session_id, user_id, sql_id, event,blocking_session
5 ;
SESSION_ID USER_ID SQL_ID EVENT BLOCKING_SESSION COUNT(EVENT)
---------- ---------- ------------- ---------------------------------------------------------------- ---------------- ------------
24 0 fpmrt9q566pwj DFS lock handle 1
1430 161 DFS lock handle 918 35906
2209 161 DFS lock handle 1949 35906
1563 57 6br3bdp02sujd DFS lock handle 1
SQL> select sql_text from v$sql where sql_id=‘6br3bdp02sujd‘
2 ;
SQL_TEXT
SELECT ATTACHED_SESSIONS FROM SYS.USER_DATAPUMP_JOBS WHERE JOB_NAME = :1
SELECT ATTACHED_SESSIONS FROM SYS.USER_DATAPUMP_JOBS WHERE JOB_NAME = :1
SQL> select distinct event,sql_id,session_id,blocking_session from v$active_session_history where session_id in (1949,918);
EVENT SQL_ID SESSION_ID BLOCKING_SESSION
---------------------------------------------------------------- ------------- ---------- ----------------
SQL*Net more data to client 3vtg8xc58vsux 918
SQL*Net more data to client 3vtg8xc58vsux 1949
SQL> select username,event,status,program,sql_id from v$session where sid in (1949,918);
USERNAME EVENT STATUS PROGRAM SQL_ID
------------------------------ ---------------------------------------------------------------- -------- ------------------------------------------------ -------------
DCDB_FOCENTER SQL*Net more data to client ACTIVE oracle@************b1 (TNS V1-V3) 3vtg8xc58vsux
DCDB_FOCENTER SQL*Net more data to client ACTIVE oracle@************b1 (TNS V1-V3) 3vtg8xc58vsux
两个会话的状态基本上没变过，该语句也很简单，所以判断两个会话有问题，经确认两个会话的应用已经关闭，决定kill 调，（需进一步关注该问题）；；
inactive transaction branch
SQL> select session_id, user_id, sql_id, event,blocking_session ,count(event)
2 from v$active_session_history
3 where event = ‘inactive transaction branch‘
4 group by session_id, user_id, sql_id, event,blocking_session;
SESSION_ID USER_ID SQL_ID EVENT BLOCKING_SESSION COUNT(EVENT)
---------- ---------- ------------- ---------------------------------------------------------------- ---------------- ------------
1430 161 inactive transaction branch 6345
2209 161 inactive transaction branch 6345
SQL> /
USERNAME EVENT STATUS SQL_ID BLOCKING_SESSION
------------------------------ ---------------------------------------------------------------- -------- ------------- ----------------
DCDB_FOCENTER DFS lock handle ACTIVE 918
DCDB_FOCENTER DFS lock handle ACTIVE 1949
SQL>
SQL> /
USERNAME EVENT STATUS SQL_ID BLOCKING_SESSION
------------------------------ ---------------------------------------------------------------- -------- ------------- ----------------
DCDB_FOCENTER inactive transaction branch ACTIVE
DCDB_FOCENTER inactive transaction branch ACTIVE
--进一步跟踪文件分析
SQL> oradebug setospid 81605
Oracle pid: 227, Unix process pid: 81605, image: oracle@
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump processstate 10
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/primary/foc2/trace/foc2_ora_81605.trc
---------------------------------------------------
[5 samples, 14:10:08 - 14:10:12]
waited for ‘DFS lock handle‘, seq_num: 126
p1: ‘type|mode‘=0x44580005
p2: ‘id1‘=0xd06e3e25
p3: ‘id2‘=0x0
time_waited: >= 4 sec (still in wait)
[1 sample, 14:10:07]
waited for ‘inactive transaction branch‘, seq_num: 125
p1: ‘branch#‘=0x0
p2: ‘waited‘=0x0
p3: ‘‘=0x0
time_waited: 1.000897 sec (sample interval: 0 sec)
[6 samples, 14:10:01 - 14:10:06]
waited for ‘DFS lock handle‘, seq_num: 124
p1: ‘type|mode‘=0x44580005
p2: ‘id1‘=0xd06e3e25
p3: ‘id2‘=0x0
time_waited: 6.084005 sec (sample interval: 5 sec)
[1 sample, 14:10:00]
waited for ‘inactive transaction branch‘, seq_num: 123
p1: ‘branch#‘=0x0
p2: ‘waited‘=0x0
p3: ‘‘=0x0
time_waited: 1.000656 sec (sample interval: 0 sec)
[6 samples, 14:09:54 - 14:09:59]
waited for ‘DFS lock handle‘, seq_num: 122
p1: ‘type|mode‘=0x44580005
p2: ‘id1‘=0xd06e3e25
p3: ‘id2‘=0x0
time_waited: 5.919257 sec (sample interval: 5 sec)
[1 sample, 14:09:53]
waited for ‘inactive transaction branch‘, seq_num: 121 【这个等待还不太清楚】
p1: ‘branch#‘=0x0
p2: ‘waited‘=0x0
p3: ‘‘=0x0
time_waited: 1.001862 sec (sample interval: 0 sec)
[6 samples, 14:09:47 - 14:09:52]
waited for ‘DFS lock handle‘, seq_num: 120
p1: ‘type|mode‘=0x44580005
p2: ‘id1‘=0xd06e3e25
p3: ‘id2‘=0x0
time_waited: 6.031084 sec (sample interval: 5 sec)
[1 sample, 14:09:46]
waited for ‘inactive transaction branch‘, seq_num: 119
p1: ‘branch#‘=0x0
p2: ‘waited‘=0x0
p3: ‘‘=0x0
time_waited: 1.000598 sec (sample interval: 0 sec)
[6 samples, 14:09:40 - 14:09:45]
PENDING_Q:
lp 0x7b1f6ab20 gl KJUSERNL rl KJUSEREX rp 0x7b5a38280 [0xd06e3e25][0x0],[DX][ext 0x0,0x0]
master 2 gl owner 0x7dce529a0 possible pid 81605 xid 0000-0000-00000000 bast 0 rseq 4030 mseq 0 history 0x49ab549a
convert opt KJUSERGETVALUE KJUSERNODEADLOCKWAIT KJUSERNODEADLOCKBLOCK
GRANTED_Q :
lp 0x7b9cabe30 gl KJUSERPR rp 0x7b5a38280 [0xd06e3e25][0x0],[DX][ext 0x0,0x0]
master 2 gl owner 0x7bcd44380 possible pid 81601xid 0000-0000-00000000 bast 0 rseq 4030 mseq 0 history 0x95514955
open opt KJUSERNO_XID
CONVERT_Q:
lp 0x7b1f6ab20 gl KJUSERNL rl KJUSEREX rp 0x7b5a38280 [0xd06e3e25][0x0],[DX][ext 0x0,0x0]
master 2 gl owner 0x7dce529a0 possible pid 81605xid 0000-0000-00000000 bast 0 rseq 4030 mseq 0 history 0x49ab549a
convert opt KJUSERGETVALUE KJUSERNODEADLOCKWAIT KJUSERNODEADLOCKBLOCK
SQL> select sid from v$session where paddr in (select addr from v$process where spid=81601);
SID
----------
918
----------------------------------------
SO: 0x7b12b6a38, type: 17, owner: 0x7b4a7e860, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x7b4a7e860, name=ksxp IPC state object, file=ksxp2.h LINE:1509, pg=0
KSXP Context allocated
Dumping ksxp state
ksxppg=0x2b57d69553d8 ksxpsg=0x7b57f9620 ksxpsg_a=0x7b57f9620ksxpssg=0x7b57f9368 rm=0x7b94080d8
proc state: (pid: 227) [flg: 1 sg: 1]（不存在）
curts 1407305412 wtctr 0
Dumping ksxp contexts
Context[2] 0x2b57d6a0aea0 GES state 1
Context[1] 0x2b57d6a0fcc0 gc ksxp component context state 1 （已经不存在，两个节点都不存在）
Dumping region queue
region count: 1
----------------------------------------
SQL> select sid from v$session where paddr in (select addr from v$process where spid=227);
no rows selected
SQL>