Re: update taking too long on 174,000+ records

If you had updated 173,999 records when you killed the session, the transaction must be rolledback. This means that 173,999 changes must be undone and until these are all undone, the locks will not be released. That can take quite a while. IIRC, there may also be a delay between the time the session is killed and PMON is posted to begin cleaning up the killed transaction.

If the problem is alleviated when the scheduler is terminated, you have identified the players involved. Has there been a change to the scheduler? Why are you needing to change the data if the application meaning is the same (N, null or space)? It seems that it would be wise to suspend the scheduler while you do an update of the data outside of the application. Once the update is complete and committed, you restart the scheduler.

Saira Somani wrote:

>Our ERP system has a built-in scheduler and in the past, it has caused
>many problems with record/table locks. I believe this could be the
>problem because after the scheduler was terminated, the update went
>through. So there is some correlation - exactly what it is, I couldn't
>tell you.
>
>Stat is equivalent to STATUS of order - 'Y' it has been processed, 'N','
>', or null implies that it hasn't.
>
>In the past when this table has been updated, it has been within 2
>minutes.
>
>Can you explain this further?
>
>"Killing the sessions may be causing more problems than it solves if the
>
>locks held by the session are not cleared immediately. Use this method
>only as a last resort, have some patience."
>
>Thanks for your help,
>Saira
>
>
>-----Original Message-----
>Fink
>Sent: May 5, 2003 2:30 PM
>To: Multiple recipients of list ORACLE-L
>
>Is another session holding a lock on a record that your session wants to
>
>update? This would cause your session to wait until the other session's
>transaction terminates.
>Is the stat column a foreign key to another table? If so, is it missing
>an index?
>How quickly does a query with the same predicate take?
>
>Killing the sessions may be causing more problems than it solves if the
>locks held by the session are not cleared immediately. Use this method
>only as a last resort, have some patience.
>
>
>

-- 
Daniel W. Fink
http://www.optimaldba.com




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).