[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@xxxxxxxxxxxxxx



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Barbara Baker
Sent: Monday, January 26, 2004 1:46 PM
To: oracle-l@xxxxxxxxxxxxx
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;
  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: