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

转载 2004年10月26日 09:40:00

很多时候我们的服务器可能会经历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或者恢复后的异常
导致的,需要具体问题具体分析了.

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

很多时候我们的服务器可能会经历CPU消耗100%的性能问题.排除系统的异常,这类问题通常都是因为系统中存在性能低下甚至存在错误的SQL语句, 消耗了大量的CPU所致.本文通过一个案例就如何捕获这样的S...
  • lvzhqi
  • lvzhqi
  • 2007年11月30日 15:18
  • 481

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

如何诊断和解决CPU高度消耗(100%)的数据库问题 很多时候我们的服务器可能会经历CPU消耗100%的性能问题. 排除系统的异常,这类问题通常都是因为系统中存在性能低下甚至存在错误的SQL语句,...
  • chen3888015
  • chen3888015
  • 2012年04月23日 20:43
  • 12444

如何诊断和解决CPU高度消耗(100%)…

作者:eygle | English Version 【转载时请以超链接形式标明文章出处和作者信息及本声明】 链接:http://www.eygle.com/archives/2004/10/how...
  • babymouse1212
  • babymouse1212
  • 2017年05月02日 10:49
  • 91

解决CPU高度消耗(100%)的数据库问题

很多人在学习和工作中都曾遇到过服务器CPU消耗100%的性能问题,此类问题的产生原因一般都是因为系统中存在性能低下或者存在错误的SQL语句。在这里我们通过一个实例介绍一个就如何捕获此类SQL的常用方法...
  • chenxiaohua
  • chenxiaohua
  • 2008年03月16日 21:58
  • 751

Oracle性能诊断的方法

 Oracle性能诊断的方法    Oracle性能诊断的方法有很多,下面我谈谈个人的实践。 一般而言,如果需要进行性能调整,那么,肯定是存在一些性能问题。所以,诊断,要从用户所提出...
  • chichimanman
  • chichimanman
  • 2014年08月25日 16:23
  • 722

Oracle EBS 启用"帮助-诊断"

需设置三个配置文件,建议设置在用户层。FND:诊断        使在“帮助”选项里出现“诊断”菜单项公用程序:诊断        使点击“诊断”里的各项功能时不需要提示输入数据库密码隐藏诊断菜单项​...
  • sunansheng
  • sunansheng
  • 2017年04月14日 14:33
  • 14379

Oracle EBS诊断方面几种情况的解决方法(诊断菜单隐藏、个性化功能报错、关于此页的显示)

Oracle EBS诊断方面几种情况的解决方法,如诊断菜单隐藏、个性化功能报错、关于此页的显示等等。 情况1:帮助(Help)菜单下看不到"诊断"(Diagnostics...
  • meunsina
  • meunsina
  • 2016年08月04日 14:11
  • 1058

Oracle EBS启用帮助诊断检查

 帮助诊断检查不能使用时 1、配置文件——系统——显示勾选用户,选择需要设置的用户 2、配置文件名为:公用程序:诊断 3、查找,设置用户下的选项为‘是’...
  • gh320
  • gh320
  • 2013年12月05日 00:27
  • 2951

weblogic 消耗CPU 5000%诊断

现场报有一个节点CPU太高,其实这种问题我觉得很好找,之前也介绍过: 找CPU飙高的问题: 1.top找到最消耗CPU的进程 2.top -H -p 线程id显示具体线程占用CPU的...
  • guogang83
  • guogang83
  • 2017年06月29日 09:53
  • 505

oracle实时性能诊断命令

oracle实时性能诊断   最近在工作中遇到一些ORACLE性能问题,用AWR和Statspack报告来做分析的话,发现问题总觉得有些滞后性,而且是对数据库整体情况的评价,不够及时,所以又把盖国...
  • yfleng2002
  • yfleng2002
  • 2011年11月14日 11:12
  • 1478
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle诊断案例---如何诊断和解决CPU高度消耗(100%)的数据库问题
举报原因:
原因补充:

(最多只允许输入30个字)