RE: see higher CPU usage after increase SGA

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 9 Jun 2004 12:27:15 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKKENIENAA.mwf@rsiz.com>

Just to be clear regarding #3: I don't believe I typed the word LATCH in my reply, but rather larger memory structures. JL mentioned one of the most likely larger memory structures, that being many more buffers scanned for row-by-row compares. The point being without doing accurate research (which may indeed be non-trivial for your situation), we can only guess. Guesses by JL tend to be quite insightful.

>From your latest comments, you appear to not have a specific operational
problem, but rather you're just trying to stave off hitting the ceiling on any one component as load increases. In general, increasing capacity of one type of resource can allow the increased consumption of another resource with no corresponding increase in throughput for any resource that has the capacity to be used waiting for the satisfaction of something else. Actual measurements of loads and resource consumptions are the only reliable way to know the answer you seek. My reference to the Hotsos site was regarding a methodology to help you measure a complex environment.

Regarding #2: As neither CPU nor disk is your bottleneck (which I think I claimed you had proved except for a boundary condition possibility from the facts you gave us earlier), I suggest that you seek the actual bottleneck to determine if use can be reduced or capacity can be increased. Now that may in fact result in periodically pegging both disk and CPU at 100%, but your transaction rates should rise and the work will be done sooner. Using all the fast resources of a machine well during peak is not a bad thing unless it causes unacceptable interactive user wait times. Then you would need to control batch consumption rather than leave in place an artificial bottleneck.

-----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 10:38 AM
To: oracle-l_at_freelists.org
Subject: Re: see higher CPU usage after increase SGA

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


----------------------------------------------------------------
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 - 11:30:06 CDT