[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? Received on Mon Jan 26 2004 - 12:56:18 CST