mysql的表格扫描率值_一次IO利用率100%，数据库大量全表扫描问题

现象描述

1， 具体什么业务受到影响不清楚，但从系统测看，主机IO资源比较紧张(HPUX 11.31 +oracle 9i)

HP-UX crmdb3 B.11.23 U ia64 09/19/12

11:09:42 %usr %sys %wio %idle

11:09:45 28 5 64 3

11:09:48 28 2 61 9

11:09:51 28 2 67 3

11:09:54 33 2 57 7

11:09:57 31 2 59 7

glance看IO已接近100%

2，数据库侧看，大量db file scattered read IO相关等待事件

SID SERIAL# OSUSER USERNAME SVRPROC SQL_HASH_VALUE EVENT P1 P2 P3

------ ---------- -------- -------- ------------------------------------ -------------- ------------------------------ --------------------- ---------- ----------

89 28200 airsm ai 10261 664153718 db file scattered read 37 192750 8

159 43064 airsm ai 26996 3295997871 db file scattered read 36 60587 8

173 8048 airsm ai 3250 1002585284 db file scattered read 36 75123 8

458 18261 airsm ai 2505 2812298138 db file scattered read 36 365179 8

……..

3，等待的sql具体如下，主要原因是对ai.RM_A_x全表扫描，该表72GB大小。

SQL> @get_sql_by_hv

Enter value for hv: 1775869170

old 3: where hash_value = '&HV'

new 3: where hash_value = '1775869170'

SQL Text

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select a.serial_num, x.c_attr_1 crypto, to_char(a.expirt_dt, 'yy

yy-mm-dd') expirt_dt, k.key_info from RM_A_x x, RM_A

a, RM_A_key_info k

where a.row_id = x.row_id (+)

and k.row_id(+) = x.n_attr_1

a

nd serial_num in ( '12475014246302465', '12475014246302485', '1

2475014246302572', '12475014246302595', '12475014246302599', '12

475014246302620', '12475014246302636', '1247