Delete script runs forever 3

[amberlynn]

I'm trying to delete a single record from a table.

The SQL I'm using is:
DELETE FROM FOREST.CODE_LOOKUP
WHERE COLU_LOOKUP_TYPE = 'ACTS' AND COLU_LOOKUP_ID = 'S';

I've confirmed that this record exists, and there is only one that contains these two values.

But when I execute the script, it just runs and runs until I cancel it.

I'm not sure what I'm doing wrong here...

Thanks,
Amber

 

[SantaMufasa]

AmberLynn,

There could be several contributory factors that cause the statement to run slowly:[ul][li]The row(s) you wish to DELETE are locked by another process. The best way to prevent a "hang" from happening due to another process locking a row you wish to DELETE or UPDATE is to issue this command just prior to the DELETE or UPDATE:

SQL> select NULL from <table_name> where <condition> for update nowait;
select NULL from <table_name> where <condition> for update nowait;
                 *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

This tells you immediately that another process has locked the rows you wish to affect, and you don't waste any time waiting for the lock to release.[/li][li]There are many rows in the table and:[/li][ul][li]Indexes do not exist on columns COLU_LOOKUP_TYPE and COLU_LOOKUP_ID[/li][li]Statistics have not been gathered subsequent to large changes in the content/number of rows in the table.[/li][/ul][/ul]These are the culprits that come to mind initially. What are your thoughts on these issues?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[amberlynn]

Thanks Dave,

I'm a bit unclear on the SQL statement you added - are you suggesting I change my code to:

select NULL from CODE_LOOKUP where COLU_LOOKUP_TYPE = 'ACTS' AND COLU_LOOKUP_ID = 'S' for update nowait;
DELETE FROM FOREST.CODE_LOOKUP
WHERE COLU_LOOKUP_TYPE = 'ACTS' AND COLU_LOOKUP_ID = 'S';
??

There are only 1690 rows in this table, but these two fields (COLU_LOOKUP_TYPE and COLU_LOOKUP_ID are both indexed, and together form the primary key for this table.

Amber

 

[SantaMufasa]

I'm a bit unclear on the SQL statement you added - are you suggesting I change my code to...

Yes, nearly, and depending upon your programming environment. If the results of the "SELECT...for update nowait" are successful, you want to do the "DELETE...". If not successful, you want to issue an error message to the application that the rows to be DELETEd are currently locked by another user.


My curiosity would lead me to do the SELECT from SQL*Plus, and see if anyone else has locked the rows destined for DELETE. If they are not locked, then issue the DELETE statement and see how long the DELETE takes.

What programming/application-execution environment is the code running under (SQL*Plus, PL/SQL, Crystal Reports, et cetera)?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[amberlynn]

If I execute "select NULL from CODE_LOOKUP where COLU_LOOKUP_TYPE = 'ACTS' AND COLU_LOOKUP_ID = 'S' for update nowait;" I don't get an error - I get one blank row returned.

When I run the Delete, it just runs until I cancel it, without ever completing...

I'm using TOAD to run my SQL.

Cheers,
Amber

 

[Turkbear]

Hi,
maybe try a DELETE FROM sql type delete:

Delete from (select * from CODE_LOOKUP where COLU_LOOKUP_TYPE = 'ACTS' AND COLU_LOOKUP_ID = 'S' )

This would remove all rows that match that condition ( in your case it should only remove 1)

Not sure if it will work any better but maybe it will invoke the Index better.

( Have you tried an Explain Plan for your original delete statement?)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Turkbear,

I've never seen before the construct where you "DELETE FROM (<some query>)"...How does that work?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
From PSOUG..

http://www.psoug.org/reference/delete.html

Maybe just 11.1 ....





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dagon]

Since you have TOAD, it may also be worth monitoring what is happening to the delete using the "Monitor Session" screen. This will show if it is waiting for a lock (or some other resource). You should also be able to monitor long operations like full table scans and see the progress of them.

 

[Dagon]

Also make sure you're not inadvertently locking yourself in TOAD out by running two uncomitted DML operations in different sessions. Even with just a single TOAD window, you can still lock yourself out if you run one update using the "execute single command" key and then another statement using the "execute script" key. The "execute script" runs under a different session and can be locked out by the uncommitted transaction from the "execute single command".

 

[amberlynn]

Wow - thanks for all the feedback!

I tried the "Delete from (select * from CODE_LOOKUP where COLU_LOOKUP_TYPE = 'ACTS' AND COLU_LOOKUP_ID = 'S' )" - same result - just continued to run until I cancelled it.

I'm not familiar with Explain Plan - but did try it out. I've attached a print screen of the results.

I'm wondering if the last comment - by Dagon may be my issue...

Thanks!
Amber

 

[amberlynn]

Thanks everyone! The issue seemed to be having 2 windows in Toad running two uncommitted DML operations.
I closed down everything, committed any outstanding scripts, and restarted - the SQL command ran no problem.

Thanks again.

Amber

 

[SantaMufasa]

...And I believe Turkbear deserves a

for his filling in a knowledge gap for me with the "DELECT FROM <query>;" syntax. Good onya, Turkbear!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

...And it's certainly not "DELECT...". Perhaps it's getting too close to lunchtime and my subconscious it thinking about something DELECTIBLE to eat. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
Thanks Santa, the star was delectible!





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[KenCunningham]

I think we're going to have take this delectible/delectable thing out to MAI!

Some days are diamonds, some days are rocks - make sure most are the former.

 

[Beilstwh]

Just another reason to use sqlplus and not a gui.

Bill
Lead Application Developer
New York State, USA

 

[sem]

Another possible reason of slow delete may be some foreign key constraint, especially with non-indexed key. This is not for topicstarter but maybe for anybody else.

Dave, you may not only delete but also update [key-preserved] queries. This is especially usefull on queries joining master-detal

update (select p.id, p.cnt,  count(c.id) clc
  from parent p, child c 
  where p.id=c.parent_id) 
set cnt=clc

instead of

update parent p
set cnt=(select count(c.id)
  from  child c 
  where p.id=c.parent_id)

In some case Oracle optimizes joining query better.

Regards, Dima

http://seminihin.narod.ru