[oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
- From: Paul Baumgartel <treegarden@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 26 Jan 2004 11:35:49 -0800 (PST)
Mike, it was very gracious of you to put it that way.
;-)
PB
--- 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
> > Optimizer goal: CHOOSE
> > Parsing user id: 23 (ADVDB) (recursive depth: 1)
> >
> >
> >
> >
>
********************************************************************************
> >
> > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> >
> > call count cpu elapsed disk
> > query current rows
> > ------- ------ -------- ---------- ----------
> > ---------- ---------- ----------
> > Parse 0 0.00 0.00 0
> > 0 0 0
> > Execute 0 0.00 0.00 0
> > 0 0 0
> > Fetch 0 0.00 0.00 0
> > 0 0 0
> > ------- ------ -------- ---------- ----------
> > ---------- ---------- ----------
> > total 0 0.00 0.00 0
> > 0 0 0
> >
> > Misses in library cache during parse: 0
> >
> >
> > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
>
=== message truncated ===
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
Other related posts:
