现象描述
1， 具体什么业务受到影响不清楚，但从系统测看，主机IO资源比较紧张（HPUX 11.31 +oracle 9i）
HP-UX crmdb3 B.11.23 U ia64 09/19/12
www.2cto.com
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相关等待事件
[sql]
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]
SQL> @get_sql_by_hv
Enter value for hv: 1775869170
old 3: where hash_value = '&HV'
new 3: where hash_value = '1775869170'
SQL Text www.2cto.com
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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'
www.2cto.com
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: hash_value='&hash'
new 2