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

From: Barbara Baker <barbarabbaker_at_yahoo.com>
Date: Mon, 26 Jan 2004 11:07:51 -0800 (PST)
Message-ID: <20040126190751.89375.qmail@web14421.mail.yahoo.com>

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_at_wangtrading.com> 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/ Received on Mon Jan 26 2004 - 13:07:51 CST