两招让你知道数据库消耗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: 8358
old 1: select sid,username,process,program from v$session where paddr in (select addr from v$process where spid in ('&ospid'))
new 1: select sid,username,process,program from v$session where paddr in (select addr from v$process where spid in ('8358'))
SID USERNAME PROCESS PROGRAM
---------- ------------------------------ ------------------------ ------------------------------
4890 SYS 8277 rman@sth01v02 (TNS V1-V3)
SQL> /
Enter value for ospid: 8342
old 1: select sid,username,process,program from v$session where paddr in (select addr from v$process where spid in ('&ospid'))
new 1: select sid,username,process,program from v$session where paddr in (select addr from v$process where spid in ('8342'))
SID USERNAME PROCESS PROGRAM
---------- ------------------------------ ------------------------ ------------------------------
4702 SYS 8277 rman@sth01v02 (TNS V1-V3)
SQL>
发现是rman进程,奇怪了,怎么会有备份?
3、从操作系统层查看该进程
*****$(/home/oracle)ps -ef | grep 8277
oracle 8288 8277 13 10:44:03 ? 0:00 <defunct>
oracle 8358 8277 252 10:44:13 ? 21:05 oraclestspsdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 13256 24344 1 11:34:58 pts/tg 0:00 grep 8277
oracle 8294 8277 0 10:44:04 ? 0:00 oraclestspsdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 8287 8277 0 10:44:03 ? 0:03 oraclestspsdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 8277 8258 0 10:44:01 ? 0:07 /home/oracle/product/10.2.0/db_1/bin/rman target / catalog rman/R2_manbak@rmanbk msglog /usr/openv/scripts/hot_database_backup.
oracle 8342 8277 255 10:44:11 ? 21:15 oraclestspsdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
*****$(/home/oracle)
看到了吧?8358、8342等都是进程8277的子进程,8277是rman备份进程,客户10:44:01启动了一个rman备份。
问题已查出,赶紧告知客户吧。