From oracle-l-bounce@freelists.org Tue May 24 12:38:45 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4OHcjN7023089 for ; Tue, 24 May 2005 12:38:45 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4OHciNi023085 for ; Tue, 24 May 2005 12:38:44 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7CDF41B1F9D; Tue, 24 May 2005 11:35:48 -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 10435-07; Tue, 24 May 2005 11:35:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EC60B1B1F5B; Tue, 24 May 2005 11:35:47 -0500 (EST) Message-ID: <42935776.5050005@centrexcc.com> Date: Tue, 24 May 2005 10:33:58 -0600 From: Wolfgang Breitling Organization: Centrex Consulting Corporation User-Agent: Mozilla Thunderbird 0.7.2 (Windows/20040707) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Christo Kutrovsky Cc: Oracle-L@freelists.org Subject: Re: 10g System statistics - single and multi References: <52a152eb0505171232556f3711@mail.gmail.com> <428B9D0B.3010600@centrexcc.com> <52a152eb0505181315745367ed@mail.gmail.com> <6.2.0.14.2.20050518204409.04085498@pop.centrexcc.com> <52a152eb050519064058d7eafb@mail.gmail.com> <52a152eb05051913271c206d48@mail.gmail.com> <428D0097.4080606@centrexcc.com> <52a152eb05052011252d525a2a@mail.gmail.com> <6.2.1.2.2.20050520144430.10b6f5b8@pop.centrexcc.com> <6.2.1.2.2.20050520224821.101b4500@pop.centrexcc.com> <52a152eb050524074946d560f@mail.gmail.com> In-Reply-To: <52a152eb050524074946d560f@mail.gmail.com> Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-UCIT-MailScanner-Information: Please contact IT Help Desk at (403) 220-5555 for more information X-UCIT-MailScanner: Found to be clean X-UCIT-MailScanner-From: breitliw@centrexcc.com X-archive-position: 20189 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: breitliw@centrexcc.com Precedence: normal Reply-To: breitliw@centrexcc.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 Christo Kutrovsky wrote: > Woflfgang, > > It was long weekend here, holidays on Monday. Sorry for delay. Same here. I'm just 2 hours (earth rotational delay) west of you, but same country. >>I have to disagree again. I just did a mathematical transformation of the >>cost formula as documented in the Performance Tuning Guid > > > I was refering to the timing differences, not formula wise. Given that > FTS will have mostly sequencial multi-reads, while range scans will > have random single read, the reduction factor will be off by a > significant factor. > I tend to think about "cost" as time measured in "sreadtim" units. > We are talking averages here. Assuming that systems statistics are gathered over a representative workload, the sreadtm, mreadtm, and mbrc readings represent system wide averages for what actually happened during that time - with all caveats regarding averages. Therefore, filling in those values into the cost formula gives the best approximation of the elapsed time for the sql. There are many more areas where this estimate can be wrong than the small differences in different reads - beginning with assumptions by the cbo regarding the data distribution and the selectivity of the predicates to the overestimation of the clustering factor to the effect of caching in the buffer pool. > "oltp" test: > Your timings seem to be relativelly low (i.e. too fast). This would be > due to disk locality. In my OS side testing, i've done a test with > random IO through the whole disk drive, and random IO through 1/3 of > the drive. The difference is about 3 times in IO capacity (forgot to > get responce time results, but I assume same difference). Thus I think > if you were to do IO over the whole array you avg times for single > would the very least double, which would make the difference > significant. I am not sure how you could test this easyly on Oracle > side. One way I can think off, if your file system on the array in > question is relativelly empty, is to create a tablespace with multiple > large files until it fills up the entire file system. Oracle will then > use it's round-robin extent allocation and the data will be somewhat > spread over the whole array. I am putting this test on my "todo" list > too. My contention is that if you have a database which spreads out that far, your mreadtm will go up too as the heads will have to move greater distances as well to serve random multiblock reads by different sessions. > RAC and system stats: > > CPUspeed is the one which should have multiple values per instance, I > agree, someone just forgot RAC, or did not have time to implement. For > sread/mread I also agree, those should be per segment. > In that same system in mind, we're already splitting the data on > different arrays based on access patterns and it will not make sense > to use the same system stats to cost both set of objects. > I disagree with gathering sreadtm, mreadtm, and mbrc by segment. That would generate too much data which the CBO has to process at parse time for too little gain. There are other areas in the CBO which have the potential for much greater payback or less overhead. Like getting a grip on the caching efficiency of different segments or dynamically correcting stark cardinality errors a-la profiles. -- Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l