Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h45Kour27685 for ; Mon, 5 May 2003 15:50:56 -0500 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h45KouA27680 for ; Mon, 5 May 2003 15:50:56 -0500 Received: from fatcity.com (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id LAA82325; Mon, 5 May 2003 11:26:06 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0058FF24; Mon, 05 May 2003 11:08:19 -0800 Message-ID: Date: Mon, 05 May 2003 11:08:19 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Saira Somani" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Saira Somani" Subject: RE: update taking too long on 174,000+ records Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: saira_somani@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).