delete数据特别慢

1 业务系统人员反映,表数据不多，但删除数据很慢，让查询原因：  delete from USER_A.s_tab_A  where mp_scheme_id in ('2020002495943225',                         '2020002495956025',                         '2020002496058954',                         '2020002495874263',                         '2020002495902717');                          进行查询，不到一秒数据就出来了，但进行删除，十几分钟都不出结果。查询死锁及阻塞会话，未发现有任何异常。 按道理查询的快，删除的也快，但什么会发生此问题呢？   15:54:10 SYS@host1>select * from USER_A.s_tab_A 15:54:11   2   where mp_scheme_id in ('2020002495943225', 15:54:11   3                          '2020002495956025', 15:54:11   4                          '2020002496058954', 15:54:11   5                          '2020002495874263', 15:54:11   6                          '2020002495902717');     ABR_ID MP_SCHEME_ID      BR_ID    CONS_ID RELA_SOR RELA_MP_ID RELA_RATIO MP_DIREC CHG_DESC ---------- ------------ ---------- ---------- -------- ---------- ---------- -------- -------- 2.0200E+15   2.0200E+15 2.0200E+15 2.0190E+15 06       2.0200E+15          1 01       01 2.0200E+15   2.0200E+15 2.0200E+15 2.0190E+15 06       2.0200E+15          1 01       01 2.0200E+15   2.0200E+15 2.0200E+15 2.0190E+15 06       2.0200E+15          1 01       01 2.0200E+15   2.0200E+15 2.0200E+15 2.0190E+15 06       2.0200E+15          1 01       01 2.0200E+15   2.0200E+15 2.0200E+15 2.0190E+15 06       2.0200E+15          1 01       01 5 rows selected.                          2 执行delete from USER_A.s_tab_A where mp_scheme_id in ('2020002495943225') 操作， 对相关会话进行追踪，发现大量的db file scattered read等待事件，此等待事件一般都是全表读引起的。 对执行的SQL（delete from USER_A.s_tab_A where mp_scheme_id in ('2020002495943225')）进行追踪， 发现一直读取数据文件179、132、382 数据文件 WAIT #3: nam='db file scattered read' ela= 15247 file#=179 block#=1528697 blocks=16 obj#=179385 tim=135845004021407 WAIT #3: nam='db file scattered read' ela= 16993 file#=179 block#=1529481 blocks=16 obj#=179385 tim=135845004039149 WAIT #3: nam='db file scattered read' ela= 18058 file#=179 block#=1529497 blocks=16 obj#=179385 tim=135845004057774 WAIT #3: nam='db file scattered read' ela= 19613 file#=179 block#=1529513 blocks=16 obj#=179385 tim=135845004077977 WAIT #3: nam='db file scattered read' ela= 12285 file#=179 block#=1529529 blocks=16 obj#=179385 tim=135845004090887 WAIT #3: nam='db file scattered read' ela= 43545 file#=179 block#=1529545 blocks=16 obj#=179385 tim=135845004135002 WAIT #3: nam='db file scattered read' ela= 7545 file#=179 block#=1529561 blocks=16 obj#=179385 tim=135845004143165 WAIT #3: nam='db file scattered read' ela= 58475 file#=179 block#=1529577 blocks=16 obj#=179385 tim=135845004202232 WAIT #3: nam='gc cr multi block request' ela= 418 file#=179 block#=1529608 class#=1 obj#=179385 tim=135845004203273 WAIT #3: nam='db file scattered read' ela= 712 file#=179 block#=1529593 blocks=16 obj#=179385 tim=135845004204039 WAIT #3: nam='gc cr multi block request' ela= 1094 file#=179 block#=1530392 class#=1 obj#=179385 tim=135845004205630  .............................................................. WAIT #3: nam='db file scattered read' ela= 2922 file#=132 block#=626297 blocks=16 obj#=179385 tim=135845132585126 WAIT #3: nam='db file scattered read' ela= 4952 file#=132 block#=626313 blocks=16 obj#=179385 tim=135845132590855 WAIT #3: nam='db file scattered read' ela= 2238 file#=132 block#=626329 blocks=16 obj#=179385 tim=135845132593786 WAIT #3: nam='db file scattered read' ela= 908 file#=132 block#=626345 blocks=16 obj#=179385 tim=135845132595494 WAIT #3: nam='db file scattered read' ela= 849 file#=132 block#=626361 blocks=16 obj#=179385 tim=135845132597081 WAIT #3: nam='db file scattered read' ela= 894 file#=132 block#=626377 blocks=16 obj#=179385 tim=135845132598794 WAIT #3: nam='db file scattered read' ela= 2285 file#=132 block#=626393 blocks=16 obj#=179385 tim=135845132601891 WAIT #3: nam='db file scattered read' ela= 770 file#=132 block#=626409 blocks=16 obj#=179385 tim=135845132603409 WAIT #3: nam='gc cr multi block request' ela= 314 file#=132 block#=626440 class#=1 obj#=179385 tim=135845132604522 WAIT #3: nam='db file scattered read' ela= 1147 file#=132 block#=626425 blocks=16 obj#=179385 tim=135845132605734 ............................................................... WAIT #3: nam='db file scattered read' ela= 653 file#=179 block#=2570493 blocks=12 obj#=179385 tim=135845175217366 WAIT #3: nam='db file scattered read' ela= 8824 file#=382 block#=2193 blocks=16 obj#=179385 tim=135845175226942 WAIT #3: nam='db file scattered read' ela= 2950 file#=382 block#=2209 blocks=16 obj#=179385 tim=135845175230573 WAIT #3: nam='db file scattered read' ela= 3931 file#=382 block#=2225 blocks=16 obj#=179385 tim=135845175235311 WAIT #3: nam='db file scattered read' ela= 6720 file#=382 block#=2241 blocks=16 obj#=179385 tim=135845175242731 WAIT #3: nam='db file scattered read' ela= 4565 file#=382 block#=2257 blocks=16 obj#=179385 tim=135845175248132 WAIT #3: nam='db file scattered read' ela= 5295 file#=382 block#=2273 blocks=16 obj#=179385 tim=135845175254098 WAIT #3: nam='gc cr multi block request' ela= 185 file#=382 block#=2304 class#=1 obj#=179385 tim=135845175254949 WAIT #3: nam='db file scattered read' ela= 7879 file#=382 block#=2289 blocks=16 obj#=179385 tim=135845175262876 3 使用TKPROF 格式化生成的trace，发现竟然有s_tab_B,且是全表扫描，根据消耗的时间， 明显可以看出主要消耗在s_tab_B表上。 delete from USER_A.s_tab_A where mp_scheme_id in ('2020002495943225') call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1     29.52     263.24     685545     688410         13           1 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2     29.52     263.24     685545     688410         13           1   select /*+ all_rows */ count(1)  from "USER_A"."s_tab_B" where "ABR_ID" = :1 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1     29.52     263.23     685545     688407          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        3     29.52     263.23     685545     688407          0           1 Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   gc cr multi block request                   50846        0.16         28.68   db file scattered read                      43006        0.15        197.34   latch: KCL gc element parent latch             23        0.00          0.00   gc cr grant 2-way                              81        0.00          0.04   db file sequential read                       108        0.02          0.25   db file parallel read                         323        0.16          4.44   gc cr block 2-way                              33        0.00          0.02   gc current grant 2-way                          8        0.00          0.00   gc current block 2-way                          6        0.00          0.00   latch: object queue header operation            1        0.00          0.00   latch free                                      1        0.00          0.00   gc cr block 3-way                              81        0.01          0.09   gc current block 3-way                          7        0.00          0.00   gc current grant busy                           1        0.00          0.00     2  user  SQL statements in session.     1  internal SQL statements in session.     3  SQL statements in session.      4 查看相关表数据 SYS@host1>select count(*) from USER_A.s_tab_A;   COUNT(*) ----------     318760 1 row selected. SYS@host1>select count(*) from "USER_A"."s_tab_B";   COUNT(*) ----------   20185296 1 row selected. 5 根据如下语句查看表的索引 SYS@host1>select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME f rom dba_constraints where TABLE_NAME=upper('s_tab_A'); OWNER                          CONSTRAINT_NAME                C TABLE_NAME ------------------------------ ------------------------------ - ------------------------------ USER_A                          SYS_C0069316                   C s_tab_A USER_A                          SYS_C0069315                   C s_tab_A USER_A                          SYS_C0069314                   C s_tab_A USER_A                          SYS_C0069313                   C s_tab_A USER_A                          SYS_C0069312                   C s_tab_A USER_A                          PK_s_tab_A                     P s_tab_A USER_A                          FK_tab_B                       R s_tab_A 7 rows selected. 查看创建外键约束的语句 alter table s_tab_A   add constraint FK_tab_B foreign key (MP_SCHEME_ID)   references s_tab_B (MP_SCHEME_ID)   disable;       SYS@host1>select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,AVG_SPACE,SAMPLE_SIZE,LAST_ANALYZED  from dba_tab_statistics where OWNER='USER_A' and TABLE_NAME in ('s_tab_A','s_tab_B'); OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE SAMPLE_SIZE LAST_ANAL ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- USER_A                          s_tab_A                           318715       2684       1718      318715 27-MAY-20 USER_A                          s_tab_B                         20055160     684122       1101     1002758 28-APR-20 2 rows selected. 根据如上信息，可以确定在删除2020002495943225这一行数据时，由于有外键，且对表s_tab_B进行了全表扫描， 数据量有2千多万,根据查询语句，发现s_tab_B表未走外键索引， 最终导致删除比较慢，建议开发优化s_tab_B表的外键索引，避免全表扫描。