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表的外键索引，避免全表扫描。