信息来源:邪恶八进制信息安全团队

原文链接: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命令查看

代码:


$ top



load averages:1.61,1.28,1.25HSWAPJSDB10: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 NCESIZERES STATETIME FLTSCPU COMMAND

 20521 oracle1 4001.8G1.7G run6:370 47.77% oracle

 20845 oracle1 4001.8G1.7G cpu020:410 40.98% oracle

 20847 oracle1 5801.8G1.7G sleep0:0000.84% oracle

 20780 oracle1 4801.8G1.7G sleep0:0200.83% oracle

 15828 oracle1 5801.8G1.7G sleep0:5800.53% oracle

 20867 root1 580 4384K 2560K sleep0:0000.29% sshd2

 20493 oracle1 5801.8G1.7G sleep0:0300.29% oracle

 20887 oracle1 4801.8G1.7G sleep0:0000.13% oracle

 20851 oracle1 5801.8G1.7G sleep0:0000.10% oracle

 20483 oracle1 4801.8G1.7G sleep0:0000.09% oracle

 20875 oracle1 450 1064K896K sleep0:0000.07% sh

 20794 oracle1 5801.8G1.7G sleep0:0000.06% oracle

 20842 jiankong1 522 1224K896K sleep0:0000.05% sadc

 20888 oracle1 550 1712K 1272K cpu000:0000.05% top

 19954 oracle1 5801.8G1.7G sleep84:2500.04% oracle

.

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

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

代码:


$ ps -ef|grep 20521

oracle 20909 208750 10:50:53 pts/100:00 grep 20521

oracle 205211 47 10:43:59 ?6:45 oraclejshs (LOCAL=NO)

$ ps -ef|grep 20845

oracle 208451 44 10:50:00 ?0:55 oraclejshs (LOCAL=NO)

oracle 20918 208750 10:50:59 pts/100: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

old10: where c.spid = '&pid'

new10: 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

old3: select addr from v$process where spid = &spid)

new3: select addr from v$process where spid = 20521)



SIDSERIAL# USERNAMEMACHINE

----------------------------------------------------------------

4538991 HSUSER_V51hswapjsptl1.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

一点说明

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

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