Re: see higher CPU usage after increase SGA
- From: "zhu chao" <chao_ping@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Jun 2004 15:06:04 +0800
Thanks Bobak.
In fact, I did post the Logical IO count, (maybe line too long and you
did not see it):)
from statspack report before and after increase SGA:
10GB data buffer: 47,990.70 consistent gets per second
11GB data buffer: 47,707.28 consistent gets per second.
It seems pretty high, as our database is quite read intensive. Most of user
view 100 page/item before they make a transaction.
[oracle@main-db1**biddb]$grep "parse time" *.lst
10gb.lst:parse time cpu 64,885 3.0
0.0
10gb.lst:parse time elapsed 70,835 3.3
0.0
11gb.lst:parse time cpu 63,586 3.0
0.0
11gb.lst:parse time elapsed 70,160 3.3
0.0
[oracle@main-db1**biddb]$grep "Logical reads" *.lst
10gb.lst: Logical reads: 47,990.70
626.99
11gb.lst: Logical reads: 47,707.28
623.26
----- Original Message -----
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, June 09, 2004 10:56 PM
Subject: RE: see higher CPU usage after increase SGA
> Zhu,
>
> Consider that possibly your CPU usage was being throttled by the disk I/O,
and by reducing disk I/O, you allow Oracle to do more work, hence consume
more CPU. You mentioned that transactions were the same from one snapshot
to the other, but that doesn't count SELECT statements. Try also comparing
logical reads/sec. How do those numbers compare? What about Parse CPU?
>
> -Mark
>
> Mark J. Bobak
> Oracle DBA
> ProQuest Company
> Ann Arbor, MI
> "Post Hoc Ergo Propter Hoc"
>
>
> -----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:
