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

eygle

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

原文链接:
http://www.eygle.com/case/how.to.getsql.which.cost.most.CPU.htm

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

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

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

1.首先通过Top命令查看

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

复制代码

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

eygle

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

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

复制代码

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

eygle

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

1. 
   
2. SELECT   /*+ ORDERED */
   
3.          sql_text
   
4.     FROM v$sqltext a
   
5.    WHERE (a.hash_value, a.address) IN (
   
6.             SELECT DECODE (sql_hash_value,
   
7.                            0, prev_hash_value,
   
8.                            sql_hash_value
   
9.                           ),
   
10.                    DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
    
11.               FROM v$session b
    
12.              WHERE b.paddr = (SELECT addr
    
13.                                 FROM v$process c
    
14.                                WHERE c.spid = '&pid'))
    
15. ORDER BY piece ASC
    
16. /
    

复制代码

注意这里我们涉及了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视图，我们得以把操作系统和数据库关联了起来.

eygle

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

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

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

复制代码

eygle

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

eygle

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

1. 
   
2. SQL>; @getsid
   
3. Enter value for spid: 20521
   
4. old   3: select addr from v$process where spid = &spid)
   
5. new   3: select addr from v$process where spid = 20521)
   
6. 
   
7.        SID    SERIAL# USERNAME        MACHINE
   
8. ----------------------------------------------------------------
   
9.         45      38991 HSUSER_V51    hswapjsptl1.hurray.com.cn
   
10. 
    
11. 
    
12. SQL>; exec dbms_system.set_sql_trace_in_session(45,38991,true);
    
13. 
    
14. PL/SQL procedure successfully completed.
    
15. 
    
16. SQL>; !
    
17. 
    

复制代码

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

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

eygle

6.一点说明

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

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

rainy_gong

好,顶一下

zero_dot

酷。顶。。。。。。。。。

UnKnow365

COOL！DING!