[oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)

Comments in line

On 01/26/2004 01:46:25 PM, 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.

Why did you turn 10046 on? The information that you've produced could  
have been produced by a simple SQL_TRACE. The event 10046 should have  
run with the level set to 10 or 12 and tkprof should have been done  
with waits=yes sys=yes sort=\(exeela\)



> 
> Any ideas?

Errr, no, not so far. Did you monitor V$SESSION_EVENT and see what is  
your session waiting  for? If it's waiting for a disk read, did you  
check where do the blocks you get as P2 belong? Is it an index block,
data block, undo segment?

Other related posts: