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

Doh!

Apologies to the list.
Thanks so much to Mike and Raj -- virtual beers for
you both!

(Unless either of you will be at RMOUG, in which case
the beer can be of the non-virtual nature.)

Thanks so much for looking at this

Barb

--- Mike Spalinger <Michael.Spalinger@xxxxxxx> wrote:
> Barbara,
> 
> Should your FETCH be inside your LOOP?
> 
> Mike
> 
> Barbara Baker wrote:
> > 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;
> > 
> > /
> > 
> > TKPROF: Release 9.2.0.4.0 - Production on Mon Jan
> 26
> > 11:03:01 2004
> > 
> > Copyright (c) 1982, 2002, Oracle Corporation.  All
> > rights reserved.
> > 
> > Trace file: ././ent9i_ora_25786.trc
> > Sort options: prsela  exeela  fchela  
> >
>
********************************************************************************
> > count    = number of times OCI procedure was
> executed
> > cpu      = cpu time in seconds executing 
> > elapsed  = elapsed time in seconds executing
> > disk     = number of physical reads of buffers
> from
> > disk
> > query    = number of buffers gotten for consistent
> > read
> > current  = number of buffers gotten in current
> mode
> > (usually for update)
> > rows     = number of rows processed by the fetch
> or
> > execute call
> >
>
********************************************************************************
> > 
> > UPDATE
> >        /*+ index(pub17 I_PUB1)
> >        use_hash (pub14 pub17) */
> >        advdb.pub pub17
> >        SET pub17.pub_sysdate = :b1
> >      WHERE pub17.adno     = :b4
> >        AND pub17.pubno    = :b3
> >        AND pub17.vno      = :b2
> >        AND pub17.pub_sysdate <> :b1
> > 
> > call     count       cpu    elapsed       disk    
> 
> > query    current        rows
> > ------- ------  -------- ---------- ----------
> > ---------- ----------  ----------
> > Parse        0      0.00       0.00          0    
>    
> >  0          0           0
> > Execute 110310    645.28    1846.76          0    
> > 441240          0           0
> > Fetch        0      0.00       0.00          0    
>    
> >  0          0           0
> > ------- ------  -------- ---------- ----------
> > ---------- ----------  ----------
> > total   110310    645.28    1846.76          0    
> > 441240          0           0
> > 
> > Misses in library cache during parse: 0
> > Optimizer goal: CHOOSE
> > Parsing user id: 23  (ADVDB)   (recursive depth:
> 1)
> > 
> > Rows     Execution Plan
> > ------- 
> >
> ---------------------------------------------------
> >       0  UPDATE STATEMENT   GOAL: CHOOSE
> >       0   UPDATE OF 'PUB'
> >       0    TABLE ACCESS   GOAL: ANALYZED (BY INDEX
> > ROWID) OF 'PUB'
> >       0     INDEX   GOAL: ANALYZED (UNIQUE SCAN)
> OF
> > 'I_PUB1' (UNIQUE)
> > 
> >
>
********************************************************************************
> > 
> > COMMIT
> > 
> > 
> > call     count       cpu    elapsed       disk    
> 
> > query    current        rows
> > ------- ------  -------- ---------- ----------
> > ---------- ----------  ----------
> > Parse        0      0.00       0.00          0    
>    
> >  0          0           0
> > Execute    110      0.08       0.13          0    
>    
> >  0          0           0
> > Fetch        0      0.00       0.00          0    
>    
> >  0          0           0
> > ------- ------  -------- ---------- ----------
> > ---------- ----------  ----------
> > total      110      0.08       0.13          0    
>    
> >  0          0           0
> > 
> > Misses in library cache during parse: 0
> 
=== message truncated ===


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

Other related posts: