[oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
- From: Barbara Baker <barbarabbaker@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 26 Jan 2004 11:07:51 -0800 (PST)
The process in question has these waits associated
with it:
Total Time Avg
SID Username EVENT
Waits Waited Wait
------ ---------- ------------------------------
-------- -------- ----------
17 ADVDB latch free
14 4 0
17 ADVDB log file sync
2 4 2
17 ADVDB SQL*Net break/reset to client
40 0 0
17 ADVDB SQL*Net message from client
45 17 0
17 ADVDB SQL*Net message to client
45 0 0
17 ADVDB control file sequential read
2 0 0
from v$session_wait, I see this:
SID EVENT P1TEXT P1 P2TEXT
P2 P3TEXT P3
----- ---------------- ---------- ----------- --------
-------- -------- ------
17 latch free address 713528668 number
98 tries 1
Latch 98 is cache buffer chains:
select * from v$latchname where latch#=98;
LATCH# NAME
---------- ----------------------
98 cache buffers chains
--- Mladen Gogala <mladen@xxxxxxxxxxxxxxx> wrote:
> 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?
>
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
Other related posts:
