表上索引过多，引发db file sequential read 等待事件

问题描述：用户BM0658告诉我说下面这个delete语句运行很长一段时间(4个多小时还在跑)

delete from ADWGU.SHPMT_CURR_FCT_NEW where LOAD_REGN_ABBR_NAME in ('NALA','APAC');

于是查询 会话

SQL> select sid,username,osuser,sql_id,sql_child_number from v$session where username='BM0658' and osuser='luobi';

 

       SID USERNAME                       OSUSER                         SQL_ID        SQL_CHILD_NUMBER

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

      4530 BM0658                         luobi                                        

      4735 BM0658                         luobi                          g69rr33fyxgfw                0

      4803 BM0658                         luobi                          6p7qw296ajga7                0

 

SQL> select * from table(dbms_xplan.display_cursor('g69rr33fyxgfw',0));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  g69rr33fyxgfw, child number 0

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

delete from ADWGU.SHPMT_CURR_FCT_NEW where LOAD_REGN_ABBR_NAME in ('NALA','APAC'

Plan hash value: 2407586203

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

| Id  | Operation               | Name               | Rows  | Bytes | Cost (%CP

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

|   0 | DELETE STATEMENT        |                    |       |       | 52353 (10

|   1 |  DELETE                 | SHPMT_CURR_FCT_NEW |       |       |

|   2 |   PARTITION RANGE ALL   |                    |    62M|  3353M| 52353  (1

|   3 |    PARTITION LIST INLIST|                    |    62M|  3353M| 52353  (1

|   4 |     TABLE ACCESS FULL   | SHPMT_CURR_FCT_NEW |    62M|  3353M| 52353  (1

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

 

16 rows selected

从执行计划中可以看出这个表是分区表，delete操作对表做了全表扫描，于是查看当前等待事件，连续查看多次

从当前等待事件中，我看到db file sequential read居多(怀疑delete时候没有禁止索引)，另外还出现了一个latch竞争。全表扫描竟然会引起

db file sequential read 等待事件，有点反常，猜测索引过多

 

SQL> select event,p1,p2,p3 from v$session where sid=4735;

 

EVENT                                                                    P1         P2         P3

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

db file sequential read                                                 249     190176          1

SQL> select event,p1,p2,p3 from v$session where sid=4735;

 

EVENT                                                                    P1         P2         P3

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

latch: cache buffers lru chain                                   1.38350580        117          0

SQL> select event,p1,p2,p3 from v$session where sid=4735;

 

EVENT                                                                    P1         P2         P3

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

db file sequential read                                                  39     178963          1

SQL> select event,p1,p2,p3 from v$session where sid=4735;

 

EVENT                                                                    P1         P2         P3

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

db file sequential read                                                  39     179011          1

SQL> select event,p1,p2,p3 from v$session where sid=4735;

 

EVENT                                                                    P1         P2         P3

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

db file sequential read                                                  85     186128          1

SQL> select event,p1,p2,p3 from v$session where sid=4735;

 

EVENT                                                                    P1         P2         P3

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

db file sequential read                                                 467    1603930          1

SQL> select event,p1,p2,p3 from v$session where sid=4735;

 

EVENT                                                                    P1         P2         P3

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

db file sequential read                                                  23    1503552          1

SQL> select event,p1,p2,p3 from v$session where sid=4735;

 

EVENT                                                                    P1         P2         P3

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

db file sequential read                                                 251    2283082          1

 

2270000

 

首先看ADWGU.SHPMT_CURR_FCT_NEW是什么类型

SQL> select owner,object_name,object_type from dba_objects where owner='ADWGU' and object_name='SHPMT_CURR_FCT_NEW';

 

OWNER                          OBJECT_NAME                                                                      OBJECT_TYPE

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

ADWGU                          SHPMT_CURR_FCT_NEW                                                               SYNONYM

查出它是同义词，现在查看它是哪个表的同义词

SQL> select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name='SHPMT_CURR_FCT_NEW' and owner='ADWGU';

 

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME

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

ADWGU                          SHPMT_CURR_FCT_NEW             ADWU                           SHPMT_CURR_FCT_NEW

这里可以看到它是ADWU.SHPMT_CURR_FCT_NEW的同义词

查询表上的索引

SQL> select index_owner,index_name,table_owner,column_name,column_position from dba_ind_columns where table_name='SHPMT_CURR_FCT_NEW' and table_owner='ADWU';

 

INDEX_OWNE INDEX_NAME                     TABLE_OWNER                    COLUMN_NAME                    COLUMN_POSITION

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

ADWU       SHPMT_CURR_FCT_NEW_BX20        ADWU                           PROD_CSU_TYPE_CODE                           1

ADWU       SHPMT_CURR_FCT_NEW_BX10        ADWU                           SHPMT_FINAL_CLASS_IND_SKID                   1

ADWU       SHPMT_CURR_FCT_NEW_BX1         ADWU                           DAY_SKID                                     1

ADWU       SHPMT_CURR_FCT_NEW_NX8         ADWU                           SHPMT_FACT_SKID                              1

ADWU       SHPMT_CURR_FCT_NEW_BX14        ADWU                           PROFT_CTR_SKID                               1

ADWU       SHPMT_CURR_FCT_NEW_BX5         ADWU                           GEO_SKID                                     1

ADWU       SHPMT_CURR_FCT_NEW_BX2         ADWU                           CUST_SHIPT_SKID                              1

ADWU       SHPMT_CURR_FCT_NEW_BX11        ADWU                           PROD_SOLD_SKID                               1

ADWU       SHPMT_CURR_FCT_NEW_BX4         ADWU                           TRADE_CHANL_SKID                             1

ADWU       SHPMT_CURR_FCT_NEW_BX15        ADWU                           CRNCY_SKID                                   1

ADWU       SHPMT_CURR_FCT_NEW_BX21        ADWU                           DAY_SKID                                     2

ADWU       SHPMT_CURR_FCT_NEW_BX21        ADWU                           LOAD_REGN_ABBR_NAME                          1

 

12 rows selected

 

查询表上面的索引类型

SQL>cselect owner,index_name,index_type,status from dba_indexes where table_owner='ADWU' and table_name='SHPMT_CURR_FCT_NEW';

 

OWNER                          INDEX_NAME                     INDEX_TYPE                  STATUS

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

ADWU                           SHPMT_CURR_FCT_NEW_BX20        BITMAP                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_BX10        BITMAP                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_BX1         BITMAP                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_NX8         NORMAL                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_BX14        BITMAP                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_BX5         BITMAP                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_BX2         BITMAP                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_BX11        BITMAP                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_BX4         BITMAP                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_BX15        BITMAP                      N/A

ADWU                           SHPMT_CURR_FCT_NEW_BX21        BITMAP                      N/A

 

11 rows selected

 

N/A表示 这个索引是分区表上的索引

 

解决方案：先kill session，然后 disable 所有索引

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX21  unusable;

alter index ADWU.SHPMT_CURR_FCT_NEW_BX20 unusable;

 

重新运行SQL语句

 

最后rebuild index parallel 8 nologging

注意，本案例发生在数据仓库，非OLTP环境