Oracle数据库HANG问题如何解决-01 Systemstate Dumps介绍

IT小Chen 2024-03-04

260

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

参考链接：

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

###chenjuchao 20240303###

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle数据库HANG问题如何解决-01 Systemstate Dumps介绍

评论