[oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
- From: "paul bennett" <pbennett@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Jan 2004 13:40:05 -0600
Hi Barbara:
You may want to look at CTAS. You can create (pub) by joining
(pub_14) and (pub_17) using CTAS.
Paul Bennett
>>> barbarabbaker@xxxxxxxxx 01/26/04 12:46PM >>>
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/
Other related posts:
