熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

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

2013-11-13 16:19:11  來源: Oracle 

  使用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
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.