概述

最近系统总是突然卡顿，数据库并没有阻塞，并发量也不是很高，通过awr及执行时间超过10s的脚本可以发现是定时任务BUSINESS_DATA_PURGE导致，该任务是清理表数据的相关任务，经确认可禁用。

下面介绍一下该问题排查的过程及思路..

以下脚本都可在ORACLE DBA实战脚本查到

---

1、查看当前正在执行的定时任务

SELECT 'kill -9 ' || p.spid, /*p.spid,p.pid,*/       s.sid,s.sql_id,       s.username,       s.machine,       s.sql_hash_value,       s.last_call_et "秒",       s.last_call_et/60 "运行时间",       s.client_info,       p.program "OSProgram",       'alter system kill session ''' || s.SID || ',' || s.SERIAL# || ''';'  FROM v$session s, v$process p WHERE (s.status = 'ACTIVE')   AND ((s.username IS NOT NULL) AND (NVL(s.osuser, 'x') <> 'SYSTEM') AND       (s.TYPE <> 'BACKGROUND'))   AND (p.addr(+) = s.paddr)      --and s.username in ('CRMDB')    and s.last_call_et > 10/*and s.sql_hash_value=880766746*/ ORDER BY s.last_call_et/60 desc, "USERNAME" ASC, ownerid, "USERNAME" ASC;

---

2、监控执行时间超过10s的sql语句

SELECT 'kill -9 ' || p.spid, /*p.spid,p.pid,*/       s.sid,s.sql_id,       s.username,       s.machine,       s.sql_hash_value,       s.last_call_et "秒",       s.last_call_et/60 "运行时间",       s.client_info,       p.program "OSProgram",       'alter system kill session ''' || s.SID || ',' || s.SERIAL# || ''';'  FROM v$session s, v$process p WHERE (s.status = 'ACTIVE')   AND ((s.username IS NOT NULL) AND (NVL(s.osuser, 'x') <> 'SYSTEM') AND       (s.TYPE <> 'BACKGROUND'))   AND (p.addr(+) = s.paddr)      --and s.username in ('CRMDB')    and s.last_call_et > 10/*and s.sql_hash_value=880766746*/ ORDER BY s.last_call_et/60 desc, "USERNAME" ASC, ownerid, "USERNAME" ASC;

从上面两条sql就可以定位到是某条定时任务导致了..

---

3、根据sid查看具体的sql语句

select username, sql_text, machine, osuser  from gv$session a, gv$sqltext_with_newlines b where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value) =       b.hash_value   and a.sid = &sid order by piece;

---

4、查看定时任务属性

 select owner,job_name,job_type,job_action,comments,enabled,to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss'),to_char(next_run_date,'yyyy-mm-dd hh24:mi:ss'),d.* from dba_scheduler_jobs d;

---

5、禁用任务提示该任务在运行

begindbms_scheduler.disable(‘GLOGOWNER.BUSINESS_DATA_PURGE’);end;/

---

6、停止任务后再次禁用提示任务不存在

神奇的现象发生了：停止该定时任务，禁用时提示任务不存在

beginDBMS_SCHEDULER.stop_job(‘GLOGOWNER.BUSINESS_DATA_PURGE’);end;/begindbms_scheduler.disable(‘GLOGOWNER.BUSINESS_DATA_PURGE’);end;/

---

7、再次查看定时任务

再次查看定时任务，job已经消失了，所以这里无法禁用

 select owner,job_name,job_type,job_action,comments,enabled,to_char(last_start_date,‘yyyy-mm-dd hh24:mi:ss’),to_char(next_run_date,‘yyyy-mm-dd hh24:mi:ss’) from dba_scheduler_jobs;

---

8、怀疑是auto_drop属性导致(无法验证)

查看定时任务相关属性，怀疑是auto_drop属性导致，我这里是TRUE，需要改成FALSE，但一删该任务就不见了，无法修改属性，所以后面我选择了强制drop

AUTO_DROP ：当该标志被置为TRUE时，ORACLE会在满足条件时自动删除创建的任务

• 任务已过期；
• 任务最大运行次数已达MAX_RUNS的设置值；
• 任务未指定REPEAT_INTERVAL参数，仅运行一次；

---

9、强制干掉该定时任务

后续观察应用系统运行情况，系统卡顿不再发生，问题解决。

begin  dbms_scheduler.drop_job(job_name => 'BUSINESS_DATA_PURGE',force => TRUE); end;

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~