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

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


Saira Somani wrote:



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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Saira Somani
  INET: saira_somani_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).