From oracle-l-bounce@freelists.org Wed Jun 9 10:36:35 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i59FaKD30311 for ; Wed, 9 Jun 2004 10:36:30 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i59Fa4630228 for ; Wed, 9 Jun 2004 10:36:14 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 80AB172CF62; Wed, 9 Jun 2004 10:21:09 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13023-08; Wed, 9 Jun 2004 10:21:09 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BCBC272CF07; Wed, 9 Jun 2004 10:21:08 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 09 Jun 2004 10:19:41 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B5D3972CA4C for ; Wed, 9 Jun 2004 10:19:39 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11792-66 for ; Wed, 9 Jun 2004 10:19:39 -0500 (EST) Received: from audiogram.mail.pas.earthlink.net (audiogram.mail.pas.earthlink.net [207.217.120.253]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C544C72CE55 for ; Wed, 9 Jun 2004 10:19:37 -0500 (EST) Received: from user-vcauocj.dsl.mindspring.com ([216.175.97.147] helo=TerrySutton) by audiogram.mail.pas.earthlink.net with asmtp (Exim 3.36 #4) id 1BY5AI-0004nZ-00 for oracle-l@freelists.org; Wed, 09 Jun 2004 08:38:58 -0700 Message-ID: <028001c44e37$e04a73c0$0400a8c0@TerrySutton> From: "Terry Sutton" To: References: <002401c44e2f$5785ea40$32355fd3@corp.ebay.com> Subject: Re: see higher CPU usage after increase SGA Date: Wed, 9 Jun 2004 08:35:21 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_027B_01C44DFC.B3B61B10" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-ELNK-Trace: 5830af7b3b91928f1aa676d7e74259b7b3291a7d08dfec79deea82bc4e6314e863417ca3a0c61277350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2356 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: terrysutton@usa.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org ------=_NextPart_000_027B_01C44DFC.B3B61B10 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: quoted-printable Your data doesn't seem to make sense. You say that your disk hits 75% = busy, but you're only doing 448 physical I/Os per second. On a system = your size I don't see how that level of PIOs would keep a disk that = busy. --Terry > Hi, > I did not post the all information within that email, else it will = be > too lenghy, and few people will read it through. > 1. There is no pagein/out taking place. Vmstat shows sr=3D0 and = sar -g > shows no swapin/out. > 2. At this time, neither CPU nor Disk is not bottleneck now. We = can > support more users. Disk is reaching its capacity soon, as > BUSY%(iostat -xn ) is at 75%+ during peak time. This is why we = increase SGA > to reduce the load on disk storage. > 3. You said it can because larger sga caused scanning the LATCH = using > more CPU. I also think it is possible. But difficult to verify. > 4.It is difficult to find out who used more resource. As there are = 800+ > connection to the database, using the same username. And I did not = record > the old v$sesstat. Even record that old v$sesstat, it is difficult to > compare that 5% in the 800+ sessions. >=20 > It is just I am not sure what caused the more CPU usage. Hotsos = notes > cannot explain everything, I read most of the notes there more than 5 = times. > One of them is LIO consumes much CPU, which I do not quite agree, = according > to my tune experience in the past several weeks. > There was a HUGE SQL(which used 30% of total system buffer_gets > according to statspack report). I changed the SQL, and later it used = less > than 0.5% of total system buffer_gets(it just dissappear from = statspack > report), but system CPU usage just drop by less than 5% from statspack > report(compare the CPU used by this session before/after change)!. >=20 > Regards > Zhu Chao. >=20 >=20 > ----- Original Message -----=20 > From: "Mark W. Farnham" > To: > Sent: Wednesday, June 09, 2004 10:03 PM > Subject: RE: see higher CPU usage after increase SGA >=20 >=20 > > Now, trying to be actually useful, I think your next task is to find = out > > where time is being spent. If analysis shows that the big = consumer(s) > is/are > > already doing as little work as possible, then you make the = restrictive > > system component faster (or discover that it is not cost effective = to make > > the bottleneck resource faster.) If analysis shows that big = consumer(s) > > is/are doing more work than required for the task at hand, you work = to > > improve the big consumer(s). > > > > mwf > > > > -----Original Message----- > > From: oracle-l-bounce@freelists.org > [mailto:oracle-l-bounce@freelists.org]On > > Behalf Of zhu chao > > Sent: Wednesday, June 09, 2004 9:31 AM > > To: oracle-l@freelists.org > > Subject: see higher CPU usage after increase SGA > > > > > > Hi, > > I once saw Jonathan said at metalink that huge SGA does not help = in > many > > case, But no further discuss at that topic later. Last night we = added 1Gb > > to oracle sga and we see fewer disk read but higher CPU usage. > > > > Fewer disk read of course cut CPU usage, but larger buffer cache > > management in unix and oracle, seems caused higher CPU usage. Has = someone > > also have similar experience? How to explain the higher CPU usage? > > > > We have a 16GB memory sun 880 with 10G data cache. As disk read = get > > higher and higher , and not much SQL to tune we deciede to increase = data > > buffer from 10G to 11GB, as there is still 1.5G free memory on the = host. > > > > We expect to see some CPU usage drop, as disk read drop by 30%. = But > > after 1 day's run, we saw higher CPU usage then before we increase = the > SGA. > > > > http://www.cnoug.org/attachments/LDBn_cpu.bmp (the Excel picture = that > > shows the CPU usage before and after increase sga). > > The following Statistics from Oracle shows the load profile = before and > > after SGA increase: > > > > LIO PIO = Transaction/Second > > CPU usage in oracle > > 10gb 47,990.70 448.68 76.54 > > 177.9 > > 11gb 47,707.28 325.95 76.54 > > 187.9 > > Change: Nearly same Disk read dropped = Transaction > rate > > CPU used increased. > > 30% keep > consistent > > by 5% > > > > > > Time I measure=A3=BA 9 am =A8C 15pm. > > Oracle: 5% increase. > > Unix: 6% increase. > > > > > > ---------------------------------------------------------------- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > ---------------------------------------------------------------- > > To unsubscribe send email to: oracle-l-request@freelists.org > > put 'unsubscribe' in the subject line. > > -- > > Archives are at http://www.freelists.org/archives/oracle-l/ > > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > > ----------------------------------------------------------------- > > > > > > ---------------------------------------------------------------- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > ---------------------------------------------------------------- > > To unsubscribe send email to: oracle-l-request@freelists.org > > put 'unsubscribe' in the subject line. > > -- > > Archives are at http://www.freelists.org/archives/oracle-l/ > > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > > ----------------------------------------------------------------- > > > > >=20 > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- >=20 > ------=_NextPart_000_027B_01C44DFC.B3B61B10 Content-Type: text/html; charset="gb2312" Content-Transfer-Encoding: quoted-printable
Your data doesn't seem to make = sense.  You say=20 that your disk hits 75% busy, but you're only doing 448 physical I/Os = per=20 second.  On a system your size I don't see how that level of PIOs = would=20 keep a disk that busy.
 
--Terry

> Hi,
>     I did not post the all = information=20 within that email, else it will be
> too lenghy, and few people = will read=20 it through.
>     1. There is no pagein/out taking = place.=20 Vmstat shows sr=3D0 and sar -g
> shows no swapin/out.
>=20     2. At this time, neither CPU nor Disk is not = bottleneck now.=20 We can
> support more users. Disk is reaching its capacity soon,=20 as
> BUSY%(iostat -xn ) is at 75%+ during peak time. This is why = we=20 increase SGA
> to reduce the load on disk storage.
>=20     3. You said it can because larger sga caused scanning = the=20 LATCH using
> more CPU. I also think it is possible. But difficult = to=20 verify.
>     4.It is difficult to find out who = used more=20 resource. As there are 800+
> connection to the database, using = the same=20 username. And I did not record
> the old v$sesstat. Even record = that old=20 v$sesstat, it is difficult to
> compare that 5% in the 800+=20 sessions.
>
>     It is just I am not sure = what=20 caused the more CPU usage. Hotsos notes
> cannot explain = everything, I=20 read most of the notes there more than 5 times.
> One of them is = LIO=20 consumes much CPU, which I do not quite agree, according
> to my = tune=20 experience in the past several weeks.
>     There = was a=20 HUGE SQL(which used 30% of total system buffer_gets
> according to = statspack report). I changed the SQL, and later it used less
> = than 0.5%=20 of total system buffer_gets(it just dissappear from statspack
> = report),=20 but system CPU usage just drop by less than 5% from statspack
>=20 report(compare the CPU used by this session before/after = change)!.
>=20
> Regards
> Zhu Chao.
>
>
> ----- = Original=20 Message -----
> From: "Mark W. Farnham" <
mwf@rsiz.com>
> To: <oracle-l@freelists.org>
>=20 Sent: Wednesday, June 09, 2004 10:03 PM
> Subject: RE: see higher = CPU=20 usage after increase SGA
>
>
> > Now, trying to = be=20 actually useful, I think your next task is to find out
> > = where time=20 is being spent. If analysis shows that the big consumer(s)
>=20 is/are
> > already doing as little work as possible, then you = make the=20 restrictive
> > system component faster (or discover that it is = not=20 cost effective to make
> > the bottleneck resource faster.) If = analysis=20 shows that big consumer(s)
> > is/are doing more work than = required for=20 the task at hand, you work to
> > improve the big = consumer(s).
>=20 >
> > mwf
> >
> > -----Original=20 Message-----
> > From:
oracle-l-bounce@freelists.org
>=20 [mailto:oracle-l-bounce@freelists.org]On
> > Behalf Of zhu = chao
>=20 > Sent: Wednesday, June 09, 2004 9:31 AM
> > To:
oracle-l@freelists.org
> >=20 Subject: see higher CPU usage after increase SGA
> >
>=20 >
> > Hi,
> >     I once saw = Jonathan=20 said at metalink that huge SGA does not help in
> many
> = > case,=20 But no further discuss at that topic later.  Last night we added=20 1Gb
> > to oracle sga and we see fewer disk read but higher CPU = usage.
> >
> >     Fewer disk read = of=20 course cut CPU usage, but larger buffer cache
> > management in = unix=20 and oracle, seems caused higher CPU usage. Has someone
> > also = have=20 similar experience? How to explain the higher CPU usage?
> = >
>=20 >     We have a 16GB memory sun 880 with 10G data = cache.=20 As disk read get
> > higher and higher , and not much SQL to = tune we=20 deciede to increase data
> > buffer from 10G to 11GB, as there = is still=20 1.5G free memory on the host.
> >
> = >    =20 We expect to see some CPU usage drop, as disk read drop by 30%. = But
> >=20 after 1 day's run, we saw higher CPU usage then before we increase = the
>=20 SGA.
> >
> >    
http://www.cnoug.org/attachments/LDBn_cpu.bmp (the Excel picture that
> > shows the CPU usage = before and=20 after increase sga).
> >     The following=20 Statistics from Oracle shows the load profile before and
> > = after SGA=20 increase:
> >
>=20 >           &nb= sp;      =20 LIO           &nbs= p;       =20 PIO           =20 Transaction/Second
> > CPU usage in oracle
>=20 >      =20 10gb       =20 47,990.70          &nbs= p;  =20 448.68           &= nbsp;  =20 76.54
> > 177.9
> = >      =20 11gb       =20 47,707.28          &nbs= p; =20 325.95           &= nbsp;   =20 76.54
> > 187.9
> = >      =20 Change:     Nearly=20 same           Disk = read=20 dropped   Transaction
> rate
> > CPU used=20 increased.
>=20 >           &nb= sp;           &nbs= p;            = ;      =20 30%           &nbs= p;  =20 keep
> consistent
> > by 5%
> >
> = >
>=20 >       Time I measure=A3=BA 9 am =A8C = 15pm.
>=20 >       Oracle: 5% increase.
>=20 >       Unix:    6%=20 increase.
> >
> >
> >=20 ----------------------------------------------------------------
> = >=20 Please see the official ORACLE-L FAQ:
http://www.orafaq.com
> >=20 ----------------------------------------------------------------
> = > To=20 unsubscribe send email to: 
oracle-l-request@freelists.org
>=20 > put 'unsubscribe' in the subject line.
> > --
> > = Archives are at
http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html<= BR>> >=20 -----------------------------------------------------------------
>= =20 >
> >
> >=20 ----------------------------------------------------------------
> = >=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >=20 ----------------------------------------------------------------
> = > To=20 unsubscribe send email to: 
oracle-l-request@freelists.org
>=20 > put 'unsubscribe' in the subject line.
> > --
> > = Archives are at
http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html<= BR>> >=20 -----------------------------------------------------------------
>= =20 >
> >
>
>=20 ----------------------------------------------------------------
> = Please=20 see the official ORACLE-L FAQ: http://www.orafaq.com
>=20 ----------------------------------------------------------------
> = To=20 unsubscribe send email to: 
oracle-l-request@freelists.org
>=20 put 'unsubscribe' in the subject line.
> --
> Archives are = at=20
http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html<= BR>>=20 -----------------------------------------------------------------
>= =20
> ------=_NextPart_000_027B_01C44DFC.B3B61B10-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------