| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> [oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
Barbara,
Could try to do it in one SQL statement (no PL/SQL):
UPDATE pub DT
SET DT.pub_sysdate =
(SELECT ST.mdate
FROM pub14 ST
WHERE DT.adno = ST.adno
AND DT.pubno = ST.pubno
AND DT.vno = ST.vno)
Should work much faster without switching between SQL and PL/SQL.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Barbara Baker
Sent: Monday, January 26, 2004 1:46 PM
To: oracle-l_at_freelists.org
Subject: [oracle-l] PL/Sql Update Table runs 38 hrs (so far)
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;
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 fromdisk
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.
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.
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.
 |
 |