OMG! Mike, you the man! Endless loop time.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Mike Spalinger [mailto:Michael.Spalinger_at_Sun.COM]
Sent: Monday, January 26, 2004 2:23 PM
To: oracle-l_at_freelists.org
Subject: [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
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
>
> call count cpu elapsed disk
> query current rows
> ------- ------ -------- ---------- ----------
> ---------- ---------- ----------
> Parse 0 0.00 0.00 0
> 0 0 0
> Execute 110420 645.36 1846.90 0
> 441240 0 0
> Fetch 0 0.00 0.00 0
> 0 0 0
> ------- ------ -------- ---------- ----------
> ---------- ---------- ----------
> total 110420 645.36 1846.90 0
> 441240 0 0
>
> Misses in library cache during parse: 0
>
> 2 user SQL statements in session.
> 0 internal SQL statements in session.
> 2 SQL statements in session.
> 1 statement EXPLAINed in this session.
>
> Trace file: ././ent9i_ora_25786.trc
> Trace file compatibility: 9.00.01
> Sort options: prsela exeela fchela
> 1 session in tracefile.
> 2 user SQL statements in trace file.
> 0 internal SQL statements in trace file.
> 2 SQL statements in trace file.
> 2 unique SQL statements in trace file.
> 1 SQL statements EXPLAINed using schema:
> ADVDB.prof$plan_table
> Default table was used.
> Table was created.
> Table was dropped.
> 1875534 lines in trace file.
>
>
>
> alter session set nls_date_format ='DD-MON-YYYY
> HH24:MI:SS';
> select adno,pubno,vno,vnoflag,mdate from advdb.pub_14
> where adno=&adno
> /
> select adno,pubno,vno,vnoflag,pub_sysdate from
> advdb.pub
> where adno=&adno
> /
>
> Enter value for adno: 4335349
> old 2: where adno=&adno
> new 2: where adno=4335349
>
> ADNO PUBNO VNO V MDATE
> ---------- ---------- ---------- -
> --------------------
> 4335349 1 1 N 17-JUL-2002
> 14:36:00
> 4335349 1 2 N 17-JUL-2002
> 14:39:00
> 4335349 1 3 Y 17-JUL-2002
> 14:39:00
> 4335349 2 1 N 17-JUL-2002
> 14:36:00
> 4335349 2 2 N 17-JUL-2002
> 14:39:00
> 4335349 2 3 Y 17-JUL-2002
> 14:39:00
> 4335349 3 1 N 17-JUL-2002
> 14:36:00
> 4335349 3 2 N 17-JUL-2002
> 14:39:00
> 4335349 3 3 Y 17-JUL-2002
> 14:39:00
>
> 9 rows selected.
>
> Enter value for adno: 4335349
> old 2: where adno=&adno
> new 2: where adno=4335349
>
> ADNO PUBNO VNO V PUB_SYSDATE
> ---------- ---------- ---------- -
> --------------------
> 4335349 1 1 N 23-DEC-2003
> 13:10:01
> 4335349 1 2 N 23-DEC-2003
> 13:10:01
> 4335349 1 3 Y 23-DEC-2003
> 13:10:01
> 4335349 2 1 N 23-DEC-2003
> 13:10:01
> 4335349 2 2 N 23-DEC-2003
> 13:10:01
> 4335349 2 3 Y 23-DEC-2003
> 13:10:01
> 4335349 3 1 N 23-DEC-2003
> 13:10:01
> 4335349 3 2 N 23-DEC-2003
> 13:10:01
> 4335349 3 3 Y 23-DEC-2003
> 13:10:01
>
> 9 rows selected.
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free web site building tool. Try it!
> http://webhosting.yahoo.com/ps/sb/
>
Received on Mon Jan 26 2004 - 13:28:24 CST
