[oracle-l] Re: PL/Sql Update Table runs 38 hrs (so far)
- From: Mladen Gogala <mladen@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 26 Jan 2004 14:42:23 -0500
Barbara, your waits don't have anything to do with the execution plan.
It seems that your instance checkpoints a lot ("log file sync") and
has a problem with buffer chains latch, which in version 9.2.0.4 which
doesn't have controllable buffer latches indicates that your buffer
cache is too small. Also, make sure that either your DBWR is able to
use async I/O or that you have more writer processes. If you don't have
async I/O, my advice is to start one DBWR process/1024 buffers. If your
database is a file system database, check the parameter
filesystemio_options and see whether you can set it to "async",
"direct" or even "setall". It depends on the file system and platform.
Commercial VxFS and JFS can utilize both asynchronous I/O and direct I/
O. Ext3, ReiserFS, HPFS and UFS can do async but direct. Ext2 allegedly
can do both (there is a patch) but I haven't tested it yet. I have no
ide about NTFS.
Other then that, can you see TIME_WAITED? You may even have a problem
with the way your program talks to the network. My advice is to collect
the full 10046 trace, lev 12 and send it to www.hotsos.com. They will
know what to with it.
On 01/26/2004 02:07:51 PM, Barbara Baker wrote:
> 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:
