[原创]数据库性能诊断与优化_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3389 | 回复: 0   主题: [原创]数据库性能诊断与优化        下一篇 
jie.liang
注册用户
等级:少校
经验:1003
发帖:77
精华:0
注册:2013-10-11
状态:离线
发送短消息息给jie.liang 加好友    发送短消息息给jie.liang 发消息
发表于: IP:您无权察看 2014-3-20 17:53:12 | [全部帖] [楼主帖] 楼主

1.   问题描述

     XXXX系统为双节点的Oracle RAC,数据库版本为10.2.0.4。操作系统为HP-UX 11.31

     根据用户发送过来的资料显示:随着系统用户在线人数的增加,两个节点的CPU的负载均出现过载的情况。

2.   问题分析

  1. 经检查,两个节点的警告日志alert_sgeip1.log及alert_sgeip2.log在最近的一段时间只有关于日志切换的信息,并无可疑点。
  2. 从门户数据库的CPU的曲线来看,节点1的CPU的负载主要集中在在9点到10点以及下午3点到4点这两个时间段,峰值甚至达到100%。节点2的负载相对来说比较均衡,峰值在70%左右。证明应该是在业务高峰期间应该是有某个sql或者是某些任务在跑导致了这么高的CPU占用率。
  3. 从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
  4. 既然是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();




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论