Delete statement was very slow in Oracle

Question

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.

The SELECT statement is as follows:

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

and DELETE statement is as follows:

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

This table have the only index at summary_ts.

What could be the reason?

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

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

How did you determine that it did not finish? Maybe it finished but was not commited and you do not see deletion took place from another connection until it was commited. — Fabian Barney, Apr 11 '12 at 07:25
I ran it in the sqlplus console, it just didn't return. so I think the statement is still under executing. — Elvis Lou, Apr 11 '12 at 07:28
Killing sessions is a completely different topic. Please ask a new question (so other people can more easily find that solution). Then, add a link from this question to the new one. — Aaron Digulla, Apr 11 '12 at 08:00
The problem had been resolved after I killed the sessions which locks the table, thanks all for the help. — Elvis Lou, Apr 11 '12 at 08:08

score 10 · Accepted Answer · edited May 23 '17 at 10:29

10

There can be many reasons:

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.

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.

edited May 23 '17 at 10:29

Community

1
1

answered Apr 11 '12 at 07:26

Aaron Digulla

321,842
108
597
820

How to check if the rows is locked by anyone? – Elvis Lou Apr 11 '12 at 07:35
so the table was locked by many sessions. see the edited question. How to kill the sessions? – Elvis Lou Apr 11 '12 at 07:52
1

Please ask a new question for that. – Aaron Digulla Apr 11 '12 at 07:59

score 6 · Answer 2 · answered Apr 11 '12 at 07:31

To delete means to change the table's content. And this means, that after each deleted row all indexes must be updated and all foreign-key references must be checked. This can take a very long time!

Maybe this helps:

Make a copy of that table without any references, triggers and additional indexes. Then do this:

insert into new_table (field1, field2, ...) values (
    select field1, field2, ...
    from daily_au_by_service_summary 
    where summary_ts < to_date('09-04-2012','dd-mm-yyyy') 
);

If the fields in the tabels are defined in identical order, this might work too:

insert into new_table values (
    select *
    from daily_au_by_service_summary 
    where summary_ts < to_date('09-04-2012','dd-mm-yyyy') 
);

After that:

truncate daily_au_by_service_summary

and then:

insert into daily_au_by_service_summary (field1, field2, ...) values (
    select field1, field2, ...
    from new_table; 
);

New Table is not needed any longer:

drop new_table;

Thanks Hubert. I want to figure out why the DELETE statement is so slowly since there is only about 100k records in the table. — Elvis Lou, Apr 11 '12 at 07:37

score 1 · Answer 3 · answered Apr 11 '12 at 07:20

1

Obviously, a delete operation will take longer than a select, but that doesn't account for the difference you see.

It sounds like additional code is being run upon the delete, which indecates there may be triggers on the table that are also running. Can you check this?

answered Apr 11 '12 at 07:20

James Wiseman

29,946
17
95
158

there is not any triggers on the table, and not any REFERENCE CONSTRAINTS either. – Elvis Lou Apr 11 '12 at 07:30

score 0 · Answer 4 · answered Apr 11 '12 at 07:44

When DML operations take a long time, create new table with the remaining rows and drop the previous table, instead of deleting.

I mean,

create table NEW_TABLE as
select * from daily_au_by_service_summary  
where summary_ts <= to_date('09-04-2012','dd-mm-yyyy');

This will be faster, especially when you are deleting a substantial number of rows. (%10 of total rows, for example.)

Delete statement was very slow in Oracle

4 Answers4

Linked