问题描述：用户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环境
