Oracle 11g 数据库服务器CPU、IO负载高的故障排除流程

向右看齐 2024-04-18 00:07 47阅读 0赞

使用top查看负载高的进程  
 ~ \[OSMSRDB\]$ top  
top - 11:02:43 up 12 days, 17:15,  4 users,  load average: 1.50, 0.93, 0.36  
Tasks: 363 total,   1 running, 362 sleeping,   0 stopped,   0 zombie  
Cpu(s):  1.2%us,  0.3%sy,  0.0%ni, 68.4%id,  30.1%wa ,  0.0%hi,  0.0%si,  0.0%st  
Mem:  10129680k total,  7539812k used,  2589868k free,   478612k buffers  
Swap:  4194300k total,        0k used,  4194300k free,  5973136k cached  
  
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND  
22584 ora112    -2   0  837m  16m  14m S  100.0  3.7  74:09.62 oracle  
 3274 ora112    -2   0  837m  16m  14m S  1.0  0.2 211:11.85 oracle  
 6147 ora112    -2   0  837m  16m  14m S  1.0  0.2  19:25.63 oracle  
 7787 ora112    -2   0  837m  16m  14m S  1.0  0.2  62:43.66 oracle  
10696 ora112    -2   0 2251m  16m  14m S  1.0  0.2 190:34.75 oracle  
17198 ora112    -2   0  837m  16m  14m S  1.0  0.2  60:53.89 oracle  
 1874 root      20   0 13584  740  576 S  0.3  0.0   1:11.80 lldpad  
 3323 ora112    20   0  837m  18m  16m S  0.3  0.2  21:49.55 oracle  
  
使用 PS 查看进程的信息  
 ~ \[OSMSRDB\]$ ps -ef|grep 22584  
ora112   15567 16186  0 11:06 pts/1    00:00:00 grep 22584  
ora112   22584     1  1 Sep19 ?        01:14:13 ora\_vktm\_OSMSRDB  
  
登陆数据库，查看等待事件  
SELECT \* FROM V$SESSION\_WAIT WHERE WAIT\_CLASS <> 'Idle' ORDER BY SECONDS\_IN\_WAIT DESC;  
![Image 1][]  
  
查看数据库正在执行的SQL，发现一直在执行的是DBMS\_SCHEDULER模块，job名是OSMSR\_OPERATION\_DURATION  
select s.SID,s.SERIAL\#,MACHINE,TERMINAL, OSUSER, SCHEMANAME, PROGRAM,MODULE,ACTION,CLIENT\_INFO,  
 decode(s.COMMAND,3,'SELECT',s.COMMAND) COMMAND,STATUS,SQL\_ID,SQL\_ADDRESS,SQL\_EXEC\_START,  
DECODE(STATUS,'ACTIVE',LAST\_CALL\_ET,NULL) "SQL执行时间",EVENT,STATE,SECONDS\_IN\_WAIT,SERVICE\_NAME  
 from V$SESSION s where s.USERNAME is not null AND WAIT\_CLASS <> 'Idle';  
![Image 1][]  
  
查看job  
select job\_name,state from dba\_scheduler\_jobs where state= 'RUNNING';  
![Image 1][]  
  
停止job  
SQL> BEGIN  
  DBMS\_SCHEDULER.STOP\_JOB('OSMSR\_OPERATION\_DURATION');  
END;  2    3    
  4  /  
PL/SQL procedure successfully completed.  
  
SQL> select job\_name,state from dba\_scheduler\_jobs where state= 'RUNNING';  
no rows selected

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

转载于:http://blog.itpub.net/26506993/viewspace-2125355/

[Image 1]: