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

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: