Message-ID: <F001.00590070.20030505114758@fatcity.com>
Date: Mon, 05 May 2003 11:47:58 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Daniel W. Fink" <optimaldba@yahoo.com>
Subject: Re: update taking too long on 174,000+ records
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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@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@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).