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

3/7/2017来源:SQL技巧人气:770

两招让你知道数据库消耗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备份。 问题已查出,赶紧告知客户吧。