| 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: see higher CPU usage after increase SGA
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 .
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_at_rsiz.com>
> To: <oracle-l_at_freelists.org>
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On
> > Behalf Of zhu chao
> > Sent: Wednesday, June 09, 2004 9:31 AM
> > To: oracle-l_at_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£º 9 am ¨C 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_at_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_at_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_at_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
> -----------------------------------------------------------------
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jun 09 2004 - 23:59:59 CDT
 |
 |
