Still, didn't see/hear why couldn't it be done in a single SQL (as I
suggested).
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Barbara Baker
Sent: Monday, January 26, 2004 2:37 PM
To: oracle-l_at_freelists.org
Subject: [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_at_Sun.COM> 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/
Received on Mon Jan 26 2004 - 14:00:10 CST
