From oracle-l-bounce@freelists.org Thu Jun 10 01:37:17 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5A6b2a22673 for ; Thu, 10 Jun 2004 01:37:12 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5A6aq622620 for ; Thu, 10 Jun 2004 01:37:02 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8EE6C72C2CF; Thu, 10 Jun 2004 01:22:24 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09301-100; Thu, 10 Jun 2004 01:22:24 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D785E72C1D2; Thu, 10 Jun 2004 01:22:23 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 10 Jun 2004 01:21:04 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3A54472C20B for ; Thu, 10 Jun 2004 01:21:03 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09905-53 for ; Thu, 10 Jun 2004 01:21:02 -0500 (EST) Received: from smtp.vip.163.com (smtp.vip.163.com [202.106.168.106]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B164672C107 for ; Thu, 10 Jun 2004 01:21:01 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by smtp.vip.163.com (Postfix) with SMTP id DBA414A0B for ; Thu, 10 Jun 2004 14:40:25 +0800 (CST) Received: from LSHACHZHU (unknown [220.248.6.124]) by 202.106.168.106 (Coremail:vip.163.com) with SMTP id HAEAAFgCyEDsdAZ8.1 for ; Thu, 10 Jun 2004 14:40:25 +0800 (CST) X-TEBIE-Originating-IP: [220.248.6.124] Message-ID: <019e01c44eb5$ce7e3040$2552fc0a@corp.ebay.com> From: "zhu chao" To: References: <009601c44e3d$c6a525e0$6701a8c0@CVMLAP02> Subject: Re: see higher CPU usage after increase SGA Date: Thu, 10 Jun 2004 14:40:22 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-2022-jp" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2458 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: chao_ping@vip.163.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Thanks Cary. I am not quite agree with your definition: Utilization and Usage. Utilization = usage_during_time_initerval/capacity_during_time_interval. During the same time interval, capacity is the same. So utilization increases is just the same as usage increase. Right? Before and after my tune effort(increase data buffer by 1GB), the statspack report time interval was the same: 9am to 15pm. And unix CPU usage report was also between 9am to 15pm. So I think Utilization increased by 5% is the same as CPU usage increased by 5%. Do you think so? Let's forget the above and go on analyze why CPU usage increased. Possible Reasons that CPU usage can decrease: Physical IO dropped by 30%.(that is, About 100-120DiskRead/Second) Possible Reasons that CPU usage can increase: Larger data buffer, same db_block_lru_latches (=4), cache buffers chains latch is same with 10gb/11gb data buffer. So each latch does manage more block buffers. My answer to your question: 1. Sorry I did not measure the application response time. As there are too many connections to the database. I *GUESS* there won't be noticable speedup in the program, as database layer is just maybe 30-50% of total application response time, and the application WAS pretty fast, most of them finished in 0.001-0.05Second. 2. What is most important to me is: Scalability, that is, to support more user, more transactions. As this is difficult to measure how much more business we can support, we use System Resource Usage percent to estimate it. So sorry, the answer to your second question is: Ratio. Do you have some suggestion about measure capacity other than the resource utilization ratio? Thanks. Regards Zhu Chao. The cache buffer chains latch activity report during 9am-15:00. Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 (11gb) cache buffers chains 1,868,578,608 449,111 13,588 435738/13175 /186/12/0 cache buffers lru chain 3,838,481 2,460 476 1985/474/1/0 (10gb) cache buffers chains 1,874,057,944 588,231 16,539 572097/15757 /367/10/0 cache buffers lru chain 4,499,478 3,228 122 3107/120/1/0 ----- Original Message ----- From: "Cary Millsap" To: Sent: Thursday, June 10, 2004 12:21 AM Subject: RE: see higher CPU usage after increase SGA > Zhu, > > I think what’s happening to you is strictly a function of how you’re > looking at your measurements. > > First, I like to distinguish between the terms “usage” and > “utilization.” (You’ve used them in your post as synonyms.) This > distinction helps me make a point. So, first, some definitions: > > Usage - the total duration for which a resource is “in use.” > > Utilization - usage divided by capacity for a specified time > interval. > > Now, “utilization” is a concept inherently susceptible to ratio fallacies. > This is because utilization, of course, IS a ratio. I think the ratio > fallacy that’s victimizing you right now is this one... > > Conjecture: If CPU utilization went up, then performance must have > gotten worse. > > This conjecture is false. It’s a commonly-believed rule of thumb, but it ’s > just not right. Here’s what I think has happened to you. When you reduced > the [so-called] physical I/O count, you probably reduced the total run-time > of your program. But of course, you have kept your CPU usage constant (you > haven’t reduced the LIO count by making your buffer cache bigger). So look > at the impact of what you did upon utilization. Remember, > > Utilization = (Usage) / (Capacity for a specified time interval). > > You have kept usage constant, but you’ve reduced the specified time > interval. Therefore, your utilization went up. > > The fallacy is that when CPU utilization goes up, it can also be an > indication that you’re simply able to get more work done now in the same > time interval as before. > > Now, my questions for you: > > 1. Did your action reduce your total response time by some small percentage > (maybe 5-6%)? > 2. What’s more important to you: (a) Utilization statistics? Or (b) total > runtime? > > I hope your answer to question 2 is (b), because (a) is a flawed metric; > utilization is NOT a reliable measure of the quality of PERFORMANCE. Answer > (b) does not leave you vulnerable to the ratio fallacies that (a) does. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > * Nullius in verba * > > Upcoming events: > - Performance Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 Boston > - SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver > - Hotsos Symposium 2005: March 6-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -----Original Message----- > From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] > On Behalf Of Mark W. Farnham > Sent: Wednesday, June 09, 2004 9:04 AM > To: oracle-l@freelists.org > Subject: RE: see higher CPU usage after increase SGA > > 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: > > 1) 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. > > 2) 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On > Behalf Of zhu chao > Sent: Wednesday, June 09, 2004 9:31 AM > To: oracle-l@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 - 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@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@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@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@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 -----------------------------------------------------------------