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

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

查看数据库正在执行的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';

查看job

select job_name,state from dba_scheduler_jobs where state= 'RUNNING';

停止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