procedure execution time

[helpme]

hi,

I have this 1 procedure running on my db server...that looks up into 57 tables in the db and updates them...
each table has around 10 cols..and each is updated individually

I started this procedure 4 hrs age..its still running

when i try to connect to the db from diff machine i find this session still running and the update sql that it is iring then..
But is it normal that it takes sooo long for this?

tables contain max 4 lacs row..

thank you

[cvd123]

maybe you have very large tables, and have some locks in
them

[helpme]

Does opeing acursor lock the table the cursor works on?

[sambavan]

Opening a cursor for update would lock that row and not the table. Are you using the index cols, to find the record to be updated? If not try using it. If you are doing the update on the entire table, then using cursor is not the way, instead use update directly on the table, it would be faster.

You might also want to look into using bulk-bind option by using FORALL, more detail in the link

http://www.oradoc.com/ora816/appdev....lems.htm#20949

Hopefully one of these would fix the problem for you.

Sam