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