9i 中v$session_wait 是Oracle wait interface的一个主要用户接口，而该动态视图的内容来源于x$ksusecst内部视图：
粗略写了一个可以代替v$session_wait视图的查询语句，过滤了可能出现的空闲等待事件，同时细化wait_time列到us级别：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
SQL>
select
view_definition
from
v$fixed_view_definition
where
view_name=
'GV$SESSION_WAIT'
;
VIEW_DEFINITION
--------------------------------------------------------------------------------
select
s.inst_id,s.indx,s.ksussseq,e.kslednam, e.ksledp1,s.ksussp1,s.ksussp1r,e.
ksledp2, s.ksussp2,s.ksussp2r,e.ksledp3,s.ksussp3,s.ksussp3r, decode(s.ksusstim,
0,0,-1,-1,-2,-2, decode(round(s.ksusstim/10000),0,-1,round(s.ksusstim/10000)))
, s.ksusewtm, decode(s.ksusstim, 0,
'WAITING'
, -2,
'WAITED UNKNOWN TIME'
, -1,
'
WAITED SHORT TIME'
,
'WAITED KNOWN TIME'
)
from
x$ksusecst s, x$ksled e
where
bit
and
(s.ksspaflg,1)!=0
and
bitand(s.ksuseflg,1)!=0
and
s.ksussseq!=0
and
s.ksussop
c=e.indx
SQL>
desc
x$ksusecst
Name
Null
? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
//即 v$session中 saddr 会话的起始地址
INDX NUMBER
//即 instance_id
INST_ID NUMBER
//即 sid
KSSPAFLG NUMBER
KSUSEFLG NUMBER
//该session是否仍活着, 1 为 alive
KSUSENUM NUMBER
//另一个固有编号
KSUSSSEQ NUMBER
// 相当于v$session 视图的SERIAL#列
KSUSSOPC NUMBER
// 对应x$ksled视图indx列，等待事件列表的一个序列号
KSUSSP1 NUMBER
// 即v$session_wait表的p1列
KSUSSP1R RAW(4)
// 即v$session_wait表的p1raw
KSUSSP2 NUMBER
// 即v$session_wait表的p2
KSUSSP2R RAW(4)
// 即v$session_wait表的p2raw
KSUSSP3 NUMBER
// 即v$session_wait表的p3
KSUSSP3R RAW(4)
// 即v$session_wait表的p3raw
KSUSSTIM NUMBER
// 即v$session_wait表的wait_time,但单位为微秒
KSUSEWTM NUMBER
// 即v$session_wait表的seconds_in_wait,单位仍为秒
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
|
select
s.inst_id,
s.indx sid,
s.ksussseq seq#,
e.kslednam event,
e.ksledp1 p1text,
s.ksussp1 p1,
s.ksussp1r p1raw,
e.ksledp2 p2text,
s.ksussp2 p2,
s.ksussp2r p2raw,
e.ksledp3 p3text,
s.ksussp3 p3,
s.ksussp3r p3raw,
s.ksusstim wait_time,
s.ksusewtm seconds_in_wait,
decode(s.ksusstim,
0,
'WAITING'
,
-2,
'WAITED UNKNOWN TIME'
,
-1,
'WAITED SHORT TIME'
,
'WAITED KNOWN TIME'
) state
from
x$ksusecst s, x$ksled e
where
bitand(s.ksspaflg, 1) != 0
and
bitand(s.ksuseflg, 1) != 0
and
s.ksussseq != 0
and
s.ksussopc = e.indx
and
e.kslednam
not
in
(
'pmon timer'
,
'VKTM Logical Idle Wait'
,
'VKTM Init Wait for GSGA'
,
'IORM Scheduler Slave Idle Wait'
,
'rdbms ipc message'
,
'i/o slave wait'
,
'VKRM Idle'
,
'wait for unread message on broadcast channel'
,
'wait for unread message on multiple broadcast channels'
,
'class slave wait'
,
'KSV master wait'
,
'PING'
,
'watchdog main loop'
,
'DIAG idle wait'
,
'ges remote message'
,
'gcs remote message'
,
'heartbeat monitor sleep'
,
'SGA: MMAN sleep for component shrink'
,
'MRP redo arrival'
,
'LNS ASYNC archive log'
,
'LNS ASYNC dest activation'
,
'LNS ASYNC end of log'
,
'simulated log write delay'
,
'LGWR real time apply sync'
,
'parallel recovery slave idle wait'
,
'LogMiner builder: idle'
,
'LogMiner builder: branch'
,
'LogMiner preparer: idle'
,
'LogMiner reader: log (idle)'
,
'LogMiner reader: redo (idle)'
,
'LogMiner client: transaction'
,
'LogMiner: other'
,
'LogMiner: activate'
,
'LogMiner: reset'
,
'LogMiner: find session'
,
'LogMiner: internal'
,
'Logical Standby Apply Delay'
,
'parallel recovery coordinator waits for slave cleanup'
,
'parallel recovery control message reply'
,
'parallel recovery slave next change'
,
'PX Deq: Txn Recovery Start'
,
'PX Deq: Txn Recovery Reply'
,
'fbar timer'
,
'smon timer'
,
'PX Deq: Metadata Update'
,
'Space Manager: slave idle wait'
,
'PX Deq: Index Merge Reply'
,
'PX Deq: Index Merge Execute'
,
'PX Deq: Index Merge Close'
,
'PX Deq: kdcph_mai'
,
'PX Deq: kdcphc_ack'
,
'shared server idle wait'
,
'dispatcher timer'
,
'cmon timer'
,
'pool server timer'
,
'JOX Jit Process Sleep'
,
'jobq slave wait'
,
'pipe get'
,
'PX Deque wait'
,
'PX Idle Wait'
,
'PX Deq: Join ACK'
,
'PX Deq Credit: need buffer'
,
'PX Deq Credit: send blkd'
,
'PX Deq: Msg Fragment'
,
'PX Deq: Parse Reply'
,
'PX Deq: Execute Reply'
,
'PX Deq: Execution Msg'
,
'PX Deq: Table Q Normal'
,
'PX Deq: Table Q Sample'
,
'Streams fetch slave: waiting for txns'
,
'Streams: waiting for messages'
,
'Streams capture: waiting for archive log'
,
'single-task message'
,
'SQL*Net message from client'
,
'SQL*Net vector message from client'
,
'SQL*Net vector message from dblink'
,
'PL/SQL lock timer'
,
'Streams AQ: emn coordinator idle wait'
,
'EMON slave idle wait'
,
'Streams AQ: waiting for messages in the queue'
,
'Streams AQ: waiting for time management or cleanup tasks'
,
'Streams AQ: delete acknowledged messages'
,
'Streams AQ: deallocate messages from Streams Pool'
,
'Streams AQ: qmn coordinator idle wait'
,
'Streams AQ: qmn slave idle wait'
,
'Streams AQ: RAC qmn coordinator idle wait'
,
'HS message to agent'
,
'ASM background timer'
,
'auto-sqltune: wait graph update'
,
'WCR: replay client notify'
,
'WCR: replay clock'
,
'WCR: replay paused'
,
'JS external job'
,
'cell worker idle'
,
'SQL*Net message to client'
);
|
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277486
