在Oracle中Delete语句非常慢 [英] Delete statement was very slow in Oracle

问题描述

我有一个约有10万条记录的表，我想删除一些行，问题是DELETE语句的运行速度非常慢-它在30分钟内未完成.但是select语句在1秒内返回.

I have a table with about 100k records and I want to delete some rows, The problem is that the DELETE statement is running very slowly - it didn't finish in 30 minutes. But the select statement was return in 1 second.

SELECT语句如下:

select * from daily_au_by_service_summary 
    where summary_ts >= to_date('09-04-2012','dd-mm-yyyy') 
    order by summary_ts desc;

和DELETE语句如下:

delete from daily_au_by_service_summary 
    where summary_ts > to_date('09-04-2012','dd-mm-yyyy');

此表在summary_ts处具有唯一索引.

This table have the only index at summary_ts.

可能是什么原因?

该表已被许多会话锁定:

the table was locked by many sessions:

SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   OBJECT OWNER                   OBJECT_NAME                                                                                                                      OBJECT_TYPE         LOCKED_MODE
---------- ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- -----------
       213 T03RPT                         elou                           T03RPT                         DAILY_AU_BY_SERVICE_SUMMARY                                                                                                      TABLE                         3 
       203 T03RPT                         elou                           T03RPT                         DAILY_AU_BY_SERVICE_SUMMARY                                                                                                      TABLE                         3 
       202 T03RPT                         elou                           T03RPT                         DAILY_AU_BY_SERVICE_SUMMARY                                                                                                      TABLE                         3 
       190 T03RPT                         elou                           T03RPT                         DAILY_AU_BY_SERVICE_SUMMARY                                                                                                      TABLE                         3 
       189 T03RPT                         elou                           T03RPT                         DAILY_AU_BY_SERVICE_SUMMARY                                                                                                      TABLE                         3 
       188 T03RPT                         elou                           T03RPT                         DAILY_AU_BY_SERVICE_SUMMARY                                                                                                      TABLE                         3 
       187 T03RPT                         elou                           T03RPT                         DAILY_AU_BY_SERVICE_SUMMARY         

如何终止这些会话?

我取消了锁定表的会话后，问题已解决，感谢大家的帮助. –

The problem had been resolved after I killed the sessions which locks the table, thanks all for the help. –

推荐答案

可能有很多原因:

• 服务器负载(不太可能是因为SELECT快速)
• 触发器(请参见如何为表格列出它们).
li>
• 外键(>外键列表及其表参考)
• 每行中有很多数据(LOB，很多列).
• 有人将您要删除的表(或整个表)中的行锁定. 请参阅此博客发布如何列出锁. 此讨论可能也有帮助.

• Server load (unlikely because the SELECT is fast)
• Triggers (see here how to list them for a table).
• Foreign keys (List of foreign keys and the tables they reference)
• A lot of data in each row (LOBs, many columns).
• Someone is locking rows in the table that you'd like to delete (or the whole table). See this blog post how to list locks. This discussion might also help.

如果外键是问题，通常的解决方案是在外列上添加索引:对于每个删除，Oracle需要检查这是否违反外键关系.

If the foreign keys are the problem, the usual solution is to add indexes on the foreign column: For each delete, Oracle needs to check whether this would violate a foreign key relation.

这篇关于在Oracle中Delete语句非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！