9i 中v$session_wait 是Oracle wait interface的一个主要用户接口，而该动态视图的内容来源于x$ksusecst内部视图：
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,单位仍为秒
粗略写了一个可以代替v$session_wait视图的查询语句，过滤了可能出现的空闲等待事件，同时细化wait_time列到us级别：
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');
Leave a Reply