两招让你知道数据库消耗IO的进程是谁？

在某客户现场保障，客户反映说“服务开通系统”IO占用率100%，让帮忙查原因。

登录到系统，两招帮客户找出了消耗IO的进程，具体步骤如下：

1、用glance查看系统资源

可以看到Disk Util为100%，按 g，再按o，再输入1，再输入disk，回车，这是按照IO排序，如下所示：

ProcList CPU Rpt  Mem Rpt  Disk Rpt                                                                                      NextKeys SlctProc   Help     Exit  
B3692A Glance C.04.70.000       11:26:31 sth01v02 9000/800                                                                          Current  Avg  High
------------------------------------------------------------------------------------------------------------------------------------------------------
CPU  Util   S                      SR                                RU                                                              | 49%   41%   60%
Disk Util   F                                                                                                                      F |100%  100%  100%
Mem  Util   S               SU                                                          UB    B                                      | 68%   68%   68%
Networkil   U                                                    UR                           R                                      | 69%   69%   69%
------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                     PROCESS LIST                                                         Users=   12
                         User      CPU %    Thrd Disk        Memory       Block
Process Name     PID     Name   (3400% max) Cnt  IOrate      RSS/VSS      On
--------------------------------------------------------------------------------
oraclestspsd        8342 oracle       63.2     1 431.6    79.3mb   79.4mb STRMS
oraclestspsd        8358 oracle       93.2     1 272.5    79.3mb   79.5mb STRMS
ora_lgwr_sts       13500 oracle        4.2     1 161.6    67.7mb  787.5mb    IO
oraclestspsd       25250 oracle       22.6     1 126.7    65.6mb   67.2mb    IO
ora_arc0_sts       13603 oracle        3.2     1  94.8    70.6mb   72.3mb    IO
ora_dbw1_sts       13493 oracle        1.6     1  81.9    60.7mb  792.5mb    IO
oraclestspsd       26244 ogguser       0.6     1  61.2    56.3mb   58.2mb    IO
oraclestspsd       26243 ogguser       0.6     1  58.7    56.2mb   58.4mb  PIPE
ora_dbw3_sts       13498 oracle        1.0     1  56.7    60.7mb  792.5mb    IO
ora_ckpt_sts       13507 oracle        1.0     1  52.9    56.4mb   62.9mb OTHER
ora_dbw0_sts       13491 oracle        0.6     1  44.8    60.8mb   61.9mb OTHER
ora_dbw2_sts       13495 oracle        0.6     1  40.9    60.3mb   60.9mb OTHER
oraclestspsd       26289 oracle       11.3     1  31.9    65.8mb   67.2mb OTHER
oraclestspsd       26295 oracle       11.0     1  24.8    62.1mb   65.1mb    IO
oraclestspsd       26336 oracle       10.6     1  24.8    64.5mb   67.1mb OTHER
extract            26236 ogguser      11.6     8  21.6   195.1mb  293.9mb OTHER
oraclestspsd       25027 oracle        9.4     1  11.6    59.8mb   61.2mb STRMS
oraclestspsd       28615 oracle        5.8     1  11.6    65.9mb   67.2mb STRMS
oraclestspsd       26364 oracle        9.4     1  11.2    65.6mb   67.1mb OTHER
oraclestspsd       25269 oracle       21.9     1   8.0    60.8mb   63.2mb OTHER
oraclestspsd       28829 oracle       25.2     1   7.7    60.9mb   63.2mb OTHER
oraclestspsd       23231 oracle        7.1     1   7.7    60.7mb   63.2mb STRMS
oraclestspsd       23245 oracle       13.5     1   7.7    60.8mb   63.2mb STRMS
oraclestspsd       25285 oracle       13.5     1   7.4    59.9mb   61.1mb OTHER
oraclestspsd       23602 oracle        3.5     1   7.4    60.9mb   63.2mb STRMS
ocssd.bin          28152 oracle        1.9    21   6.4   575.4mb  575.4mb SLEEP
           

这里我们可以很清楚的看到是Oracle用户的进程，pid为8342和8358等。那我们就继续去Oracle中查这两个进程是什么进程。

2、查询v$session就可以得到想要的答案

SQL> col program for a30
SQL> col username for a30
SQL> /
Enter value for ospid: 
old   : select sid,username,process,program from v$session where paddr in (select  addr from v$process where spid in ('&ospid'))
new   : select sid,username,process,program from v$session where paddr in (select  addr from v$process where spid in ('8358'))

       SID USERNAME                       PROCESS                  PROGRAM
---------- ------------------------------ ------------------------ ------------------------------
       SYS                                                 [email protected] (TNS V1-V3)

SQL> /
Enter value for ospid: 
old   : select sid,username,process,program from v$session where paddr in (select  addr from v$process where spid in ('&ospid'))
new   : select sid,username,process,program from v$session where paddr in (select  addr from v$process where spid in ('8342'))

       SID USERNAME                       PROCESS                  PROGRAM
---------- ------------------------------ ------------------------ ------------------------------
       SYS                                                 [email protected] (TNS V1-V3)

SQL>
           

发现是rman进程，奇怪了，怎么会有备份？

3、从操作系统层查看该进程

*****$(/home/oracle)ps -ef | grep 
  oracle      :: ?         : <defunct>
  oracle      :: ?        : oraclestspsdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
  oracle     :: pts/tg    : grep 
  oracle       :: ?         : oraclestspsdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
  oracle       :: ?         : oraclestspsdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
  oracle       :: ?         : /home/oracle/product//db_1/bin/rman target / catalog rman/[email protected] msglog /usr/openv/scripts/hot_database_backup.
  oracle      :: ?        : oraclestspsdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
*****$(/home/oracle)
           

看到了吧？8358、8342等都是进程8277的子进程，8277是rman备份进程，客户10：44：01启动了一个rman备份。

问题已查出，赶紧告知客户吧。