[oracle-l] Please Read- Re: PL/Sql Update Table runs 38 hrs (sofar)
- From: Jared Still <jkstill@xxxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Jan 2004 20:33:25 -0800
This post brings up the topic of mailing list etiquette.
When creating a new post to a mailing list, please create
a new email in your MUA ( mail user agent: Outlook, etc )
Why is that?
Because when you reply to an email on a list and then
change the subject line, as has been done in this thread,
the result is the new subject is linked to the old subject
via the mail headers.
Ergo, MUA's that know how to handle email threads ( Evolution,
Pine, Eudora, maybe even Outlook ) bunch your new topic together
with the previous one.
So please, when you have a new topic, start a new thread.
Thanks,
Jared
On Mon, 2004-01-26 at 10:46, 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/
-------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
-------------------------------------------------------------
Other related posts:
- » [oracle-l] Please Read- Re: PL/Sql Update Table runs 38 hrs (sofar)
