查詢前等待事件語句，進行效能分析和優化
用這個語句查詢使用者當前正在等待事件，把語句抽取出來。
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;
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 效能優化查詢語句優化
- 【效能優化】查詢繫結變數的sql語句優化變數SQL
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- 資料庫查詢優化：使用explain分析sql語句執行效率資料庫優化AISQL
- 優化SQL查詢：如何寫出高效能SQL語句優化SQL
- MySQL-效能優化-索引和查詢優化MySql優化索引
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- Sql語句本身的優化-定位慢查詢SQL優化
- 一個connect by查詢語句的優化優化
- sql語句效能優化SQL優化
- 查詢當前正在執行的SQL語句並KILLSQL
- PostgreSQL 原始碼解讀（35）- 查詢語句#20（查詢優化-簡化Having和Grou...SQL原始碼優化
- EntityFramework優化：查詢效能Framework優化
- MySql常用30種SQL查詢語句優化方法MySql優化
- mysql查詢語句優化工具MySql優化
- 改進資料庫效能-SQL查詢優化資料庫SQL優化
- PostgreSQL 原始碼解讀（29）- 查詢語句#14（查詢優化-上拉子查詢）SQL原始碼優化
- PostgreSQL 原始碼解讀（25）- 查詢語句#10（查詢優化概覽）SQL原始碼優化
- PostgreSQL 原始碼解讀（17）- 查詢語句#2（查詢優化基礎）SQL原始碼優化
- 從語句繁簡和效能優化想到的......優化
- 資料庫設計與查詢語句的優化資料庫優化
- 效能優化之分頁查詢優化
- 全文查詢的效能優化優化
- ORACLE結構化查詢語句Oracle
- sql語句執行順序與效能優化（1）SQL優化
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- oracle 使用explain plan分析查詢語句OracleAI
- PostgreSQL 原始碼解讀（36）- 查詢語句#21（查詢優化-消除外連線）SQL原始碼優化
- PostgreSQL 原始碼解讀（37）- 查詢語句#22（查詢優化-grouping_plan...SQL原始碼優化
- 資料庫效能優化之SQL語句優化資料庫優化SQL
- 使用SQL調整顧問進行語句優化SQL優化
- mysql查詢太慢，我們如何進行效能優化？MySql優化
- SQL Server簡潔查詢正在執行SQL（等待事件）SQLServer事件
- 查詢Oracle正在執行和執行過的SQL語句OracleSQL
- linux下開啟mysql慢查詢，分析查詢語句LinuxMySql
- MySQL: 使用explain 優化查詢效能MySqlAI優化
- mysql查詢效能優化總結MySql優化
- SQLServer效能優化之查詢提示SQLServer優化