转:Oracle系统非空闲等待事件

select sid,
       p1,
       p1raw,
       p2,
       p2raw,
       p3,
       p3raw,
       wait_time,
       seconds_in_wait,
       state,
       event,
       sysdate
  from v$session_wait
 where event not in
       ('AQ Proxy Cleanup Wait', 'ASM background timer', 'DIAG idle wait',
        'EMON idle wait', 'KSV master wait', 'LNS ASYNC archive log',
        'LNS ASYNC dest activation', 'LNS ASYNC end of log',
        'LogMiner: client waiting for transaction',
        'LogMiner: slave waiting for activate message',
        'LogMiner: wakeup event for builder',
        'LogMiner: wakeup event for preparer',
        'LogMiner: wakeup event for reader', 'Null event',
        'PX Deq Credit: need buffer', 'PX Deq Credit: send blkd',
        'PX Deq: Execute Reply', 'PX Deq: Execution Msg',
        'PX Deq: Par Recov Execute', 'PX Deq: Signal ACK',
        'PX Deq: Table Q Normal', 'PX Deq: Table Q Sample', 'PX Deque wait',
        'PX Idle Wait', 'Queue Monitor Shutdown Wait',
        'Queue Monitor Slave Wait', 'Queue Monitor Wait',
        'SQL*Net message from client', 'SQL*Net message to client',
        'SQL*Net more data from client',
        'STREAMS apply coord waiting for slave message',
        'STREAMS apply slave idle wait',
        'STREAMS apply slave waiting for coord message',
        'STREAMS capture process filter callback wait for ruleset',
        'STREAMS fetch slave waiting for txns',
        'STREAMS waiting for subscribers to catch up',
        'Streams AQ: RAC qmn coordinator idle wait',
        'Streams AQ: deallocate messages from Streams Pool',
        'Streams AQ: delete acknowledged messages',
        'Streams AQ: qmn coordinator idle wait',
        'Streams AQ: qmn slave idle wait',
        'Streams AQ: waiting for messages in the queue',
        'Streams AQ: waiting for time management or cleanup tasks',
        'Streams fetch slave: waiting for txns', 'class slave wait',
        'client message', 'dispatcher timer', 'gcs for action',
        'gcs remote message', 'ges remote message', 'i/o slave wait',
        'jobq slave wait', 'knlqdeq', 'lock manager wait for remote message',
        'master wait', 'null event', 'parallel query dequeue', 'pipe get',
        'pmon timer', 'queue messages', 'rdbms ipc message', 'slave wait',
        'smon timer', 'virtual circuit status', 'wait for activate message',
        'wait for unread message on broadcast channel',
        'wakeup event for builder', 'wakeup event for preparer',
        'wakeup event for reader', 'wakeup time manager');

找到某连续等待的记录的sid,运行以下代码求其对应sql：

select sql_text
  from v$sqltext_with_newlines st, v$session se
 where st.address = se.sql_address
   and st.hash_value = se.sql_hash_value
   and se.sid = :SID
 order by piece; 
 

转自:http://www.javaeye.com/topic/498902