一次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', '12475014246302765')

9 rows selected.

SQL> /
Enter value for hv: 2144161010
old   3:  where hash_value = '&HV'
new   3:  where hash_value = '2144161010'

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 (  '12475014246306603', '12475014246306726', '1
2475014246306804')

8 rows selected.

SQL>  select bytes/1024/1024 M,owner from dba_segments where segment_name ='RM_A';

         M OWNER
---------- ------------------------------------------------------
     71206 ai
     
SQL> @showplan_9i
Enter value for hash: 125827763
old  29: ha