OMG! Mike, you the man! Endless loop time. Tom Mercadante Oracle Certified Professional -----Original Message----- From: Mike Spalinger [mailto:Michael.Spalinger@xxxxxxx] Sent: Monday, January 26, 2004 2:23 PM To: oracle-l@xxxxxxxxxxxxx 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/ >