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

来源：赛迪网技术社区    作者：96312

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

系统CPU高度消耗,系统运行缓慢

OS:Sun Solaris8
Oracle:Oracle9203

◆首先我们通过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资源。

◆下一步找到存在问题的进程信息，以此确认它们是两个远程连接的用户进程。

$ 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

◆下面我们再来看一下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视图，我们就以把操作系统和数据库关联起来了。

◆下面，我们来连接数据库,找到问题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的元凶.

下面我们需要找出这段代码的问题，看一看是否可以通过优化来提高其效率，减少资源消耗.

◆下一步则可以通过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> !