DFS lock handle & inactive transaction branch

最新推荐文章于 2021-01-28 06:09:45 发布

1ihj2015

最新推荐文章于 2021-01-28 06:09:45 发布

阅读量3.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle Troubleshooting Study & Testing

本文链接：https://blog.youkuaiyun.com/itdba/article/details/38400083

Study & Testing 同时被 2 个专栏收录

66 篇文章

订阅专栏

Oracle Troubleshooting

37 篇文章

订阅专栏

本文分析了Oracle数据库中DFSlockhandle等待事件的问题，通过查询v$active_session_history视图定位到具体会话，并利用oradebug工具进一步诊断，发现会话长时间处于等待状态。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

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 81601 xid 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 81605 xid 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>