一次IO利用率100%，数据库大量全表扫描问题(四)
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
解决方案：
www.2cto.com
由于问题比较紧急，需要尽快解决，因此使用了最简单有效的解决办法用hint，使用此方法修改sql后问题很快解决。
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
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
www.2cto.com
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 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"(+))
www.2cto.com
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