一次IO利用率100%，数据库大量全表扫描问题(二)
9: hash_value='125827763'
www.2cto.com
Optimizer Plan:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
|SELECT STATEMENT |----- 125827763 [0]
-| | | |
|NESTED LOOPS OUTER | | | | |
| NESTED LOOPS OUTER | | | | |
| TABLE ACCESS FULL |RM_A | | | |
| TABLE ACCESS BY INDEX ROWID |RM_A_X | | | |
| INDEX UNIQUE SCAN |RM_A_X_P1 | | | |
| TABLE ACCESS BY INDEX ROWID |RM_A_KEY_INFO | | | |
| INDEX UNIQUE SCAN |RM_A_KEY_INFO_P1 | | | |
www.2cto.com
原因分析
1，该表有相关索引，RM_A_M1对应SERIAL_NUM列的索引，字段类型也匹配
[sql]
Index Column Col Column
Name Name Pos Details
--------------- ------------------------- ---- ------------------------
RM_A_F1 BUCKET_ID 1 NUMBER(15,0)
BUCKET_ID 1 NUMBER(15,0)
RM_A_F2 INV_ID 1 NUMBER(15,0)
INV_ID 1 NUMBER(15,0)
RM_A_M1 SERIAL_NUM 1 VARCHAR2(80)
SERIAL_NUM 1 VARCHAR2(80)
www.2cto.com
2，为什么不走索引呢.原来是使用基于rule类型的优化器，RULE优化器根据FROM列表中的位置来选择驱动表，FROM列表中最后一个表被作为驱动表。
[sql]
SQL> select OPTIMIZER_MODE from v$sqlarea where HASH_VALUE='125827763';
OPTIMIZER_MODE
---------------------------------------------------------------------------
RULE
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------
RM_A_X NO NO
SQL> select CREATED,OBJECT_NAME from dba_objects where object_name ='RM_A_X' and owner='ai';
www.2cto.com
CREATED OBJECT_NAME
---------------------------------------------------------------------------------------------------- --------------------
2012-06-26 09:52:48 RM_A_X
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_mode string RULE
3，rbo的执行顺序如下，安装如下文章所述，IN应该属于rank 10,如果把in改成单条件=则直接走相关索引
www.2cto.com
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