Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: update taking too long on 174,000+ records
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 Saira Somani wrote:Received on Mon May 05 2003 - 13:29:49 CDT
>List Gurus,
>
>SQL> desc TEST65D.PDA_ORDER_HEADER;
> Name Null? Type
> ----------------------------------------- -------- ---------
> ID NUMBER(10)
> USER_CODE CHAR(30)
> CUST_NUM CHAR(10)
> SORT_NAME CHAR(20)
> DATE_STAMP DATE
> STAT CHAR(1)
>
>I wanted to update the STAT field to ' ' in PDA_ORDER_HEADER table under
>the TEST65D schema so records are available for testing.
>
>Usually it doesn't take this long. Previously, I had changed the STAT
>from 'Y' to 'N' in less than 5 minutes with an update and commit
>statement for 174,000+ records. Now the statement runs for over 15
>minutes and I haven't yet had success.
>
>update pda_order_header set stat=' ' where stat='N'
>
>Then I tried to filter it by customer and date but no success - again
>the query ran for way too long:
>
>update pda_order_header set stat=' ' where cust_num='2' and date_stamp
>between '02-JAN-03' and '31-JAN-03'
>
>This is also the case when I try to update one record using a unique ID
>in the where clause like this:
>
>update pda_order_header set stat=' ' where id=37267
>
>V$SESSION shows that the session is active but even after 15 minutes,
>there is no result.
>
>I have since killed the sessions from SQLPLUS using:
>
>alter system kill session 'pid,serial'
>
>What is going on?
>Thanks for your help.
>
>Saira
>
>
>
-- 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).