20240307-Oracle数据库HANG问题如何解决-01 Systemstate Dumps介绍-优快云博客

在这里插入图片描述
Systemstate Dumps:简称SSD

适用场景：

当Oracle数据库出现严重性能问题时，甚至sysdba无法登陆，可以通过Systemstate Dumps收集诊断日志。

Systemstate levels:

level 2:dump(包括lock element)
level 10:dump
level 11:dump+global cache of rac --会产生大量的trc，并耗时较久，不建议使用
level 256:short stack（函数堆栈）
level 258:level256+level2 —可以快速dump 但是会丢失部分锁信息
level 266:level56+level10 —较为常用 速度较快根据系统负载一般20-60s，收集的信息也足够
level 267:level256+level11 —和level11类似耗时久 trc大

sqlplus -prelim是什么：

使用SQL*Plus，使用以下命令连接为SYSDBA：

sqlplus '/ as sysdba'

如果进行此连接时出现问题，无法正常登陆，那么在10gR2及以上版本中，可以使用sqlplus “preliminary connection”：

sqlplus -prelim '/ as sysdba'

有两种方式进行prelim连接

方式一：

[oracle@cjc-db-01 ~]$ sqlplus -prelim / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 3 12:57:28 2024
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL>

方式二：

[oracle@cjc-db-01 ~]$ sqlplus /nolog
SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established

模拟锁阻塞：

conn cjc/***
create table t1(id int,age int);
create table t2(id int,age int);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
insert into t2 values(10,10);
insert into t2 values(20,20);
insert into t2 values(30,30);
commit;

---SID=39
select distinct sid from v$mystat;
update t1 set age=100 where id=1;
---SID=41
select distinct sid from v$mystat;
update t2 set age=1000 where id=10;
---SID=39
update t2 set age=100 where id=10;
---SID=41
update t1 set age=1000 where id=1;
---SID=33
update t1 set age=10000 where id=1;

SQL> set line 300
SQL> select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE   MODE_HELD    MODE_REQUESTED       LOCK_ID1   LOCK_ID2
--------------- --------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
     41      39 Transaction   Exclusive    Exclusive 589824        945
     33      39 Transaction   Exclusive    Exclusive 589824        945
     41      41 Transaction   None     Exclusive 589824        945
     33      41 Transaction   None     Exclusive 589824        945

收集SSD

[oracle@cjc-db-01 ~]$ sqlplus  -prelim "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 3 13:07:48 2024
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug  dump systemstate 266
Statement processed.
SQL> oradebug  dump systemstate 266
Statement processed.
SQL> oradebug  tracefile_name
/oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_3673.trc

搜索关键字 waiting for ‘enq: TX - row lock contention’

可以看到sid=39阻塞了41和33，阻塞源头是39;

PROCESS 22:
  ----------------------------------------
  SO: 0x9dcbb6c8, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x9dcbb6c8, name=process, file=ksu.h LINE:12721, pg=0
  (process) Oracle pid:22, ser:4, calls cur/top: (nil)/0x9d8cba98
            flags : (0x0) -
            flags2: (0x0),  flags3: (0x10)
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
......
    There are 2 sessions blocked by this session.
    Dumping one waiter:
      inst: 1, sid: 41, ser: 37
      wait event: 'enq: TX - row lock contention'
        p1: 'name|mode'=0x54580006
        p2: 'usn<<16 | slot'=0x90000
        p3: 'sequence'=0x3b1
      row_wait_obj#: 88662, block#: 157, row#: 0, file# 6
      min_blocked_time: 305 secs, waiter_cache_ver: 289
    Wait State:
      fixed_waits=0 flags=0x22 boundary=(nil)/-1
......
    Current Wait Stack:
     0: waiting for 'enq: TX - row lock contention'
        name|mode=0x54580006, usn<<16 | slot=0x90000, sequence=0x3b1
        wait_id=28 seq_num=29 snap_id=1
        wait times: snap=5 min 8 sec, exc=5 min 8 sec, total=5 min 8 sec
        wait times: max=infinite, heur=5 min 8 sec
        wait counts: calls=104 os=104
        in_wait=1 iflags=0x15a0
    There is at least one session blocking this session.
      Dumping 1 direct blocker(s):
        inst: 1, sid: 39, ser: 9
      Dumping final blocker:
        inst: 1, sid: 39, ser: 9
    There are 0 sessions blocked by this session.
    Dumping one waiter:
      inst: 1, sid: 33, ser: 27
      wait event: 'enq: TX - row lock contention'
        p1: 'name|mode'=0x54580006
        p2: 'usn<<16 | slot'=0x90000
        p3: 'sequence'=0x3b1
      row_wait_obj#: 88662, block#: 157, row#: 0, file# 6
      min_blocked_time: 284 secs, waiter_cache_ver: 289
    Wait State:
      fixed_waits=0 flags=0x22 boundary=(nil)/-1
   
    There are 2 sessions blocked by this session.
    Dumping one waiter:
      inst: 1, sid: 41, ser: 37
      wait event: 'enq: TX - row lock contention'
        p1: 'name|mode'=0x54580006
        p2: 'usn<<16 | slot'=0x90000
        p3: 'sequence'=0x3b1
      row_wait_obj#: 88662, block#: 157, row#: 0, file# 6
      min_blocked_time: 268 secs, waiter_cache_ver: 277
    Wait State:
      fixed_waits=0 flags=0x22 boundary=(nil)/-1
   
......
    There are 0 sessions blocked by this session.
    Dumping one waiter:
      inst: 1, sid: 33, ser: 27
      wait event: 'enq: TX - row lock contention'
        p1: 'name|mode'=0x54580006
        p2: 'usn<<16 | slot'=0x90000
        p3: 'sequence'=0x3b1
      row_wait_obj#: 88662, block#: 157, row#: 0, file# 6
      min_blocked_time: 247 secs, waiter_cache_ver: 277
    Wait State:
      fixed_waits=0 flags=0x22 boundary=(nil)/-1

参考链接：

How To Connect Using A Sqlplus Preliminary Connection (Doc ID 986640.1)
《Oracle systemstate、gdb、dbx介绍》
https://blog.youkuaiyun.com/xiaofan23z/article/details/136040441

###chenjuchao 20240303###
欢迎关注我的公众号《IT小Chen》