Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: excessive number of db writer

Re: excessive number of db writer

From: Anand Rao <panandrao_at_gmail.com>
Date: Wed, 6 Sep 2006 17:13:39 +0530
Message-ID: <d70710370609060443r7bb17582xa3455f2db0461e43@mail.gmail.com>


Its good to have more working sets when you need it.

x$kcbwds has some of things that you are looking for.

Statspack has some details on DBWR statistics.

DBWR buffers scanned
DBWR checkpoint buffers written
DBWR checkpoints
DBWR cross instance writes
DBWR free buffers found
DBWR fusion writes
DBWR lru scans
DBWR make free requests
DBWR revisited being-written buff
DBWR summed scan depth
DBWR transaction table writes
DBWR undo block writes

background checkpoints completed
background checkpoints started

consistent gets
db block changes
db block gets

Of course, you would not interpret them individually. They have to be collectively studied and compared (with other stats) with respect to a particular problem or symptom to get some good information that could be used for further diagnosis/tuning. How useful and reliable these statistics are is something that is not definitive.

cheers
anand

On 06/09/06, amonte <ax.mount_at_gmail.com> wrote:
>
> they are not trying to fix anything, they are being proactive, this is not
> in production yet, they want to avoid emergency reboots.
>
> You said that more dbwr means more working sets and less buffers are
> managed by each dbwr, does that sound bad if you have too many? Doesnt sound
> bad to me. I cant see any side effects and so far I cant find any
> documentation which suggest this would lead to problems...
>
> What statistics you check for DBWR efficiency?
>
> Cheers
>
> Alex
>
>
>
> On 9/6/06, Anand Rao <panandrao_at_gmail.com > wrote:
> >
> > Hi,
> >
> > Its more the practice than the actual suggestions.
> >
> > you need to treat the cause not just the symptoms. what problems are
> > they trying to solve?
> >
> > well, if the vendor is sure that those settings help them in
> > upgrade/migration due to their past experience, that fine as long as they
> > have tested it and done it a few times with success.
> >
> > with 20 DBWrs, you have more working sets and more latches protecting
> > the structures. the no.of buffers that each DBWR has to "manage" is
> > lesser when you have more working sets (or DBWRs).
> >
> > our applications constantly generates around writes of 200 MB/second and
> > reads that are more than that. i only ever use 2 DBWRs. i have previously
> > tried 4, 8, 16 and 20 DBWRs. nothing changed significantly in the
> > performance of the "complete" application. maybe i saved 20-35 seconds
> > somewhere...but thats not my concern.
> >
> > You can dig deep and produce a table that "could" time the writes with
> > different no. of writers, but does that affect your total/overall response
> > time? how much difference does it make?
> >
> > if you find that your application needs and uses 4 DBWRs well, then you
> > try and use it. see how fast the writes are compared to 2 DBWRs....what is
> > the delta change?
> >
> > again, as far as the other settings are concerned, i wouldn't fiddle
> > with them unless i know exactly what i am doing.
> >
> > regards
> > anand
> >
> >
> > On 06/09/06, amonte <ax.mount_at_gmail.com > wrote:
> > >
> > > Anand do you know what side effects can excessive DBWR cause?
> > >
> > > I dont know because I cant find anything which tells you the side
> > > effects :-(
> > >
> > >
> > >
> > >
> > > On 9/6/06, Anand Rao <panandrao_at_gmail.com> wrote:
> > > >
> > > > 20 DB Writers is way too high.
> > > >
> > > > what kind of an application is this? Even for a
> > > > 'write-only-will-not-select-any-data' application, 20 is high questionable.
> > > >
> > > > normally, you don't need to dab around with those parameters. why
> > > > did they need to be changed?
> > > >
> > > > sorry, but your Oracle consultant has got it wrong.
> > > >
> > > > cheers
> > > > anand
> > > >
> > > >
> > > >
> > > > On 06/09/06, K Gopalakrishnan < kaygopal_at_yahoo.com> wrote:
> > > > >
> > > > > Alex,
> > > > >
> > > > > Did you get an explanation for those suggestions? It would be
> > > > > difficult for us to comment on those changes without any additional
> > > > > details..
> > > > >
> > > > > However, However,However, 20 DBWriters are certainly high for your
> > > > > configuration ;)
> > > > >
> > > > > Have a nice day !!
> > > > > ------------------------------------------------------------
> > > > > Best Regards,
> > > > > K Gopalakrishnan,
> > > > > Co-Author: Oracle Wait Interface: Oracle Press 2004.
> > > > > http://www.amazon.com/gp/product/007222729X/
> > > > >
> > > > > Coming Soon: Oracle RAC Handbook, Oracle Press 2006
> > > > > http://www.amazon.com/gp/product/007146509X/
> > > > >
> > > > > ----- Original Message ----
> > > > > From: amonte <ax.mount_at_gmail.com>
> > > > > To: oracle-l_at_freelists.org
> > > > > Sent: Wednesday, September 6, 2006 1:39:51 AM
> > > > > Subject: excessive number of db writer
> > > > >
> > > > > Hi
> > > > >
> > > > > I have been suggested by some Oracle Consultant to set 20 Database
> > > > > Writer in a 12 CPU HP-UX Server.
> > > > >
> > > > > Does anyone know what are the side effects of having excessive
> > > > > DBWR? I detected a higher CPU usage (between, 1% to 2%) not sure if its due
> > > > > to the number of Database Writer. Because when I increased the number of
> > > > > DBWR at init.ora I also changed these parameters
> > > > >
> > > > > _row_cache_cursors = 60
> > > > > _kgl_latch_count = 59
> > > > > _kghdsidx_count = 4
> > > > > statistics_level = basic
> > > > >
> > > > > again suugested by this guy.
> > > > >
> > > > > TIA
> > > > >
> > > > > Alex
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > All I need to make a comedy is a park, a policeman and a pretty girl
> > > > - Charlie Chaplin
> > > >
> > >
> > >
> >
> >
> > --
> > All I need to make a comedy is a park, a policeman and a pretty girl -
> > Charlie Chaplin
> >
>
>

-- 
All I need to make a comedy is a park, a policeman and a pretty girl -
Charlie Chaplin

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 06 2006 - 06:43:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US