From oracle-l-bounce@freelists.org Wed May 18 11:34:54 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4IGYsbt024320 for ; Wed, 18 May 2005 11:34:54 -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 j4IGYr4Z024316 for ; Wed, 18 May 2005 11:34:53 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A94B4195835; Wed, 18 May 2005 10:32: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 15976-08; Wed, 18 May 2005 10:32:03 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 245521957FD; Wed, 18 May 2005 10:32:03 -0500 (EST) Message-ID: <428B5F80.1020407@centrexcc.com> Date: Wed, 18 May 2005 09:30:08 -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> <428A57C7.7040002@centrexcc.com> <52a152eb05051713544f014099@mail.gmail.com> <428A5D27.40908@centrexcc.com> <52a152eb050517142673be4b33@mail.gmail.com> <52a152eb050517145930f4deff@mail.gmail.com> <6.2.1.2.2.20050517173110.04255de0@pop.centrexcc.com> <52a152eb050518070242a720cd@mail.gmail.com> In-Reply-To: <52a152eb050518070242a720cd@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: 19852 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 I would say that concluding from your example that "in all modern SANs, unless your dfmbrc is such that you will read > 512 Kb, your mread will be lower then sread" is a rather bold statement. Excluding caching, mreadtm should always be higher than sreadtm since on average it should take the same amount of time to position the head and wait for the rotational delay until the data shows up under the head. But it takes longer to transmit nK of data than mK when n > m I did a quick test, setting db_file_multiblock_read_count to 1, 2, 8, 16, 32, 64, 128, and 256 for a table on an 8K blocksize LMT with uniform 4M extends stored on an IBM ESS (Shark) 700. These are the numbers from the ELA of the extended 10046 trace sequential and scattered read wait events: 1 539.095 2 682.760 3 795.782 4 911.000 6 1066.778 7 1171.429 8 1274.440 9 1824.500 10 1912.000 11 1994.800 15 2569.000 16 2812.132 25 3794.500 26 3880.000 31 4688.000 32 4790.857 36 5218.000 38 5260.000 40 5332.667 56 7578.000 57 7565.833 64 8454.308 102 12553.500 108 13349.000 128 15635.545 I failed to clear the buffer between reads which is why some "odd" counts show up that to not coincide with any of the dfmrc settings. But in general, with the exception of multiblock reads 56 and 57, more blocks take longer to read than fewer, and thus mreadtm should be higher than sreadtm. If system statistics are gathered over a long enough representative workload, mreadtm should definitely come out higher than sreadtm. If mreadtm is consistently less than sreadtm then I would investigate why that is. Christo Kutrovsky wrote: > I've profiled my SAN. IBM FastT 700 > > Stripe size plays very little in sequencial or random IO. Actually > larger stripe size is a bit better. > > Sequencial reads at sizes between 512 bytes to 128 Kb are under 1 ms. > Compared to random IO been always in the 6ms range. > > So in all modern SANs, unless your dfmbrc is such that you will read > > 512 Kb, your mread will be lower then sread. > > P.S. > Not sure why you send this to me only, and not to the list. > > On 5/17/05, Wolfgang Breitling wrote: > >>Actually, depending on your SAN, it could just as easily be reverse. If you >>have a large db_file_multiblock_read_cound (I always refer to it as dfmrc, >>taking the initials of each word) the SAN microcode could very well detect >>a sequential read pattern and prefetch the next chunk so that cumulatively >>the average multiblock read count comes out very fast because later reads >>are serviced from the cache and do no real physical IO, wheras if you leaf >>dfmrc at a moderate value of say 32, it may be below the prefetch radar. >>Christian Antognini has an interesting chart on the relationship between >>dfmrc and IO time on different systems. Unfortunately there is not data >>about the different storage architectures on those systems. >>If prefetch is not a factor, stripe size can come into the equation. If >>dfmrc is greater than the stripe size, the average IO time goes up >>depending on the # of physical disks involved. The IO rate is spread more >>evenly, avoiding hot disks, but a single large IO request can get slower. >> -- Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l