Re: see higher CPU usage after increase SGA

From: zhu chao <chao_ping_at_vip.163.com>
Date: Wed, 9 Jun 2004 22:37:46 +0800
Message-ID: <002401c44e2f$5785ea40$32355fd3@corp.ebay.com>

Hi,

I did not post the all information within that email, else it will be too lenghy, and few people will read it through.

There is no pagein/out taking place. Vmstat shows sr=0 and sar -g shows no swapin/out.
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.
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
-----------------------------------------------------------------

Received on Wed Jun 09 2004 - 10:01:13 CDT