一次IO利用率100%，数据库大量全表扫描问题

一次IO利用率100%，数据库大量全表扫描问题

 

现象描述

 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 = &＃39;&HV&＃39;  

new   3:  where hash_value = &＃39;1775869170&＃39;  

  

SQL Text    www.2cto.com  

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  

select a.serial_num, x.c_attr_1 crypto, to_char(a.expirt_dt, &＃39;yy  

yy-mm-dd&＃39;) 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 (  &＃39;12475014246302465&＃39;, &＃39;12475014246302485&＃39;, &＃39;1  

2475014246302572&＃39;, &＃39;12475014246302595&＃39;, &＃39;12475014246302599&＃39;, &＃39;12  

475014246302620&＃39;, &＃39;12475014246302636&＃39;, &＃39;12475014246302765&＃39;)  

  

9 rows selected.  

  

SQL> /  

Enter value for hv: 2144161010  

old   3:  where hash_value = &＃39;&HV&＃39;  

new   3:  where hash_value = &＃39;2144161010&＃39;  

www.2cto.com  

SQL Text  

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  

select a.serial_num, x.c_attr_1 crypto, to_char(a.expirt_dt, &＃39;yy  

yy-mm-dd&＃39;) 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 (  &＃39;12475014246306603&＃39;, &＃39;12475014246306726&＃39;, &＃39;1  

2475014246306804&＃39;)  

  

8 rows selected.  

  

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

  

         M OWNER  

---------- ------------------------------------------------------------------------------------------  

     71206 ai  

       

SQL> @showplan_9i  

Enter value for hash: 125827763  

old  29: hash_value=&＃39;&hash&＃39;  

new  29: hash_value=&＃39;125827763&＃39;  

    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=&＃39;125827763&＃39;;  

  

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 =&＃39;RM_A_X&＃39; and owner=&＃39;ai&＃39;;  

    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&＃39;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

 

解决方案：

  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, &＃39;yyyy-mm-dd&＃39;) 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 (  &＃39;12475014246300079&＃39;, &＃39;12475014246300099&＃39;);

 

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 |

|   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"=&＃39;12475014246300079&＃39; OR

              "A"."SERIAL_NUM"=&＃39;12475014246300099&＃39;)

   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