x$ksusecst 内部视图详解

9i 中v$session_wait 是Oracle wait interface的一个主要用户接口，而该动态视图的内容来源于x$ksusecst内部视图：

?

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,单位仍为秒

粗略写了一个可以代替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 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');