如何理解systemstate dump

什麼是systemstate
一個systemstate是由在例項中呼叫生成systemstats時由每一個程式的程式狀態組成.而每一個程式狀態是由每一個程式所持有的當前物件所對應的詳細物件狀態資訊組成.

生成systemstate的例子如下

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 267
Statement processed.
SQL> oradebug dump systemstate 267
Statement processed.
SQL> oradebug tracefile_name
/oracle/admin/RLZY/udump/rlzy_ora_54657104.trc

如何瀏覽系統狀態資訊
首先需要做的就是判斷大多數會話正在等待什麼(或者在你知道一個會話被阻塞時它的程式號).所以現在要從PROCESS XX或者一個例如'latch free'標示開始瀏覽.然後就是找到第一個PORCESS XX或者'latch free'標識.如果你正使用PROCESS XX那麼你需要找到這個程式正在等待什麼

PROCESS XX waits for YYYYYYY
然後你需要做的是找到PROCESS XX會話正在等待什麼會話資源.

PROCESS xx waits for YYYYYY
PROCESS YY holds YYYYYY
然後可以開始查詢正在等待的資源和資源的持所者.最終你會找到一個最後等待CPU資源的一個程式或者你將會導航到一個你已經瞭解的程式.對於等待CPU的程式你將需要生成了一個errorstack來判斷為什麼它正被阻塞.

PROCESS XX waits for YYYYYYY
PROCESS YY holds YYYYYYY and waits for ZZZZZZZZ
PROCESS ZZ holds ZZZZZZZ ... etc etc

常見的場景和相關的條目
1:enqueue 佇列

PROCESS 141
... 
waiting for 'enq: TX - row lock contention' blocking sess=0x39b3a5c90 seq=152 wait_time=0 seconds since wait
started=796
name|mode=54580006, usn< 54580006 is split into ASCII 54 + ASCII 58 (TX) + Mode 0006 (X) ...

為了找到對於這個佇列的更詳細資訊可以簡單地向下搜尋'req':

SO: 39ad80d60, type: 5, owner: 393cb85e0, flag: INIT/-/-/0x00
(enqueue) TX-00020009-0001FA04 DID: 0001-0029-00000090
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 flag: 0x6
res: 39aef20c8, req: X, prv: 39aef20e8, own: 39b383aa8, sess: 39b383aa8, proc: 39b7384f0

那麼現在已經有了這個佇列的名字是一個字串(TX-00020009-0001FA04)使用它可以用來搜尋持有者:

(enqueue) TX-00020009-0001FA04 DID: 0001-002E-00000014
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 flag: 0x6
res: 39aef20c8, mode: X, prv: 39aef20d8, own: 39b3a5c90, sess: 39b3a5c90, proc: 39b73ac78

可以看到持有的佇列(mode:X)以一種不相容的模式來執行X request(排他請求)

2:Rowcache locks 行快取鎖

PROCESS 219:
... 
waiting for 'row cache lock' blocking sess=0x0 seq=2174 wait_time=0
cache id=7, mode=0, request=3 *
We do not hold it currently (mode=0), but want it in Shared (mode=3) ... 
--------------------------------------------------------------------------------
SO: 7000000c6de7678, type: 48, owner: 7000000a6c97cf8, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=7000000a660b8b0 object=7000000eedc13a0, request=S*Here we see the request is Shared(S) 
savepoint=2148
row cache parent object: address=7000000eedc13a0 cid=7(dc_users)*dc_users is the cache type indicated by 7 
hash=2a057ebe typ=9 transaction=7000000c42297a0 flags=00000002
own=7000000eedc1480[7000000c6de8518,7000000c6de8518] wat=7000000eedc1490[7000000c6de7568,7000000c6deed98] mode=X *The holder has it in this mode
status=VALID/-/-/-/-/-/-/-/-
request=N release=TRUE flags=0

為了找到持有者可以搜尋物件,持有模式(object,MODE)(比如object=7000000eedc13a0, mode=X)

SO: 7000000c6de84e8, type: 48, owner: 7000000c42297a0, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=7000000a6702710 object=7000000eedc13a0, mode=X*This confirms the Mode we thought the holder had (X)
savepoint=109
row cache parent object: address=7000000eedc13a0 cid=7(dc_users)
hash=2a057ebe typ=9 transaction=7000000c42297a0 flags=00000002
own=7000000eedc1480[7000000c6de8518,7000000c6de8518] wat=7000000eedc1490[7000000c6de7568,7000000c6df1b08] mode=X
status=VALID/-/-/-/-/-/-/-/-
request=N release=TRUE flags=0
instance lock id=QH 00000440 00000000
set=0, complete=FALSE
set=1, complete=FALSE
set=2, complete=FALSE
data=

3:Library Cache Pins (10G - Mutexes)

PROCESS 116:

waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=58849 wait_time=0 seconds since wait started=0
idn=535d1a6c, value=c1600000000, where|sleeps=5003f2428

為了找到更詳細的資訊使用idn=XXXXXX來進行搜尋(比如:idn=535d1a6c)

KGX Atomic Operation Log 7000002e5b9d160
Mutex 7000002b8e92268(3094, 0) idn 535d1a6c oper GET_SHRD *We can see (a) That SID 3094 holds it (3094,0) and (b) we want it in Shared (GET_SHRD)
Cursor Pin uid 2489 efd 0 whr 5 slp 58733
opr=2 pso=70000028c47def0 flg=0
pcs=7000002b8e92268 nxt=0 flg=34 cld=3 hd=70000030d6c6eb0 par=7000002eefe64d0
ct=31 hsh=0 unp=0 unn=0 hvl=b825a4d0 nhv=1 ses=700000309b42600
hep=7000002b8e922e8 flg=80 ld=1 ob=7000002de49f8a0 ptr=70000022cf39db8 fex=70000022cf390c8

為了找到持有者,搜尋idn=XXXXXX oper直到找到一個持有者為止(不是使用GET_XXX)(比如: idn 535d1a6c oper)

KGX Atomic Operation Log 7000002cd934270
Mutex 7000002b8e92268(3094, 0) idn 535d1a6c oper EXCL *We can see SID 3094 holds in Exclusive (EXCL)
Cursor Pin uid 3094 efd 0 whr 7 slp 0
opr=3 pso=7000002a71c4180 flg=0
pcs=7000002b8e92268 nxt=0 flg=34 cld=3 hd=70000030d6c6eb0 par=7000002eefe64d0
ct=31 hsh=0 unp=0 unn=0 hvl=b825a4d0 nhv=1 ses=700000309b42600
hep=7000002b8e922e8 flg=80 ld=1 ob=7000002de49f8a0 ptr=70000022cf39db8 fex=70000022cf390c8

4:Library Cache Pins (Pre 10G - non mutex)

PROCESS 20:

waiting for 'library cache pin' blocking sess=0x0 seq=575 wait_time=0
handle address=c00000006c0f8490, pin address=c0000000689b19a8, 10*mode+namespace=14

為了找到更詳細的資訊使用handle=XXXXXX來搜尋(比如:handle=c00000006c0f8490)就會看到一個'request'行資訊

SO: c0000000689b19a8, type: 34, owner: c00000006cf85e80, flag: INIT/-/-/0x00 
LIBRARY OBJECT PIN: pin=c0000000689b19a8 handle=c00000006c0f8490 request=S lock=c00000006d00e218 *We can see we want it in Shared (S)
user=c00000005eeafeb0 session=c00000005eeafeb0 count=0 mask=0000 savepoint=17 flags=[00]

然後為了找到持有者搜尋'handle=XXXXXX mode'直到你找到一個以不相容模式的所持有它的持有者為止(比如

:handle=c00000006c0f8490 mode)SO: c00000006b1f4780, type: 34, owner: c0000000699758e8, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=c00000006b1f4780 handle=c00000006c0f8490 mode=X lock=c00000006b6c40a0 *We hold it in Exclusive (X)
user=c00000005edf0f48 session=c00000005edf0f48 count=1 mask=0001 savepoint=49 flags=[00]

5:Library Cache Lock

PROCESS 35:

waiting for 'library cache lock' blocking sess=0x0 seq=35844 wait_time=0 seconds since wait started=14615
handle address=70000030de975a8, lock address=70000026947e190, 100*mode+namespace=12d

為了找到更多詳細資訊以handle=address格式來使用handle address來進行搜尋(比如:handle=70000030de975a8)

SO: 70000026947e190, type: 53, owner: 700000308d726f0, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=70000026947e190 handle=70000030de975a8 request=X *We want it in Exclusive (X)
call pin=0 session pin=0 hpc=0000 hlc=0000
htl=70000026947e210[7000002b333ffe8,7000002b333ffe8] htb=7000002b333ffe8 ssga=7000002b333f2a0
user=700000307a7ca68 session=700000307a7ca68 count=0 flags=[0000] savepoint=0x23e411
LIBRARY OBJECT HANDLE: handle=70000030de975a8 mtx=70000030de976d8(0) cdp=0
name=ACSELP.POLIZA *This is the object we are trying to lock

為了找到持有者搜尋'handle=XXXXXXXXXX mode='直到你找到一個持有者(不能是NULL)(比如: handle=70000030de975a8 mode=)

SO: 700000288b03ae0, type: 53, owner: 7000002cc697468, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=700000288b03ae0 handle=70000030de975a8 mode=S *We hold in in Shared (S)
call pin=0 session pin=0 hpc=0000 hlc=0000
htl=700000288b03b60[7000002a179a1a8,7000002b3800878] htb=7000002b3800878 ssga=7000002b37ffb30
user=70000030fafab00 session=70000030fafab00 count=1 flags=[0000] savepoint=0x417
LIBRARY OBJECT HANDLE: handle=70000030de975a8 mtx=70000030de976d8(0) cdp=0
name=ACSELP.POLIZA *This confirms the object

6:Latch free

PROCESS 8: 

waiting for 'latch free' blocking sess=0x0 seq=4577 wait_time=0
address=99ff60018, number=9d, tries=0 *9d is the latch# from v$latchname in HEX

如果檢視頂級程式轉儲資訊你就會看到正在等待的精確的閂鎖甚至是它的持有者:

waiting for 99ff60018 Child library cache level=5 child#=3 
Location from where latch is held: kglic: child
Context saved from call: 26
state=busy
possible holder pid = 127 ospid=23086 *This tell us PROCESS 127 (ospid:23086) holds it
wtr=99ff60018, next waiter 9993858b8

所以程式127持有它如果現在去檢視程式127將會看到:

holding 99ff60018 Child library cache level=5 child#=3 
Location from where latch is held: kglic: child
Context saved from call: 26
state=busy

如果想知道持有者引用的什麼物件可以使用'handle=XXXXXXXXXX'來進行搜尋直到你看到LIBRARY OBJECT HANDLE為止
(比如:handle=c00000006c0f8490)

LIBRARY OBJECT HANDLE: handle=c00000006c0f8490
name=SELECT USER FROM DUAL *This is the name of the handle
hash=cd1ceca0 timestamp=11-23-2013 09:00:00
namespace=CRSR flags=RON/TIM/PN0/SML/[12010000]*It is a CURSOR (CRSR).. but we can tell that by the name!