一次delete速度异常慢的处理过程

转自：http://space.itpub.net/10710960/viewspace-610982

一次小数据量删除，但花费2个小时还没完成的问题

delete from TOPBOX_COURSEWARE where id like '760%';
花费非常长的时间，topbox_courseware表大概2w数据，要删除的也就2500条数据。
问题原因：
由于TOPBOX_COURSEWARE表与多个表有外键关联，而且关联的表中有2张千万级别的大表。
通过v$session_wait,v$session表

select * from v$session_wait a,v$session b
where b.sid=a.sid
and a.event not like 'SQL*Net%';

发现该session的event是db file scattered read。
这个事件一般是表示法伤了全表扫描相关的等待。通常意味着全表扫描过多，或者I/O能力不足，或是I/O争用造成的。

解决方法：
1.通过dba_constraints表找到topbox_courseware表对应的约束
select * from dba_constraints where constraint_type='R' and wner='TOPBOX' and r_constraint_name='PK_TOPBOX_COURSEWARE'
得到两个外键约束名
FK_TOPBOX_COURSCO_REF_COUR
FK_TOPBOX_CSTUDY_REF_COUSE

2.通过命令将这2个约束disable
alter table topbox_coursescore disable constraint fk_topbox_coursco_ref_cour;
alter table topbox_coursestudy disable constraint fk_topbox_cstudy_ref_couse;

通过上面的处理delete只需要不到1秒的时间

3.将约束重新激活
由于topbox_coursescore，topbox_coursestudy是千万级的大表，如果直接enable而不加其他参数，启用约束后，oracle会对表中数据
逐条检查，所以速度会非常慢。而且已经插入的数据没有脏数据，所以为了避免不必要的工作，就要使用novalidate
alter table topbox_coursescore enable novalidate constraint fk_topbox_coursco_ref_cour;
alter table topbox_coursestudy enable novalidate constraint fk_topbox_cstudy_ref_couse;