Re: Oracle db writer taking too much CPU ?

Howdy folks...

Thanks for the responses. I ran these two queries on the database, and two other databases here. The one that's been giving me trouble had a result of 60(!) incomplete checkpoints and a 60.3% hit_ratio. Versus the other databases that had 0 incomplete checkpoints, and I forget the hit ratio, but not so low...

So, a bunch of checks were done, and a bad SCSI disk was found! Wee! So they're gonna fix that! Yippee!

And while I'd love to claim I was the hero of the company, I of course told everyone about my new best friends, on the ORACLE-L fatcity mailing list...

Thanks again folks.

CHUCK_HAMILTON_at_qvc.com wrote:
>
> If DBWR is taking up that much time, something isn't tuned correctly. My
> first bet is that the db is in a constant state of checkpointing itself.
> Are there messages in the alert log that say something like "checkpoing not
> complete"? Also run this query. If the result is > 1 it's beginning new
> checkpoints before the previous one has even completed. That's a problem.
>
> select sum( decode(name,'background checkpoints started',value,
> 'background checkpoints completed',-1*value,0) )
> incomplete_checkpoints
> from v$sysstat;
>
> Another thing that'll cause DBWR to work overtime is if it has to free
> blocks in the buffer cache too often. This could be caused by an undersized
> buffer cache. An undersized buffer cache can generally be determined by
> looking at the buffer hit ratio which is determined by this query...
>
> select (b.value + c.value - a.value) / (b.value + c.value) * 100 hit_ratio
> from v$sysstat a, v$sysstat b, v$sysstat c
> where a.name = 'physical reads'
> and b.name = 'consistent gets'
> and c.name = 'db block gets';
>
> There are other issues effecting DBWR too but check these first.
>
> --
> Chuck Hamilton
> QVC Inc.
> Enterprise Technical Services
> Oracle DBA
>
>
> R Bresner
> <wawb_at_escape. To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> net> cc: (bcc: CHUCK HAMILTON/QVC)
> Ext: NA Subject: Oracle db writer taking too much CPU ?
> Sent by:
> root_at_fatcity.
> com
>
>
> 05/01/00
> 10:46 AM
> Please
> respond to
> ORACLE-L
>
>
>
> Howdy all,
>
> I just started work at a leetle place which does data
> warehousing, and my current project involves a 40GB database
> with some 19.5million rows. So, I'm not surprised when simple
> operations take a long, long, (and did I mention LONG?)
> time to finish.
> But, last week there was a problem with a control file
> (did I mention I'm not a DBA, and I'm just parroting
> what I heard?) which was causing the Oracle DB writer
> process to suck up CPU wildly as it wrote many messages
> to an alert file.
> And, my simple operations took even longer than normal.
> ( Many hours longer. )
> I watched the DBA guy fix the problem, and so I could
> now be classified as one of those people who only knows half
> the story so I assume I know everything, and from what I
> know, it still is running slow. The DBA guy says that the
> slowness I'm experiencing is back to normal slowness, (did
> I mention they're bIG BIG databases?)
>
> I'd ask for some benchmark comparisons, but I don't know
> anything about the Oracle server... Let's just assume its
> a VERY good machine, since I heard it was expensive and
> relatively new.
>
> You see, last week when the DB writer was sucking up CPU wildly
> everyone panic'd and said "Something must be DONE!" This week,
> that same db writer is still sucking up CPU, and now they're
> saying, "Oh, that's normal."
> They're saying that because there are no new messages in the
> alert file.
>
> I'm asking, is that _REALLY_ normal? I kick off a simple update:
> sqlplus @my_update &
>
> and it appears in my top as taking up between 0.15% and 5.00%, while
> the dbwr has it's own CPU to itself, and is churning away at a keen
> 33%. ( 3CPUs, ya see ).
>
> I admit up front, I'm no DBA, and I don't know what I'm mumbling
> about. I just want to get out of work earlier, ya see.
>
> If it matters, we're running Oracle server 8.0.4 on some machine
> over in that room there.
>
> Any explanation would be appreciated.
> Thanks
> --
> Author: R Bresner
> INET: wawb_at_escape.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Author:
> INET: CHUCK_HAMILTON_at_qvc.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon May 01 2000 - 17:42:55 CDT