IDC资讯中心
上一个例子中我们主要借助于x$kgllk基表和event systemstate解决问题，那么如果你不了解x$kgllk基表，或者忘记了如何使用它，那也不要紧张，这里再介绍一种常规的方法。
从system state 的转储信息中，我们已经注意到process 28当前正在等待library cache lock。
handle address表示的就是正持有 process 28 进程所等待的library cache中的地址。
现在我们继续在跟踪文件中查找包含 handle=c000000122e2a6d8 字符串的oracle process，也就是查找blocking session的信息，发现信息如下：
process 26: —————-阻塞其他会话的oracle进程，这里process 26对应了v$process中的pid的值
—————————————-
so: c000000109c831e0, type: 2, owner: 0000000000000000, flag: init/-/-/0x00
(process) oracle pid=26, calls cur/top: c00000010b2774d0/c00000010b2774d0, flag: (0) –
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 17 24 6
last post received-location: ksusig
last process to post me: c000000109c840f8 25 0
last post sent: 751404 0 15
last post sent-location: ksasnd
last process posted by me: c000000109c836e8 1 6
(latch info) wait_event=0 bits=0
process group: default, pseudo proc: c000000109eefda0
o/s info: user: ora9i, term: unknown, ospid: 20552
osd pid info: unix process pid: 20552, image: oracle@cs_dc02 (tns v1-v3)
—————————————-
so: c0000001180b9510, type: 8, owner: c000000109c831e0, flag: init/-/-/0x00
(fob) flags=2 fib ptr=162e1b48 incno=0 pending i/o cnt=0
—————————————-
so: c0000001180b9458, type: 8, owner: c000000109c831e0, flag: init/-/-/0x00
(fob) flags=2 fib ptr=162deb18 incno=0 pending i/o cnt=0
—————————————-
so: c0000001180b8230, type: 8, owner: c000000109c831e0, flag: init/-/-/0x00
(fob) flags=2 fib ptr=162de848 incno=0 pending i/o cnt=0
—————————————-
so: c0000001180b7b00, type: 8, owner: c000000109c831e0, flag: init/-/-/0x00
(fob) flags=2 fib ptr=162de578 incno=0 pending i/o cnt=0
—————————————-
so: c000000108c99e28, type: 4, owner: c000000109c831e0, flag: init/-/-/0x00
c000000108c99e28 对应的就是v$session 中的saddr的值，通过这个信息就可以找到blocking session的sid等信息
(session) trans: c0000001169403c0, creator: c000000109c831e0, flag: (100041) usr/- bsy/-/-/-/-/-
did: 0002-001a-0000007d, short-term did: 0000-0000-00000000
txn branch: c00000011b825e18
oct: 0, prv: 0, sql: 800003fb0005f7b0, psql: c00000011fbe3f98, user: 50/pubuser
o/s info: user: report16, term: , ospid: 20550, machine: cs_dc02
program: sqlplus@cs_dc02 (tns v1-v3)
application name: sql*plus, hash value=3669949024
waiting for sql*net message from dblink blocking sess=0x0 seq=3319 wait_time=0
driver id=28444553, #bytes=1, =0
——————-
这里，
#bytes 表示个server process通过database link发送给另一个server process的字节数（bytes）
driver id 是一个10进制数，我们需要把它转化为16进制数，然后就会发现它对应于我们通过event 10046中的相应的信息：
*** 2005-01-10 11:44:48.200
wait #1: nam=sql*net message from dblink ela= 104397696 p1=675562835 p2=1 p3=0
wait #1: nam=sql*net message to dblink ela= 4 p1=675562835 p2=1 p3=0
sql> select to_char(675562835,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx) from dual;
to_char(675562835,xxxxxxxxxxxxxx
———————————
28444553
sql>
temporary object counter: 0
—————————————-
so: c00000011a4496b0, type: 51, owner: c000000108c99e28, flag: init/-/-/0x00
library object lock: lock=c00000011a4496b0 handle=c00000012029f968 mode=n
call pin=0000000000000000 session pin=c00000011a44ad70
htl=c00000011a449720[c00000011a4baa78,c00000011a4baa78] htb=c00000011a4baa78
user=c000000108c99e28 session=c000000108c99e28 count=1 flags=[00] savepoint=173
library object handle: handle=c00000012029f968
namespace=crsr flags=ron/kghp/pn0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=n pin=0 latch#=3
lwt=c00000012029f998[c00000012029f998,c00000012029f998] ltm=c00000012029f9a8[c00000012029f9a8,c00000012029f9a8]
pwt=c00000012029f9c8[c00000012029f9c8,c00000012029f9c8] ptm=c00000012029fa58[c00000012029fa58,c00000012029fa58]
ref=c00000012029f978[c0000001202a0068, c0000001202a0068] lnd=c00000012029fa70[c00000012029fa70,c00000012029fa70]
library object: object=c00000012029f5c8
type=crsr flags=exs[0001] pflags= [00] status=vald load=0
dependencies: count=1 size=16
authorizations: count=1 size=16 minimum entrysize=16
accesses: count=1 size=16
data blocks:
data# heap pointer status pins change
—– ——– ——– —— —- ——
0 c00000012029f8a8 c00000012029f288 i/p/a 0 none
6 c00000012029f6e8 c00000012029e7c8 i/-/a 0 none
—————————————-
。。。 。。。
—————————————-
so: c00000011a44a150, type: 51, owner: c0000001169403c0, flag: init/-/-/0x00
////////////// x$kgllk.kgllkadr 对应于so(so: c00000011a44a150 //////////////
////////////// x$kgllk.kgllkuse 和 x$kgllk.kgllkses 对应于owner的值(owner: c0000001169403c0)
library object lock: lock=c00000011a44a150 handle=c000000122e2a6d8 mode=x
////////////// x$kgllk.kgllkadr 对应于so 和 lock的值(so: c00000011a44a150,lock=c00000011a44a150) //////////////
////////////// x$kgllk.kgllkhdl 对应于handle的值(handle=c000000122e2a6d8) ////////////////
call pin=0000000000000000 session pin=0000000000000000
////////////// x$kgllk.kgllkpns 对应于session pin的值(session pin=0000000000000000) //////////////
htl=c00000011a44a1c0[c00000011a4bb328,c00000011a4bb328] htb=c00000011a4bb328
user=c000000108c99e28 session=c000000108c99e28 count=1 flags=[00] savepoint=179
user和session的值分别对应着x$kgllk.kgllkuse 和 x$kgllk.kgllkses，也对应于v$session中阻塞其他会话的saddr
////////////// x$kgllk.kgllkspn对应于savepoint的值(savepoint=179) //////////////
library object handle: handle=c000000122e2a6d8
name=pubuser.csnoz629926699966
hash=eddf82b5 timestamp=01-08-2005 13:00:18 previous=null
namespace=tabl/prcd/type flags=kghp/tim/ptm/sml/[02000000]
kkkk-dddd-llll=0000-0709-0001 lock=x pin=x latch#=3
lwt=c000000122e2a708[c00000011a449e40,c00000011a449e40] ltm=c000000122e2a718[c000000122e2a718,c000000122e2a718]
pwt=c000000122e2a738[c000000122e2a738,c000000122e2a738] ptm=c000000122e2a7c8[c000000122e2a7c8,c000000122e2a7c8]
ref=c000000122e2a6e8[c000000122e2a6e8, c000000122e2a6e8] lnd=c000000122e2a7e0[c000000122e2a7e0,c000000122e2a7e0]
lock instance lock: id=lbcafc8485d0949f81
pin instance lock: id=nbcafc8485d0949f81 mode=x release=f flags=[00]
library object: object=c000000122e12f70
type=tabl flags=exs/loc/crt[0015] pflags= [00] status=vald load=0
data blocks:
data# heap pointer status pins change
—– ——– ——– —— —- ——
0 c000000122e2a618 c000000122e13118 i/p/a 0 insert
3 c000000122e13178 0 -/p/- 1 none
8 c000000122e12c30 c000000122febdb8 i/p/a 1 update
9 c000000122e13090 0 -/p/- 1 none
10 c000000122e12ce0 c000000122acbc70 i/p/a 1 update
—————————————-
。。。 。。。
根据上述两个oracle进程号(oracle pid)，我们可以找到他们的会话信息和操作系统进程信息
sql> select spid,pid,addr from v$process where pid in (26,28);
spid pid addr
———— ———- —————-
20552 26 c000000109c831e0 ———– 阻塞其他会话的oracle进程
22580 28 c000000109c83bf0 ———– 被阻塞的oracle进程
sql>
我们来进一步证实一下上述信息：
sql>col username for a20
sql> col osuser for a20
sql> col machine for a20
sql> l
1 select sid,serial#,username,osuser,machine,to_char(logon_time,yyyy/mm/dd hh24:mi:ss) logontime
2* from v$session where paddr in ( select addr from v$process where spid =&spid)
sql> /
enter value for spid: 20552 ———– 阻塞其他会话的oracle进程
old 2: from v$session where paddr in ( select addr from v$process where spid =&spid)
new 2: from v$session where paddr in ( select addr from v$process where spid =20552)
sid serial# username osuser machine logontime
———- ———- ——————– ——————– ——————– ——————-
37 2707 pubuser report16 cs_dc02 2005/01/08 13:00:17
sql> /
enter value for spid: 22580 ———– 被阻塞的oracle进程
old 2: from v$session where paddr in ( select addr from v$process where spid =&spid)
new 2: from v$session where paddr in ( select addr from v$process where spid =22580)
sid serial# username osuser machine logontime
———- ———- ——————– ——————– ——————– ——————-
30 24167 pubuser ora9i cs_dc02 2005/01/10 10:20:31
sql> select sid,saddr,paddr,username,status,osuser from v$session where sid in (37,30);
sid saddr paddr username status osuser
———- —————- —————- ——————– ——– ——————–
30 c000000109f02c68 c000000109c83bf0 pubuser active ora9i
37 c000000108c99e28 c000000109c831e0 pubuser active report16
sql>
现在，问题已经水落石出了，解决方法和方法1中的一样（在操作系统中直接kill掉相应的操作系统进程）。
当然，处于研究的目的，我们可以进一步了解一下上述两个会话（sid 30 和 sid 37）所有已经持有锁的相关信息：
sql> set linesize 150
sql> set pages 10000
sql> select * from v$lock where sid in (37,30);
addr kaddr sid ty id1 id2 lmode request ctime block
—————- —————- ———- — ———- ———- ———- ———- ———- ———-
c0000001169403c0 c000000116940538 37 tx 917507 26579 6 0 180478 2
c00000011676dae0 c00000011676db08 37 tm 18 0 3 0 180478 2
c00000010b30c4e8 c00000010b30c508 37 xr 4 0 2 0 180369 2
c00000010b30c460 c00000010b30c480 37 dx 21 0 1 0 68 0
sql>
不难看出，会话37阻塞了其他会话
现在，我们再进一步看看会话37当前在哪些对象上加了锁：
sql> select object_name,object_id from dba_objects where object_id in (917507,18,4,21) order by object_id;
object_name object_id
—————————— ———-
tab$ 4
obj$ 18
col$ 21
sql> /
object_name object_id
—————————— ———-
tab$ 4
obj$ 18
col$ 21
sql>
接下来，再着重看看sid 为37的会话在library cache中请求和持有对象锁的详细信息：
sql> col kglnaobj for a30
sql> col user_name for a10
sql> l
1 select inst_id,user_name,kglnaobj,kgllksnm,kgllkuse,kgllkses,kgllkmod,kgllkreq
2* from x$kgllk where kgllksnm = 37
sql> /
inst_id user_name kglnaobj kgllksnm kgllkuse kgllkses kgllkmod kgllkreq
———- ———- —————————— ———- —————- —————- ———- ———-
2 pubuser dbms_output 37 c000000108c99e28 c000000108c99e28 1 0
2 pubuser dbms_output 37 c000000108c99e28 c000000108c99e28 1 0
2 pubuser dbms_standard 37 c000000108c99e28 c000000108c99e28 1 0
2 pubuser pubuser 37 c000000108c99e28 c000000108c99e28 1 0
2 pubuser select minor_version from sy 37 c000000108c99e28 c000000108c99e28 1 0
s.cdc_system$
2 pubuser select minor_version from sy 37 c000000108c99e28 c000000108c99e28 1 0
s.cdc_system$
2 pubuser dbms_cdc_publish 37 c000000108c99e28 c000000108c99e28 1 0
2 pubuser dbms_cdc_publish 37 c000000108c99e28 c000000108c99e28 1 0
2 pubuser csnoz629926699966 37 c000000108c99e28 c000000108c99e28 3 0
2 pubuser dbms_application_info 37 c000000108c99e28 c000000108c99e28 1 0
2 pubuser dbms_application_info 37 c000000108c99e28 c000000108c99e28 1 0
2 pubuser database 37 c000000108c99e28 c000000108c99e28 1 0
12 rows selected.
sql>
再看看sid为30的会话在library cache中请求和持有对象锁的详细信息：
sql> select inst_id,user_name,kglnaobj,kgllksnm,kgllkuse,kgllkses,kgllkmod,kgllkreq
2 from x$kgllk where kgllksnm = 30
3 /
inst_id user_name kglnaobj kgllksnm kgllkuse kgllkses kgllkmod kgllkreq
———- ———- —————————— ———- —————- —————- ———- ———-
2 pubuser pubuser 30 c000000109f02c68 c000000109f02c68 1 0
2 pubuser csnoz629926699966 30 c000000109f02c68 c000000109f02c68 0 2
2 pubuser dbms_application_info 30 c000000109f02c68 c000000109f02c68 1 0
2 pubuser dbms_application_info 30 c000000109f02c68 c000000109f02c68 1 0
2 pubuser database 30 c000000109f02c68 c000000109f02c68 1 0
sql>
kglnaobj 列包含了在librarky cache中的对象上执行命令的语句的前80个字符，其实从这里我们也可以大大缩小范围了
kgllkses 对应于v$session 中的 saddr列的值
kgllksnm 对应于v$session 中的sid(session id)
kgllkhdl 的值与方法1中跟踪文件中的handle address的值对应
kgllkpns 的值对应于方法1中跟踪文件中的ssession pin的值
sql> set linesize 2000
sql> l
1 select inst_id,user_name,kglnaobj,kgllksnm,kgllkuse,kgllkses,kgllkmod,kgllkreq,kgllkpns,kgllkhdl
2* from x$kgllk where kgllksnm in (30,37) order by kgllksnm,kglnaobj
sql> /
inst_id user_name kglnaobj kgllksnm kgllkuse kgllkses kgllkmod kgllkreq kgllkpns kgllkhdl
———- —————————— ———————————————————— ———- —————- —————- ———- ———- —————- —————-
2 pubuser database 30 c000000109f02c68 c000000109f02c68 1 0 00 c000000119f8ec58
2 pubuser dbms_application_info 30 c000000109f02c68 c000000109f02c68 1 0 00 c00000011ccdda48
2 pubuser dbms_application_info 30 c000000109f02c68 c000000109f02c68 1 0 00 c00000011ccd81b8
2 pubuser pubuser 30 c000000109f02c68 c000000109f02c68 1 0 00 c00000011cbfdaa8
2 pubuser csnoz629926699966 30 c000000109f02c68 c000000109f02c68 0 2 00 c000000122e2a6d8
2 pubuser database 37 c000000108c99e28 c000000108c99e28 1 0 00 c000000119f8ec58
2 pubuser dbms_application_info 37 c000000108c99e28 c000000108c99e28 1 0 00 c00000011ccdda48
2 pubuser dbms_application_info 37 c000000108c99e28 c000000108c99e28 1 0 00 c00000011ccd81b8
2 pubuser dbms_cdc_publish 37 c000000108c99e28 c000000108c99e28 1 0 00 c00000011fea4918
2 pubuser dbms_cdc_publish 37 c000000108c99e28 c000000108c99e28 1 0 00 c0000001202a4988
2 pubuser dbms_output 37 c000000108c99e28 c000000108c99e28 1 0 00 c00000011ccb48b0
2 pubuser dbms_output 37 c000000108c99e28 c000000108c99e28 1 0 00 c00000011fff5098
2 pubuser dbms_standard 37 c000000108c99e28 c000000108c99e28 1 0 00 c00000011ccf0ed8
2 pubuser pubuser 37 c000000108c99e28 c000000108c99e28 1 0 00 c00000011cbfdaa8
2 pubuser select minor_version from sys.cdc_system$ 37 c000000108c99e28 c000000108c99e28 1 0 c00000011a44ad70 c00000012029f968
2 pubuser select minor_version from sys.cdc_system$ 37 c000000108c99e28 c000000108c99e28 1 0 00 c0000001202a0228
2 pubuser csnoz629926699966 37 c000000108c99e28 c000000108c99e28 3 0 00 c000000122e2a6d8
17 rows selected.
sql>
