诊断案例：由于sql的执行导致操作系统进程占用而极大提高了cpu

$ 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

这两个进程分别是20521和20845.通过PS命令可以找到这两个进程更为详细的信息： 

$ 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

这两个LOCAL=NO的进程显然是来自应用的远程连接，通过如下脚本，关联V$PROCESS视图和V$SESSION视图、V$SQLTEXT视图，可以找出这个进程正在执行的SQL语句，这里只需要一个“发动”条件，就是进程号（PID）： 

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个视图,并应用其关联进行数据获取，核心逻辑分解如下：

1.首先输入一个pid,这个pid即Process id,也就是在Top或ps中看到的PID。

2.通过pid和v$process.spid相关联我们可以获得Process的相关信息。

3.通过v$process.addr和v$session.paddr相关联,可以获得和session相关的所有信息。

4.再结合v$sqltext,即可获得当前session正在执行的SQL语句。

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

以SYSDBA身份连接到数据库，执行这个SQL，提供PID号，就找到了该进程正在执行的SQL代码：

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包跟踪该进程，或者通过AWR获得该SQL的执行计划等。