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.csdn.net/xiaofan23z/article/details/136040441
###chenjuchao 20240303###
