Re: see higher CPU usage after increase SGA
- From: "zhu chao" <chao_ping@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Jun 2004 11:47:03 +0800
Hi, Terry:
I am trying to give out the real number, I won't try to fool your guys
and waste your time.
We are running Sun Fire V880, with 8*1.2G CPu and 16G memory, and a
HDS9570 15*73GB Raid10 storage.
The following is the IO statistics from unix(iostat -xnz 2)
extended device statistics
r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device
0.5 0.0 4.0 0.0 0.0 0.0 0.0 0.7 0 0 c1t0d0
296.5 0.5 7364.2 4.0 0.0 1.1 0.0 3.6 0 71
c3t50060E8000439AA0d2
171.5 55.0 1372.0 1346.8 0.0 0.9 0.0 3.8 0 66
c3t50060E8000439AA0d1
We did not setup the storage with optimal setting, which according to
HDS engineer, should be setup as 3* (2D+2P) raid group + 2standby. While we
setup it like (6D+6P)+2 standby .
----- Original Message -----
From: Terry Sutton
To: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, June 09, 2004 11:35 PM
Subject: Re: see higher CPU usage after increase SGA
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=0 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.
>
> 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)!.
>
> Regards
> Zhu Chao.
>
>
> ----- Original Message -----
> From: "Mark W. Farnham" <mwf@xxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Wednesday, June 09, 2004 10:03 PM
> Subject: RE: see higher CPU usage after increase SGA
>
>
> > 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
> > Behalf Of zhu chao
> > Sent: Wednesday, June 09, 2004 9:31 AM
> > To: oracle-l@xxxxxxxxxxxxx
> > 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： 9 am ? 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@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //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@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
Other related posts:
