oracle - 突然删除需要很长时间

Question

我们有一个全年每天都在运行的饲料流程。作为其中的一部分，我们每天从表中删除每一行(大约 100 万行)，使用 5 个不同的存储过程重新填充它，然后提交事务。这是我们调用的唯一提交语句。突然之间，删除已开始，大约需要 2 小时才能完成。删除也很简单(delete from T_PROFILE_WORK)这在过去一年中运行良好，但在过去一周我注意到了这个问题。

非常感谢对此的任何帮助

Answer 1

请查看此 SO 问题的答案:"oracle delete query taking too much time" :

1. You could be blocked by another session (most likely). Before you delete you should make sure noone else is locking the rows, eg: issue SELECT NULL FROM tablename WHERE colname=:value FOR UPDATE NOWAIT,
2. There could be a ON DELETE TRIGGER that does additional work,
3. Check for UNINDEXED REFERENCE CONSTRAINTS pointing to this table (there is a script from AskTom that will help you determine if such unindexed foreign keys exist).

我会先检查#2 和#3，它们最容易诊断。