Re: multiple dbwr's and deadlock

James,

db writer processes have in principle nothing to do with CPU's (they spend more time waiting for an write to complete than that it takes CPU). So take 2-4 per disk.

Anjo.

James Mu wrote:

> Hi,
> I have some questions about Anjo's post. I think there is a basic
> principal that is db_writer_processes should equal or less than the number
> of the cpus. And if your system support AIO, maybe this problem will be some
> relief.
>
> James
> Anjo Kolk <k.kolk_at_chello.nl> wrote in message
> news:3AF79C25.1EAE31E_at_chello.nl...
> >
> > Here is the problem that you *could* be facing:
> >
> > 1) multiple insert process make buffers dirty in a rate that is higher
 than the
> > DBWR(s) can write out
> > 2) at some stage the insert processes will wait on the DBWR(s).
> > 3) such a wait will most likely be in the middle of an insert when you are
> > holding other resources (memory and enqueues)
> > 4) increase the number of DBWR(s) and you problems could go away (assuming
 that
> > you have locking problems because of this).
> >
> > Infact I just had the same scenario at a customer site. They complained
 about
> > 'hanging locks' (some process would hold on too locks for a long time and
> > others would wait very long). So they assumed a locking problem. The
 locking
> > problem in their case was a symptom of not having enough DBWRs.
> >
> > So what to do in your case:
> >
> > 1) db_block_lru_latches = 4-8
> > 2) db_writers = 16 or even higher
> > 3) _db_block_write_batch = <something decent, less than (db_files *
> > db_file_multi_block_read_count)/2).
> >
> > Anjo.
> >
> >
> > James Mu wrote:
> >
> > > hi,
> > > You ask a big question. To let it some clear, I think you should
 record
> > > the ora number, then go to metalink to query some info.
> > > James
> > >
> > > Maurice Samuels <samuels_at_red.seas.upenn.edu> wrote in message
> > > news:9crsph$o3r$1_at_netnews.upenn.edu...
> > > > hi,
> > > > recently i turned on multiple dbwr's (4) and increased the
 db_block_lru_latches (24) on an 8-CPU Sun 5500
> > > > server with 8G of memory (oracle 8.0.x). a few times when i've tried
 to
 run 5 insert /*+append ..
> > > > operations, i get
> > > > deadlock messages written to the alert.log and some of the operations
 outright fail. but i have no problems
> > > > running 4 of those types of operations at a time (is this related to
 how
 many writer's there are).
> > > > had anyone ever run into anything like this?
> > > > what performance views should i be watching to see how oracle is
 managing
 the buffer cache and locks?
> > > > can anyone point to a good book on the topic of multiple dbwr's and
 locks?
> > > > the oracle documentation regarding the multiple dbwr's is a bit
 confusing
 on this topic.
> > > > thanks in advance
> > > > -maurice
> > > > samuels_at_seas.upenn.edu
> >
> >
> >
> >
> >
  Received on Tue May 08 2001 - 13:54:40 CDT