查询前等待事件语句，进行性能分析和优化

用这个语句查询用户当前正在等待事件，把语句抽取出来。
SELECT s.username,
       s.osuser,
       s.sid||','||
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       S.EVENT,
       a.SQL_FULLTEXT,
       TO_CHAR(s.logon_Time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time
  FROM v$session s, v$process p,v$sqlarea a
 WHERE s.paddr = p.addr and s.sql_hash_value = a.hash_value (+)
AND    s.sql_address    = a.address (+)
   and s.event not in ('smon timer',
                       'pmon timer',
                       'rdbms ipc message',
                       'Null event',
                       'parallel query dequeue',
                       'pipe get',
                       'client message',
                       'SQL*Net message to client',
                       'SQL*Net message from client',
                       'SQL*Net more data from client',
                       'dispatcher timer',
                       'virtual circuit status',
                       'lock manager wait for remote message',
                       'PX Idle Wait',
                       'PX Deq: Execution Msg',
                       'PX Deq: Table Q Normal',
                       'wakeup time manager',
                       'slave wait',
                       'i/o slave wait',
                       'jobq slave wait',
                       'null event',
                       'gcs remote message',
                       'gcs for action',
                       'ges remote message',
                       'queue messages',
                       'wait for unread message on broadcast channel',
                       'PX Deq Credit: send blkd',
                       'PX Deq: Execute Reply',
                       'PX Deq: Signal ACK',
                       'PX Deque wait',
                       'PX Deq Credit: need buffer',
                       'STREAMS apply coord waiting for slave message',
                       'STREAMS apply slave waiting for coord message',
                       'Queue Monitor Wait',
                       'Queue Monitor Slave Wait',
                       'wakeup event for builder',
                       'wakeup event for preparer',
                       'wakeup event for reader',
                       'wait for activate message',
                       'PX Deq: Par Recov Execute',
                       'PX Deq: Table Q Sample',
                       'STREAMS apply slave idle wait',
                       'STREAMS capture process filter callback wait for ruleset',
                       'STREAMS fetch slave waiting for txns',
                       'STREAMS waiting for subscribers to catch up',
                       'Queue Monitor Shutdown Wait',
                       'AQ Proxy Cleanup Wait',
                       'knlqdeq',
                       'class slave wait',
                       'master wait',
                       'DIAG idle wait',
                       'ASM background timer',
                       'KSV master wait',
                       'EMON idle wait',
                       'Streams AQ: RAC qmn coordinator idle wait',
                       'Streams AQ: qmn coordinator idle wait',
                       'Streams AQ: qmn slave idle wait',
                       'Streams AQ: waiting for time management or cleanup tasks',
                       'Streams AQ: waiting for messages in the queue',
                       'Streams fetch slave: waiting for txns',
                       'Streams AQ: deallocate messages from Streams Pool',
                       'Streams AQ: delete acknowledged messages',
                       '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',
                       'VKTM Logical Idle Wait',
                       'EMON slave idle wait',
                       'EMON slave idle wait',
                       'Space Manager: slave idle wait',
                       'Streams AQ: emn coordinator idle wait'
                      
                       )
   AND s.status = 'ACTIVE'
 ORDER BY s.username, s.osuser;  
上面抽取语句执行下面语句，把抽取语句填入，分析语句对语句进行优化。
例如：创建索引，重建索引，修改语句等..........
 SELECT s.username,s.osuser,s.sid||','||s.serial#,p.spid,s.lockwait,s.status,s.module,s.machine,s.program,S.EVENT,a.SQL_FULLTEXT,TO_CHAR(s.logon_Time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time FROM v$session s, v$process p,v$sqlarea a WHERE s.paddr = p.addr and s.sql_hash_value = a.hash_value (+) AND    s.sql_address = a.address (+) and s.event not in ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client', 'SQL*Net message from client','SQL*Net more data from client','dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait','PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait','jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages','wait for unread message on broadcast channel','PX Deq Credit: send blkd','PX Deq: Execute Reply','PX Deq: Signal ACK','PX Deque wait','PX Deq Credit: need buffer','STREAMS apply coord waiting for slave message','STREAMS apply slave waiting for coord message', 'Queue Monitor Wait','Queue Monitor Slave Wait','wakeup event for builder','wakeup event for preparer','wakeup event for reader','wait for activate message','PX Deq: Par Recov Execute','PX Deq: Table Q Sample','STREAMS apply slave idle wait','STREAMS capture process filter callback wait for ruleset','STREAMS fetch slave waiting for txns','STREAMS waiting for subscribers to catch up','Queue Monitor Shutdown Wait','AQ Proxy Cleanup Wait','knlqdeq','class slave wait','master wait','DIAG idle wait', 'ASM background timer','KSV master wait','EMON idle wait', 'Streams AQ: RAC qmn coordinator idle wait','Streams AQ: qmn coordinator idle wait','Streams AQ: qmn slave idle wait','Streams AQ: waiting for time management or cleanup tasks','Streams AQ: waiting for messages in the queue','Streams fetch slave: waiting for txns','Streams AQ: delete acknowledged messages','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','VKTM Logical Idle Wait','EMON slave idle wait','EMON slave idle wait','Space Manager: slave idle wait','Streams AQ: emn coordinator idle wait')AND s.status = 'ACTIVE' ORDER BY s.username, s.osuser;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29065182/viewspace-1147792/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29065182/viewspace-1147792/