1. 问题描述
XXXX系统为双节点的Oracle RAC,数据库版本为10.2.0.4。操作系统为HP-UX 11.31
根据用户发送过来的资料显示:随着系统用户在线人数的增加,两个节点的CPU的负载均出现过载的情况。
2. 问题分析
- 经检查,两个节点的警告日志alert_sgeip1.log及alert_sgeip2.log在最近的一段时间只有关于日志切换的信息,并无可疑点。
- 从门户数据库的CPU的曲线来看,节点1的CPU的负载主要集中在在9点到10点以及下午3点到4点这两个时间段,峰值甚至达到100%。节点2的负载相对来说比较均衡,峰值在70%左右。证明应该是在业务高峰期间应该是有某个sql或者是某些任务在跑导致了这么高的CPU占用率。
- 从top命令来看,在这个时间段最消耗CPU的是ora_j000_sgeip1(ora_j000_sgeip2)这个job。从而证明了导致高CPU 占用率的是个任务(JOB),oracle对j000的官方解释是http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#sthref1602。
- 既然是CPU占用率过高,那么具体的内容可以从awr报告中看出来的。
但是可以看到awr报告中的首要等待事件都是跟CPU无关的。而且整个过程中redo产生的数据量很小,证明修改数据并非很频繁。
从awr报告中的等待事件一栏可以看出,数据库的等待事件排在前面的是log file parallel write、events in waitclass Other、db file parallel write 也就是说并无与CPU相关的等待事件出现。所以衡量CPU瓶颈的方式通常是看它的SQL ordered CPU Time及SQL ordered Gets这两个段可以找到一些线索。
从这两个区段来看排在首位的是:
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
从名字上可以看出:这个job是Oracle Enterprise Manager产生的。
最终调用了EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS这个存储过程。
同时注意,下面还有两个SQL Module的SEVERITY EVALUATION状态的,这表示它是通过调用EXECUTE_EM_DBMS_JOB_PROCS产生的,也就是排在前面几位的都是与这个job相关 的。而且可以看到,这两个语句执行的次数(Executions)比其它的语句执行次数高了好几个数量级。
以此我初步断定,cpu瞬间负载达到100%可能是这个job产生的。因为以前em导致的bug也很常见。
使用一下sql脚本对CPU占用率最高的进程进程捕获:
select b.username as DBUserName, c.spid as ProcessID, a.sql_text as SQLText from v$sql a,(select b1.paddr, b1.username, Decode(b1.sql_hash_value, 0, b1.prev_hash_value, b1.sql_hash_value) as sql_hash_value,Decode(b1.sql_address, '00', b1.prev_sql_addr, b1.sql_address) as sql_address from v$session b1 ) b, (select c1.addr, c1.spid from v$process c1) c where a.hash_value = b.sql_hash_value and a.address = b.sql_address and b.paddr = c.addr --and b.username = 'DB User Name' --and c.spid = 'System Process ID'order by 1,2,3
在上面的脚本中用实际的进程ID号来替换System Process ID得到CPU占用率最高的脚本为:
select count(*) from tbnetworkflow t where to_char(t.logindate1, ‘yyyy-mm’) = to_char (sysdate, ‘yyyy-mm’) and userid=’****’
其中***为不同的用户名
另外从awr报告的 SQL ordered by CPU Time 和 SQL ordered by Gets 来看
1. SELECT COUNT(*) FROM tbNetworkFlow t where t.zonesign='FY';
2. select WCMDOCUMENT.DOCID, DOCCHANNEL, WCMDOCUMENT.DOCSTATUS, DOCTYPE, DOCTITLE, DOCKEYWORDS, DOCRELWORDS, WCMDOCUMENT.CRUSER, WCMDOCUMENT.CRTIME, TITLECOLOR, DOCLINK, DOCFILENAME, DOCVERSION, DOCRELTIME, WCMDOCUMENT.DOCPUBTIME from WCMCHNLDOC, WCMDOCUMENT where (WCMCHNLDOC.CHNLID=:1 AND WCMCHNLDOC.DOCID=WCMDOCUMENT.DOCID) and ((WCMCHNLDOC.DocStatus>0 and DocChannel>0 and WCMDocument.DocStatus>0) and (WCMCHNLDOC.DOCSTATUS in(:2))) order by WCMCHNLDOC.DOCORDERPRI desc, WCMCHNLDOC.DOCORDER desc ;
3. select distinct RIGHTID from WCMRIGHT where ObjType=101 AND ObjId=2199 AND (OprType=203 AND OprId IN(9, 24, 2)) order by RIGHTID desc
4. select * from WCMCHNLDOC where ChnlId=:1 and DocId=:2
5. select zonesign, count(distinct t.userid) as count from tbnetworkflow t where to_char(t.logindate1, 'yyyy-mm-dd')=to_char(sysdate, 'yyyy-mm-dd') group by t.zonesign
从应用的业务逻辑角度来看,使用比较频繁的应该是以下的sql语句:
select t.varappid,a.varappname,count(*) count from tf_task_taskinfotb t,(select distinct varappname,varappid from tf_sso_apps) a where a.varappid = t.varappid and ( 1=0 or ((t.varappuserid='huyq0025' or t.varappuserid like 'huyq0025;%' or t.varappuserid like '%;huyq0025;%' or t.varappuserid like '%;huyq0025') and t.varappid='18') ) group by t.varappid,a.varappname
select sql_text ,sw.event from v$sqltext_with_newlines st,v$session se,v$session_wait sw where st.address=se.sql_address and st.hash_value=se.sql_hash_value and se.sid =sw.sid and (sw.event = 'buffer busy waits' or sw.event = 'enqueue' or sw.event = 'free buffer waits' or sw.event = 'global cache freelist wait' or sw.event = 'latch free' or sw.event = 'log buffer space' or sw.event = 'parallel query qref latch' or sw.event = 'pipe put' or sw.event = 'write complete waits' orsw.event like 'library cache%' or sw.event like 'log file switch%') order by st.hash_value,st.piece;
3. 分析结论:
建议可以先在业务高峰期禁用em, 如果不用em的话,可以一直禁用。
禁用em
1) emctl status dbconsole
2) emctl stop dbconsole
3) emctl status dbconsole
确认emctl已经关闭
4) 使用sysman用户登录。
exec emd_maintenance.remove_em_dbms_jobs;
如果想重新启用的话,可以采用如下方式进行:
1)使用sysman登录
2) alter system set job_queue_processes=0;
3) select * from dba_jobs_running;
直到产生 “no rows selected”.
4) 运行以下脚本:
\sysman\admin\emdrep\sql\core\latest\admin\admin_remove_dbms_jobs.sql;
\sysman\admin\emdrep\sql\core\latest\admin\admin_submit_dbms_jobs.sql;
5)执行 exec emd_maintenance.recompile_invalid_objects;
6)alter system set job_queue_processes=10;
7)执行 select job,what from dba_jobs;
确认EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS是否创建成功。
8) exec dbms_job.run();