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