linux下 ORACLE进程占用高CPU利用率

公司服务器Oracle数据库经常会遇到CPU利用率很高的情况，而内存和I/O都不高，top查看如下

先查看数据库的告警日志ALERT文件，并没有发现有什么错误存在，日志显示数据库运行正常，排除数据库本身存在问题。

然后查看这些占用CPU资源很高的Oracle进程究竟是在做什么操作，使用如下SQL语句：

select sql_text,spid,v$session.program,process  from v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value 
and v$session.paddr=v$process.addr
and v$process.spid in (PID);

用top中占用CPU很高的进程的PID替换脚本中的PID,得到相应的Oracle进程所执行的SQL语句，发现占用CPU资源很高的进程都是执行同一个SQL语句：

 

 --PID3713 
 INSERT INTO T_SBM_MT_TRANS_DAY_DTL (ID_, DATA_TYPE_, STA_COUNT_, GEN_DATE_, CREATE_ID_, CREATE_DATE_)    SELECT 021411280811,AREA_NAME_, COUNT(AREA_NAME_),TO_DATE('0214-11-28 13:40:00', 'YYYY-MM-DD HH24:MI:SS'),'0', SYSDATE    FROM (SELECT DECODE(C.AREA_NAME_, NULL, '未知区域', AREA_NAME_) AREA_NAME_              FROM T_SBM_BIKE_HIRE A              LEFT JOIN T_SBM_STATION B ON A.STATION_ID_ = B.ID_              LEFT JOIN T_SBM_AREA C ON B.AREA_ID_ = C.ID_               WHERE HIRE_DATE_ > TO_DATE('0214-11-28 13:30:00', 'YYYY-MM-DD HH24:MI:SS') AND               HIRE_DATE_ < TO_DATE('0214-11-28 13:40:00', 'YYYY-MM-DD HH24:MI:SS')) A               GROUP BY AREA_NAME_  HAVING COUNT(AREA_NAME_) > 0      
 --PID3748
INSERT INTO T_SBM_MT_TRANS_DAY_DTL (ID_, DATA_TYPE_, STA_COUNT_, GEN_DATE_, CREATE_ID_, CREATE_DATE_)    SELECT 021501181421,AREA_NAME_, COUNT(AREA_NAME_),TO_DATE('0215-01-18 23:50:00', 'YYYY-MM-DD HH24:MI:SS'),'0', SYSDATE    FROM (SELECT DECODE(C.AREA_NAME_, NULL, '未知区域', AREA_NAME_) AREA_NAME_              FROM T_SBM_BIKE_HIRE A              LEFT JOIN T_SBM_STATION B ON A.STATION_ID_ = B.ID_              LEFT JOIN T_SBM_AREA C ON B.AREA_ID_ = C.ID_               WHERE HIRE_DATE_ > TO_DATE('0215-01-18 23:40:00', 'YYYY-MM-DD HH24:MI:SS') AND               HIRE_DATE_ < TO_DATE('0215-01-18 23:50:00', 'YYYY-MM-DD HH24:MI:SS')) A               GROUP BY AREA_NAME_  HAVING COUNT(AREA_NAME_) > 0   
--PID3746   
INSERT INTO T_SBM_MT_TRANS_DAY_DTL (ID_, DATA_TYPE_, STA_COUNT_, GEN_DATE_, CREATE_ID_, CREATE_DATE_)    SELECT 021502090331,AREA_NAME_, COUNT(AREA_NAME_),TO_DATE('0215-02-09 05:40:00', 'YYYY-MM-DD HH24:MI:SS'),'0', SYSDATE    FROM (SELECT DECODE(C.AREA_NAME_, NULL, '未知区域', AREA_NAME_) AREA_NAME_              FROM T_SBM_BIKE_HIRE A              LEFT JOIN T_SBM_STATION B ON A.STATION_ID_ = B.ID_              LEFT JOIN T_SBM_AREA C ON B.AREA_ID_ = C.ID_               WHERE HIRE_DATE_ > TO_DATE('0215-02-09 05:30:00', 'YYYY-MM-DD HH24:MI:SS') AND               HIRE_DATE_ < TO_DATE('0215-02-09 05:40:00', 'YYYY-MM-DD HH24:MI:SS')) A               GROUP BY AREA_NAME_  HAVING COUNT(AREA_NAME_) > 0

 

而时间明显不合法：TO_DATE('0215-02-09 05:30:00', 'YYYY-MM-DD HH24:MI:SS')，导致系统的统计任务一直走在执行，从0215年统计到2015年的数据

 

转载于:https://www.cnblogs.com/zealotpz/p/4387051.html