Delete on oracle taking long

samsuf2002 · Post by **samsuf2002** » Wed Feb 18, 2009 9:21 am

I am running a job with two Oracle stages where it extracts the Key of table A and in second stage deletes some record from table A based on some conditions.

In the target I am using user define query for delete rows. like

DELETE FROM TABLE A
where KEY in (........

The job starts fine and it even delete those records which I can see on Database side but even after deleting the jobs doesn't finish, it shows running status for hours.

I am not sure what's going on DB side. Are there any other way to achieve this ?

Any help will be appreciated.

mekrreddy · Post by **mekrreddy** » Wed Feb 18, 2009 9:30 am

Let me know if the Oracle database is on same server, if not whats the database version and the client connector version.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Feb 18, 2009 9:35 am

Check for locks in oracle. Try running on single node config.

samsuf2002 · Post by **samsuf2002** » Wed Feb 18, 2009 11:45 am

Datastage and oracle are on same server.

I am not sure but i tried using nolock in my query but it dint work.

mekrreddy · Post by **mekrreddy** » Wed Feb 18, 2009 12:48 pm

Try running job by constructing new Oracle stage without any parameters.

DSguru2B · Post by **DSguru2B** » Wed Feb 18, 2009 2:11 pm

Sainath.Srinivasan wrote:Try running on single node config.

Did you do that?

samsuf2002 · Post by **samsuf2002** » Wed Feb 18, 2009 2:59 pm

Yes I tried running with single node but still the same issue.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 18, 2009 3:42 pm

Get your DBA to monitor the session. In my experience DELETE does take a very long time, particularly when there are referential integrity constraints involved.

chulett · Post by **chulett** » Wed Feb 18, 2009 3:49 pm

But the fact that the deletes 'can be seen' on the database side, which I take to mean they can be seen to be gone, would imply they have been committed and thus the Oracle part is done. Well... unless intermediate commits are being done and they only checked some.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Feb 19, 2009 3:44 am

How did you find it is the 'delete' stage that takes time?

How many rows matching the delete criteria are available in the target?

What if you do a delete outside DS? How long does it take?

Did you set commit intervals and see the count go down?

Did you get the plan for delete SQL?

priyadarshikunal · Post by **priyadarshikunal** » Fri Feb 20, 2009 9:39 am

In addition to that

If you are sure database is taking time then
Check the indexes on columns used in where condition. I believe Its indexed as you termed it as KEY.

Analyze the table and indexes. If references of that table are present in the database analyse all referneced tables and indexes.

Check if cascade delete is enabled on that table generally it should not.

Ask your DBA to monitor the session to find any bottlenecks.

choudary_sivaji · Post by **choudary_sivaji** » Fri Feb 20, 2009 5:33 pm

Hi,
Are you selecting and deleting from same table?
if that is the case then it will lock.

regards
sivaji

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Feb 23, 2009 4:04 am

Selecting and deleting from same table will not cause locks unless you lock with 'FOR UPDATE'.

It may result in blowing your temp, rollback or redo spaces (depending on version and action in Oracle).