同事给我反馈一个问题，删除某个表上的数据非常的慢。即使是通过主键删除，也是非常的慢，删除1条数据要1分钟。我测试删除了2000条，花了差不多12分钟。

我看了执行计划，和预期一样，走了索引，效率貌似很高。

看了一下这个表，发现存在很多外键关联，把外键全部删除，再测试数据删除，依然很慢。

通过v$session ,v$sqlarea 和 v$session_wait 找到该sql对应的event为“db file sequential read”，好像也正常。

怀疑是表空间碎片，执行如下语句

alter table xxx enable row movement;

alter table xxx shrink space;

select a.segment_name,a.bytes,a.blocks from user_segments a where segment_name = 'XXX';

前后变化不大，看来不是这问题。

后面，想到用trace方法跟踪delete事件，发现trace文件里面多了几个多余的select语句，应该都是系统自动产生的。根据这几个可疑的sql语句，发现原来是因为有几个表的外键引用了我的删除表，而这些外键上又没有创建索引，导致每次删除这个表的记录，都要去全表扫描那几个关联表。

于是，在那几个表的外键上新建索引。再次删除，测试效果，搞定。

附上trace脚本：

alter session set events='10046 trace name context forever,level 12';

执行要跟踪的SQL

alter session set events='10046 trace name context off';

完成后，到udump目录找到trace文件，拉到本地，使用命令转换

D:\>tkprof orcl_ora_21999.trc myoutput.txt

另一种跟踪方式，执行完sql用这个跟踪

exec dbms_system.set_sql_trace_in_session(SID, SERIAL# ,true);

exec dbms_system.set_sql_trace_in_session(SID, SERIAL# ,false);

ITPub博客

数据删除慢的原因排查