使用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
