_num=22 snap_id=1
wait times: snap=9 min 31 sec, exc=9 min 31 sec, total=9 min 31 sec
wait times: max=15 min 0 sec, heur=9 min 31 sec
wait counts: calls=192 os=192
in_wait=1 iflags=0x15a2
There is at least one session blocking this session.
Dumping 2 direct blocker(s):
inst: 1, sid: 42, ser: 49
inst: 1, sid: 22, ser: 27
Dumping final blocker:
inst: 1, sid: 42, ser: 49
There are 0 sessions blocked by this session.
Dumping one waiter:
inst: 1, sid: 42, ser: 49
wait event: 'library cache pin'
p1: 'handle address'=0x78ad1830
p2: 'pin address'=0x7e9434c8
p3: '100*mode+namespace'=0x1704200010003
row_wait_obj#: 5541, block#: 11545, row#: 0, file# 1
min_blocked_time: 569 secs, waiter_cache_ver: 757
Wait State:
fixed_waits=0 flags=0x22 boundary=(nil)/-1
方法二：
select a.event,
a.sid,
a.SERIAL#,
a.username,
a.machine,
a.wait_time wt,
a.seconds_in_wait sw,
a.state,
p.kglpncnt,
p.kglpnmod,
p.kglpnreq,
b.kglnaown,
b.kglnaobj,
b.kglfnobj,
b.kglhdobj
from v$session a, x$kglpn p, x$kglob b
where p.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq <>0)
and p.kglpnhdl=b.kglhdadr
and a.P1RAW=rawtohex(p.kglpnhdl)
order by seconds_in_wait desc ；
刚刚开始其实我关联的是x$kglob,x$kglpn和v$session视图，但是发现这三个视图关联起来执行速度非常慢。
原因如下：
SQL> select * from v$session;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 644658511
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1378 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 1378 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 1169 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 1008 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 161 | 0 (0)| 00:00:01 |
|* 5 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 209 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
4 - filter("S"."INDX"="W"."KSLWTSID")
5 - filter("W"."KSLWTEVT"="E"."INDX")
那么在对v$session查询的时候实际上是在查询x$ksuse,x$kslwt,x$ksled这三个表。倒不如直接把v$session分开。
SQL> select distinct ses.ksusenum sid,
2 ses.ksuseser serial#,
3 ses.ksuudlna username,
4 ses.ksuseunm machine,
5 ob.kglnaown obj_owner,
6 ob.kglnaobj obj_name,
7 pn.kglpncnt pin_cnt,
8 pn.kglpnmod pin_mode,
9 pn.kglpnreq pin_req,
10 w.state,
11 w.event