开发者社区> jimbuster> 正文

(转)如何诊断和解决CPU高度消耗(100%)的数据库问题

简介: 如何诊断和解决CPU高度消耗(100%)的数据库问题 2008-05-24 13:46 链接:http://www.eygle.com/archives/2004/10/howto_getsql_which_cost_most_cpu.
+关注继续查看
如何诊断和解决CPU高度消耗(100%)的数据库问题
2008-05-24 13:46

链接:

很多时候我们的服务器可能会经历CPU消耗100%的性能问题.
排除系统的异常,这类问题通常都是因为系统中存在性能低下甚至存在错误的SQL语句, 消耗了大量的CPU所致.

本文通过一个案例就如何捕获这样的SQL给出一个通用的方法.

问题描述:系统CPU高度消耗,系统运行缓慢
OS:Sun Solaris8
Oracle:Oracle9203

1.首先通过Top命令查看

$ top
                        load averages:  1.61,  1.28,  1.25                     HSWAPJSDB             10:50:44
                        172 processes: 160 sleeping, 1 running, 3 zombie, 6 stopped, 2 on cpu
                        CPU states:     % idle,     % user,     % kernel,     % iowait,     % swap
                        Memory: 4.0G real, 1.4G free, 1.9G swap in use, 8.9G swap free
                        PID USERNAME THR PR NCE  SIZE   RES STATE   TIME FLTS    CPU COMMAND
                        20521 oracle     1 40   0  1.8G  1.7G run     6:37    0 47.77% oracle
                        20845 oracle     1 40   0  1.8G  1.7G cpu02   0:41    0 40.98% oracle
                        20847 oracle     1 58   0  1.8G  1.7G sleep   0:00    0  0.84% oracle
                        20780 oracle     1 48   0  1.8G  1.7G sleep   0:02    0  0.83% oracle
                        15828 oracle     1 58   0  1.8G  1.7G sleep   0:58    0  0.53% oracle
                        20867 root       1 58   0 4384K 2560K sleep   0:00    0  0.29% sshd2
                        20493 oracle     1 58   0  1.8G  1.7G sleep   0:03    0  0.29% oracle
                        20887 oracle     1 48   0  1.8G  1.7G sleep   0:00    0  0.13% oracle
                        20851 oracle     1 58   0  1.8G  1.7G sleep   0:00    0  0.10% oracle
                        20483 oracle     1 48   0  1.8G  1.7G sleep   0:00    0  0.09% oracle
                        20875 oracle     1 45   0 1064K  896K sleep   0:00    0  0.07% sh
                        20794 oracle     1 58   0  1.8G  1.7G sleep   0:00    0  0.06% oracle
                        20842 jiankong   1 52   2 1224K  896K sleep   0:00    0  0.05% sadc
                        20888 oracle     1 55   0 1712K 1272K cpu00   0:00    0  0.05% top
                        19954 oracle     1 58   0  1.8G  1.7G sleep  84:25    0  0.04% oracle

我们发现在进城列表里,存在两个高CPU耗用的Oracle进城,分别消耗了47.77%和40.98%的CPU资源.

 

2.找到存在问题的进程信息

 

                        $ ps -ef|grep 20521
                        oracle 20909 20875  0 10:50:53 pts/10   0:00 grep 20521
                        oracle 20521     1 47 10:43:59 ?        6:45 oraclejshs (LOCAL=NO)
                        $ ps -ef|grep 20845
                        oracle 20845     1 44 10:50:00 ?        0:55 oraclejshs (LOCAL=NO)
                        oracle 20918 20875  0 10:50:59 pts/10   0:00 grep 20845

确认这是两个远程连接的用户进程.

 

3.熟悉一下我的getsql.sql脚本

 

                        SELECT   /*+ ORDERED */
                        sql_text
                        FROM v$sqltext a
                        WHERE (a.hash_value, a.address) IN (
                        SELECT DECODE (sql_hash_value,
                        0, prev_hash_value,
                        sql_hash_value
                        ),
                        DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
                        FROM v$session b
                        WHERE b.paddr = (SELECT addr
                        FROM v$process c
                        WHERE c.spid = '&pid'))
                        ORDER BY piece ASC
                        /

注意这里我们涉及了3个视图,并应用其关联进行数据获取.
首先需要输入一个pid,这个pid即process id,也就是在Top或ps中我们看到的PID.
通过pid和v$process.spid相关联我们可以获得Process的相关信息
进而通过v$process.addr和v$session.paddr相关联,我们就可以获得和session相关的所有信息.
再结合v$sqltext,我们即可获得当前session正在执行的SQL语句.

通过v$process视图,我们得以把操作系统和数据库关联了起来.

 

4.连接数据库,找到问题sql及进程

通过Top中我们观察到的PID,进而应用我的getsql脚本,我们得到以下结果输出.

 

                        $ sqlplus "/ as sysdba"
                        SQL*Plus: Release 9.2.0.3.0 - Production on Mon Dec 29 10:52:14 2003
                        Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
                        Connected to:
                        Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
                        With the Partitioning, OLAP and Oracle Data Mining options
                        JServer Release 9.2.0.3.0 - Production
                        SQL> @getsql
                        Enter value for spid: 20521
                        old  10: where c.spid = '&pid'
                        new  10: where c.spid = '20521'
                        SQL_TEXT
                        ----------------------------------------------------------------
                        select * from (select VC2URL,VC2PVDID,VC2MOBILE,VC2ENCRYPTFLAG,S
                        ERVICEID,VC2SUB_TYPE,CISORDER,NUMGUID,VC2KEY1, VC2NEEDDISORDER,V
                        C2PACKFLAG,datopertime from hsv_2cpsync where datopertime<=sysda
                        te and numguid>70000000000308 order by NUMGUid) where rownum<=20

那么这段代码就是当前正在疯狂消耗CPU的罪魁祸首.
接下来需要进行的工作就是找出这段代码的问题,看是否可以通过优化提高其效率,减少资源消耗.

 

5.进一步的我们可以通过dbms_system包跟踪该进程

 

SQL> @getsid
Enter value for spid: 20521
old 3: select addr from v$process where spid = &spid)
new 3: select addr from v$process where spid = 20521)

SID SERIAL# USERNAME MACHINE
----------------------------------------------------------------
45 38991 HSUSER_V51 hswapjsptl1.hurray.com.cn

SQL> exec dbms_system.set_sql_trace_in_session(45,38991,true);

PL/SQL procedure successfully completed.

SQL> !

 

这部分内容可以参考:
http://www.eygle.com/case/sql_trace_1.htm

对于Windows上的类似问题,可以参考:
http://www.eygle.com/faq/Use.Nt.tools.manage.Oracle.htm

 

6.一点说明

很多时候,高CPU消耗都是由于问题SQL导致的,所以找到这些SQL通常也就找到了问题所在,通过优化调整
通常就可以解决问题。

但是有时候你可能会发现,这些最消耗CPU的进程是后台进程,这一般是由于异常、BUG或者恢复后的异常
导致的,需要具体问题具体分析了.

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MongoDB云数据库常见问题诊断
重要的内容 MongoDB的主备节点在运行过程中是不固定的,实例重启、升级、节点故障等都有可能导致主备切换,在生产环境应该使用副本集的方式来正确连接MongoDB来实现高可用。 连接问题 用户可通过DMS或mongo shell连接MongoDB云数据库,以下场景都基于用户使用mongo sh
16865 0
excel文件内容导入数据库的问题及解决
今天需要导一些数据,从excel导入到数据库中。 没有装现成的plsqldev,只能用sql*loader来弄了。 首先我把excel文件的内容转换成csv文件,以逗号分隔,在另存外excel文件的时候有那个选项。
1140 0
一次数据库响应慢的问题诊断
今天接到开发一个同事的电话,说前端系统那边反馈有一个查询很慢,初步怀疑是有一些并发或者锁之类的问题导致的。 接到问题之后,自己还是带着一些的紧迫感来处理的。 首先查看资源使用情况,使用top来检查,结果发现CPU使用率也不高,都在90%以上的idle 查看数据库的DB time情况,发现数据库的负载其实不高,但是还是有所提高,需要进一步关注。
750 0
【阿里云大数据产品MaxCompute(原名ODPS)】DT时代企业数据资产的护卫舰
MaxCompute设计之初就是面向多租户,确保租户的数据安全是MaxCompute的必备功能之一。在MaxCompute系统的安全设计和实现上,MaxCompute的工程师们会遵循一些经过实践检验的安全设计原则(如Saltzer-Schroeder原则)。
3657 0
数据库突然宕机无法open的问题及解决
测试的数据库有一天突然宕机,然后无法正常open了,这个问题虽然过去了一段时间,也在这儿总结一把。 从alert日志中的信息如下。 Fri Jan 10 16:09:42 2014 Archived Log entry 6837 added for thr...
740 0
主机cpu突然飙高,如何快速排查问题
主机cpu突然飙高,如何快速排查问题[问题发现] 使用zabbix软件监控服务器时发现cpu突然异常,在业务主机上使用top命令查看系统的整体运行情况,使用top命令后发现mysqld占用CPU特别高,初步判断可能是mysqld出现问题,需要排查: [排查步骤] Step1: 登录oneapm ai平台后可以看到应用列表的总览视图,在总览视图中可以看到所有应用的名称以及相关指标信息,同时我们还可以根据应用颜色变化来判断每个应用的指标变化情况。
1386 0
成功解决:Win系统下的Tensorflow使用CPU而不使用GPU运行加速
成功解决:Win系统下的Tensorflow使用CPU而不使用GPU运行加速
82 0
如何重置Joomla并解决文件或数据库问题
如果您在Joomla网站上遇到问题,那么重新安装其核心文件和数据库可能是最好的解决方案。
794 0
利用MySQL系统数据库做性能负载诊断
利用MySQL系统数据库做性能负载诊断某大师曾说过,像了解自己的老婆 一样了解自己管理的数据库,个人认为包含了两个方面的了解:1,在稳定性层面来说,更多的是关注高可用、读写分离、负载均衡,灾备管理等等high level层面的措施(就好比要保证生活的稳定性)2,在实例级别的来说,需要关注内存、IO、网络,热点表,热点索引,top sql,死锁,阻塞,历史上执行异常的SQL(好比生活品质细节)MySQL的performance_data库和sys库提供了非常丰富的系统日志数据,可以帮助我们更好地了解非常细节的,这里简单地列举出来了一些常用的数据。
2920 0
+关注
jimbuster
从事数据库管理和运维
280
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载