[oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 26 Jan 2004 19:35:01 -0000
Message-ID: <001201c3e443$aeec0130$6702a8c0@Primary>

 The pl/sql you've sent has become almost unreadable in transit, but it seems to me that it would help if you put the LOOP above the FETCH. Your code looks as if it does one FETCH
from the cursor, then goes into an infinite loop doing the same no-row-update update committing every thousand attempts.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person   who can answer the questions, but the   person who can question the answers -- T. Schick Jr

Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG - v$ and x$  March 2004 Hotsos Symposium - The Burden of Proof  March 2004 Charlotte NC OUG - CBO Tutorial  April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "Barbara Baker" <barbarabbaker_at_yahoo.com> To: <oracle-l_at_freelists.org> Sent: Monday, January 26, 2004 6:46 PM Subject: [oracle-l] PL/Sql Update Table runs 38 hrs (so far)

Hi, list.
Solaris 9
Oracle 9.2.0.4

I have been trying for several days to update a field in one table (pub) from a field in another table (pub_14)
The table I'm updating FROM (pub_14) has about 500,000 rows in it.
The table I'm updating (pub) has about 18,000,000 rows in it.

I'm on about my 5th attempt. The current version has been running for 38 hours. So far I believe I've managed to update about 500 records.

The tables originally were identical in structure, but 1 came from another database. To eliminate link issues, I created a new table (pub_14) with just the 5 fields I need.

Both tables have an index on these 3 columns (adno, pubno, vno). I've analyzed both tables. The cost is lower with the hints I've provided, but I don't really think it makes any difference.

I turned on 10046 level 12 for the current process (38 hour one). In just a few minutes of tracing, I see bunches of executes, but no updates.

Any ideas?
Here's my update pl/sql, tkprof from the 10046 trace, and a sample of one of the sets of adno's that need to be updated.

pacer:ent9i> more update_pub_from_mdate.sql

set serveroutput on size 1000000

DECLARE
  CURSOR pub14_cur IS

    SELECT pub14.adno,
           pub14.pubno,
           pub14.vno,
           pub14.vnoflag,
           pub14.mdate
      FROM advdb.pub_14 pub14;

  pub14_rec pub14_cur%ROWTYPE;

  v_insert NUMBER(9,0) := 0;

BEGIN
  OPEN pub14_cur;
  FETCH pub14_cur INTO pub14_rec;
  LOOP
    EXIT WHEN pub14_cur%NOTFOUND;
    UPDATE

       /*+ index(pub17 I_PUB1)    
       use_hash (pub14 pub17) */
advdb.pub pub17
       SET pub17.pub_sysdate = pub14_rec.mdate
     WHERE pub17.adno     = pub14_rec.adno
       AND pub17.pubno    = pub14_rec.pubno
       AND pub17.vno      = pub14_rec.vno

AND pub17.pub_sysdate <> pub14_rec.mdate;

    v_insert := v_insert + 1;
    IF MOD(v_insert,1000) = 0

       then COMMIT;
End IF;
  END LOOP;
  COMMIT;
  CLOSE pub14_cur;
  DBMS_OUTPUT.PUT_LINE (v_insert||' records were inserted.');

END; / Received on Mon Jan 26 2004 - 13:35:01 CST