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

2024-07-21 02:53:02

字体：大中小

来源：转载

供稿：网友

两招让你知道数据库消耗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 BlockProcess Name PID Name (3400% max) Cnt IOrate rss/VSS On--------------------------------------------------------------------------------OraclestsPSD 8342 oracle 63.2 1 431.6 79.3mb 79.4mb STRMSoraclestspsd 8358 oracle 93.2 1 272.5 79.3mb 79.5mb STRMSora_lgwr_sts 13500 oracle 4.2 1 161.6 67.7mb 787.5mb IOoraclestspsd 25250 oracle 22.6 1 126.7 65.6mb 67.2mb IOora_arc0_sts 13603 oracle 3.2 1 94.8 70.6mb 72.3mb IOora_dbw1_sts 13493 oracle 1.6 1 81.9 60.7mb 792.5mb IOoraclestspsd 26244 ogguser 0.6 1 61.2 56.3mb 58.2mb IOoraclestspsd 26243 ogguser 0.6 1 58.7 56.2mb 58.4mb PipEora_dbw3_sts 13498 oracle 1.0 1 56.7 60.7mb 792.5mb IOora_ckpt_sts 13507 oracle 1.0 1 52.9 56.4mb 62.9mb OTHERora_dbw0_sts 13491 oracle 0.6 1 44.8 60.8mb 61.9mb OTHERora_dbw2_sts 13495 oracle 0.6 1 40.9 60.3mb 60.9mb OTHERoraclestspsd 26289 oracle 11.3 1 31.9 65.8mb 67.2mb OTHERoraclestspsd 26295 oracle 11.0 1 24.8 62.1mb 65.1mb IOoraclestspsd 26336 oracle 10.6 1 24.8 64.5mb 67.1mb OTHERextract 26236 ogguser 11.6 8 21.6 195.1mb 293.9mb OTHERoraclestspsd 25027 oracle 9.4 1 11.6 59.8mb 61.2mb STRMSoraclestspsd 28615 oracle 5.8 1 11.6 65.9mb 67.2mb STRMSoraclestspsd 26364 oracle 9.4 1 11.2 65.6mb 67.1mb OTHERoraclestspsd 25269 oracle 21.9 1 8.0 60.8mb 63.2mb OTHERoraclestspsd 28829 oracle 25.2 1 7.7 60.9mb 63.2mb OTHERoraclestspsd 23231 oracle 7.1 1 7.7 60.7mb 63.2mb STRMSoraclestspsd 23245 oracle 13.5 1 7.7 60.8mb 63.2mb STRMSoraclestspsd 25285 oracle 13.5 1 7.4 59.9mb 61.1mb OTHERoraclestspsd 23602 oracle 3.5 1 7.4 60.9mb 63.2mb STRMSoCSSd.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 a30SQL> col username for a30SQL> /Enter value for ospid: 8358old 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: 8342old 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备份。问题已查出，赶紧告知客户吧。

上一篇：JDBC的tinyInt1isBit

下一篇：安装pg_rman是出现libpq.so.5()(64bit) is needed by ...