删除时间很长很突然 [英] Delete suddenly taking a long time

问题描述

我们有一个Feed流程，一年中的每一天都在运行. 作为其一部分，我们每天删除表中的每一行(大约一百万行)，使用5种不同的存储过程重新填充它，然后提交事务. 这是我们唯一调用的commit语句. 突然删除操作开始takign大约需要2个小时才能完成. 删除也非常简单(从T_PROFILE_WORK删除) 在过去的一年中，此方法运行良好，但是在过去的一周中，我注意到了这个问题.

We have a feed process which runs every day of the year. As part of that we delete every row from a table (approx 1 million rows) every day, repopulate it using 5 different stored procedures and then commit the transaction. This is the only commit statement that we call. All of a sudden the delete has started takign about 2 hours to complete. The delete is also very simple (delete from T_PROFILE_WORK) This has worked perfectly well for the past year, but in the past week I have noticed this issue.

对此的任何帮助将不胜感激

Any help on this is greatly appreciated

推荐答案

请查看以下SO问题的答案::

please review the answers of this SO Question: "oracle delete query taking too much time":

1. 您可能被另一个会话阻止(最有可能).在你之前 删除您应该确保没有其他人 正在锁定行，例如:issue SELECT 表名WHERE中为NULL colname =:value FOR UPDATE NOWAIT，
2. 可能有一个ON DELETE TRIGGER可以做其他工作，
3. 检查指向此表的UNINDEXED参考约束 (有一个来自AskTom的脚本 将帮助您确定是否 存在未索引的外键.

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，它们最容易诊断.

I would check #2 and #3 first, they are the easiest to diagnose.

这篇关于删除时间很长很突然的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！