一次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相关等待事件
SQL> select OPTIMIZER_MODE from v$sqlarea where HASH_VALUE='125827763';OPTIMIZER_MODE---------------------------------------RULETable Number Empty Average Chain Average Global User Sample DateName of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------RM_A_X NO NOSQL> select CREATED,OBJECT_NAME from dba_objects where object_name ='RM_A_X' and owner='ai';CREATED OBJECT_NAME---------------------------------------------------------------- --------------------2012-06-26 09:52:48 RM_A_XSQL> show parameter optimizer_modeNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------optimizer_mode string RULE
3，rbo的执行顺序如下，安装如下文章所述，IN应该属于rank 10,如果把in改成单条件=则直接走相关索引
sing the RBO, the optimizer chooses an execution plan based on the access paths available and the ranks of these access paths. Oracle's ranking of the access paths is heuristic. If there is more than one way to execute a SQL statement, then the RBO always uses the operation with the lower rank. Usually, operations of lower rank execute faster than those associated with constructs of higher rank.
The list shows access paths and their ranking:
RBO Path 1: Single Row by Rowid
RBO Path 2: Single Row by Cluster Join
RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
RBO Path 4: Single Row by Unique or Primary Key
RBO Path 5: Clustered Join
RBO Path 6: Hash Cluster Key
RBO Path 7: Indexed Cluster Key
RBO Path 8: Composite Index
RBO Path 9: Single-Column Indexes
RBO Path 10: Bounded Range Search on Indexed Columns
RBO Path 11: Unbounded Range Search on Indexed Columns
RBO Path 12: Sort Merge Join
RBO Path 13: MAX or MIN of Indexed Column
RBO Path 14: ORDER BY on Indexed Column
RBO Path 15: Full Table Scan
解决方案：
SQL> explain plan for
2 select /*+index(a RM_A_M1) */ a.serial_num, x.c_attr_1 crypto, to_char(a.expirt_dt, 'yyyy-mm-dd') expirt_dt, k.key_info from ai.RM_A_key_info k ,ai.RM_A_x x ,ai.RM_A
3 a where a.row_id = x.row_id (+)
4 and k.row_id(+) = x.n_attr_1 and serial_num in ( '12475014246300079', '12475014246300099');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
----------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6204 | 9 |
| 1 | NESTED LOOPS OUTER | | 2 | 6204 | 9 |
| 2 | NESTED LOOPS OUTER | | 2 | 4126 | 7 |
| 3 | INLIST ITERATOR | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| RM_A | 2 | 70 | 5 |
|* 5 | INDEX RANGE SCAN | RM_A_M1 | 2 | | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID | RM_A_X | 1 | 2028 | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
|* 7 | INDEX UNIQUE SCAN | RM_A_X_P1 | 1 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | RM_A_KEY_INFO | 1 | 1039 | 1 |
|* 9 | INDEX UNIQUE SCAN | RM_A_KEY_INFO_P1 | 1 | | |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."SERIAL_NUM"='12475014246300079' OR
"A"."SERIAL_NUM"='12475014246300099')
7 - access("A"."ROW_ID"="X"."ROW_ID"(+))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
9 - access("K"."ROW_ID"(+)="X"."N_ATTR_1")
Note: cpu costing is off
25 rows selected.
参考资料
http://docs.oracle.com/cd/B10501_01/server.920/a96533/rbo.htm#39232