如何捕獲問題SQL解決過度CPU消耗問題

　　使用vmstattop等輔助解決Oracle數據庫性能問題
　　
　　Last Updated: Sunday : Eygle
　　
　　問題描述:
　　
　　開發人員報告系統運行緩慢影響用戶訪問
　　
　　登陸數據庫主機
　　
　　使用vmstat檢查發現CPU資源已經耗盡大量任務位於運行隊列:
　　
　　bash$ vmstat procs memory page disk faults cpu r b w swap free re mf pi po fr de sr s s s sd in sy cs us sy id
　　
　　使用Top命令
　　
　　觀察進程CPU耗用發現沒有明顯過高CPU使用的進程
　　$ top
　　
　　last pid: ; load averages: :: processes: sleeping running zombie on cpuCPU states: % idle % user % kernel % iowait % swapMemory: M real M free M swap in use M swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND oraclei M M run : % oracle oraclei M M sleep : % oracle oraclei M M run : % oracle oraclei M M run : % oracle oraclei M M run : % oracle oraclei M M run : % oracle oraclei M M cpu/ : % oracle oraclei M M run : % oracle oraclei M M run : % oracle oraclei M M run : % oracle oraclei M M run : % oracle oraclei M M run : % oracle oraclei M M run : % oracle oraclei M M run : % oracle oraclei M M run : % oracle
　　
　　檢查進程數量
　　
　　bash$ ps ef|grep ora|wc l bash$ ps ef|grep ora|wc l bash$ ps ef|grep ora|wc l bash$ ps ef|grep ora|wc l bash$ ps ef|grep ora|wc l bash$ ps ef|grep ora|wc l
　　
　　發現系統存在大量Oracle進程大約在左右而正常情況下Oracle連接數應該在左右
　　
　　檢查數據庫
　　
　　查詢v$session_wait獲取各進程等待事件
　　
　　SQL> select sideventpptext from v$session_wait; SID EVENT P PTEXT
　　
　　 latch free E+ address pmon timer duration rdbms ipc message timeout rdbms ipc message timeout rdbms ipc message timeout rdbms ipc message timeout rdbms ipc message timeout rdbms ipc message timeout rdbms ipc message timeout rdbms ipc message timeout rdbms ipc message timeout SID EVENT P PTEXT
　　
　　 rdbms ipc message timeout buffer busy waits file# buffer busy waits file# buffer busy waits file# buffer busy waits file# buffer busy waits file# buffer busy waits file# buffer busy waits file# buffer busy waits file# buffer busy waits file# buffer busy waits file# SID EVENT P PTEXT
　　
　　 buffer busy waits file# buffer busy waits file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# SID EVENT P PTEXT
　　
　　 db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# SID EVENT P PTEXT
　　
　　 db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# SID EVENT P PTEXT
　　
　　 db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# SID EVENT P PTEXT
　　
　　 db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# SID EVENT P PTEXT
　　
　　 db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# db file sequential read file# SID EVENT P PTEXT
　　
From:http://tw.wingwit.com/Article/program/Oracle/201311/18139.html