RE: see higher CPU usage after increase SGA

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 9 Jun 2004 10:03:30 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKAENDENAA.mwf@rsiz.com>

okay, I'm a sucker for mystery puzzles with partial information. Take a look at Hotsos' site to get an idea of figuring out where you're burning your time. In the meantime, from what you've written I have these observations:

Neither disk farm/io channel capacity nor CPU utilization is what limits your performance to 76.54/sec.
- How can I say this? Well, in the boundary case that you exactly shifted the bottleneck from disk to CPU I'd be wrong, but otherwise you previously had CPU to burn and now you've got io capacity to burn, and the rate didn't change.
Probably the increase in CPU utilization is just searching longer memory structures in Oracle, although I suppose it could be something like a bit of increased paging. OR, now that you've marginally reduced waits for disk reads you're pounding a bit harder on the actual bottleneck.

Someone might have an educated guess at your new bottleneck, but without data about where your processes are spending time and which resources are in short supply compared to load, I'm guessing all they'd be doing is guessing.

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

Received on Wed Jun 09 2004 - 09:06:20 CDT