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: Oracle db writer taking too much CPU ?

Re: Oracle db writer taking too much CPU ?

From: Kimberly Smith <KSMITH1_at_gmd.fujitsu.com>
Date: 02 May 2000 08:13:43 -0700
Message-Id: <10485.104680@fatcity.com>


--=_ORCL_5946146_0r0
Content-Type:text/plain; charset="iso-8859-1" Content-Transfer-Encoding:quoted-printable

Yeah but you were smart enough to realize you needed to post something to such a great list. Still a hero;-)



Kimberly Smith
EDS/Fujitsu
Gresham, Oregon
Work: 503-669-6050
Email: kimberly.smith_at_gmd.fujitsu.com

        kimberly.smith_at_eds.com

--=_ORCL_5946146_0r0
content-type:message/rfc822

Date: 01 May 2000 17:04:53
From:R Bresner <wawb_at_escape.net>
To:Multiple recipients of list ORACLE-L <ORACLE-L> Subject:Re: Oracle db writer taking too much CPU ? Reply-to:UNIX.IOGMD:ORACLE-L_at_fatcity.com Return-Path:root_at_fatcity.cts.com

Received:from gmd.fujitsu.com (ducks.gmd.fujitsu.com [198.6.119.2]) by hdoc02.gmd.fujitsu.com with SMTP (8.7.1/8.7.1) id RAA04772 for <ksmith1.ofcmail_at_hdbi01>; Mon, 1 May 2000 17:23:24 -0700 (PDT)
Received:from dfwl01.gmd.fujitsu.com by gmd.fujitsu.com (SMI-8.6/) id RAA19268; Mon, 1 May 2000 17:23:18 -0700
Received:from dfwl01.gmd.fujitsu.com (root_at_localhost) by dfwl01.gmd.fujitsu.com with ESMTP id RAA21426 for <ksmith1_at_gmd.fujitsu.com>; Mon, 1 May 2000 17:23:17 -0700 (PDT)
Received:from newsfeed.cts.com (newsfeed.cts.com [209.68.192.199]) by dfwl01.gmd.fujitsu.com with ESMTP id RAA21422 for <ksmith1_at_gmd.fujitsu.com>; Mon, 1 May 2000 17:23:16 -0700 (PDT)
Received:from fatcity.UUCP (uucp_at_localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id QAA02021; Mon, 1 May 2000 16:26:11 -0700 (PDT)
Received:by fatcity.com (02-Mar-2000/v1.0f-b68/bab) via UUCP id 00155710; Mon, 01 May 2000 16:04:53 -0800
Message-ID:<F001.00155710.20000501160453_at_fatcity.com> X-Comment:Oracle RDBMS Community Forum
X-Sender:R Bresner <wawb_at_escape.net>
Sender:root_at_fatcity.com
Errors-To:ML-ERRORS_at_fatcity.com
Organization:Fat City Network Services, San Diego, California X-ListServer:v1.0f, build 68; ListGuru (c) 1996-2000 Bruce A. Bergman Precedence: bulk
MIME-Version: 1.0
Content-Type:text/plain; charset=us-ascii Content-Transfer-Encoding:7bit

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
> also send the HELP command for other information (like subscribing).

-- 
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).

--=_ORCL_5946146_0r0--



------------------------------

 From: Anuj Gupta <AGupta_at_rens.com>
Received on Tue May 02 2000 - 10:13:43 CDT

Original text of this message

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