| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle db writer taking too much CPU ?
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
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
Received on Mon May 01 2000 - 12:22:10 CDT
 |
 |