From oracle-l-bounce@freelists.org Sat May 21 01:38:02 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4L6c1is000523 for ; Sat, 21 May 2005 01:38:01 -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 j4L6c14Z000519 for ; Sat, 21 May 2005 01:38:01 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6BFF31956CA; Sat, 21 May 2005 00:35:08 -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 07459-07; Sat, 21 May 2005 00:35:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CD6D11956DE; Sat, 21 May 2005 00:35:07 -0500 (EST) Message-Id: <6.2.1.2.2.20050520224821.101b4500@pop.centrexcc.com> Date: Fri, 20 May 2005 23:33:19 -0600 To: Oracle-L@freelists.org From: Wolfgang Breitling Subject: Re: 10g System statistics - single and multi Cc: Christo Kutrovsky In-Reply-To: <6.2.1.2.2.20050520144430.10b6f5b8@pop.centrexcc.com> References: <52a152eb0505171232556f3711@mail.gmail.com> <52a152eb050518070242a720cd@mail.gmail.com> <428B5F80.1020407@centrexcc.com> <52a152eb05051811256b9b58da@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> Mime-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-archive-position: 20066 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 Ok, here is my test. To verify my proposition that "sequential" single block reads become random single block reads when many user sessions are involved I borrowed Tom Kyte's big_table and oltp_style (random reads) and dw_style (FTS = sequential reads) procedures from his "Effective Oracle by Design" book. I created 3 separate "big_tables" of 400,000 rows each. Then I ran two tests. One to run several concurrent sessions invoking the oltp_style procedure, one of which I traced with level 8. For the other test I ran several concurrent sessions invoking the dw_style procedure setting db_file_multiblock_read_count to between 12 and 16 except for the one session that is being traced where dfmrc is set to 1, generating "sequential" single block reads. Below are some of the db file sequential read wait lines from the traces: oltp_style = "random reads". Note the p2 (block #) values are not consecutive (p1=4 is the index datafile, p1=5 is the data datafile): WAIT #23: nam='db file sequential read' ela= 6717 p1=4 p2=1088 p3=1 WAIT #23: nam='db file sequential read' ela= 2415 p1=5 p2=15448 p3=1 WAIT #23: nam='db file sequential read' ela= 7870 p1=4 p2=262 p3=1 WAIT #23: nam='db file sequential read' ela= 6254 p1=5 p2=11093 p3=1 WAIT #23: nam='db file sequential read' ela= 585 p1=4 p2=341 p3=1 WAIT #23: nam='db file sequential read' ela= 4728 p1=5 p2=28 p3=1 WAIT #23: nam='db file sequential read' ela= 5116 p1=4 p2=519 p3=1 WAIT #23: nam='db file sequential read' ela= 6209 p1=5 p2=12005 p3=1 WAIT #23: nam='db file sequential read' ela= 242 p1=4 p2=507 p3=1 WAIT #23: nam='db file sequential read' ela= 6678 p1=5 p2=11930 p3=1 WAIT #23: nam='db file sequential read' ela= 4165 p1=4 p2=852 p3=1 WAIT #23: nam='db file sequential read' ela= 6008 p1=5 p2=14020 p3=1 WAIT #23: nam='db file sequential read' ela= 184 p1=4 p2=979 p3=1 WAIT #23: nam='db file sequential read' ela= 6647 p1=5 p2=14796 p3=1 WAIT #23: nam='db file sequential read' ela= 182 p1=4 p2=794 p3=1 WAIT #23: nam='db file sequential read' ela= 3350 p1=5 p2=13669 p3=1 WAIT #23: nam='db file sequential read' ela= 178 p1=4 p2=755 p3=1 WAIT #23: nam='db file sequential read' ela= 3357 p1=5 p2=13436 p3=1 WAIT #23: nam='db file sequential read' ela= 12307 p1=4 p2=249 p3=1 WAIT #23: nam='db file sequential read' ela= 6281 p1=5 p2=11016 p3=1 WAIT #23: nam='db file sequential read' ela= 320 p1=4 p2=724 p3=1 WAIT #23: nam='db file sequential read' ela= 13147 p1=5 p2=13242 p3=1 WAIT #23: nam='db file sequential read' ela= 177 p1=4 p2=898 p3=1 WAIT #23: nam='db file sequential read' ela= 200 p1=5 p2=14310 p3=1 WAIT #23: nam='db file sequential read' ela= 190 p1=4 p2=529 p3=1 WAIT #23: nam='db file sequential read' ela= 6093 p1=5 p2=12056 p3=1 WAIT #23: nam='db file sequential read' ela= 3903 p1=4 p2=896 p3=1 WAIT #23: nam='db file sequential read' ela= 3671 p1=5 p2=14295 p3=1 WAIT #23: nam='db file sequential read' ela= 21533 p1=5 p2=14770 p3=1 WAIT #23: nam='db file sequential read' ela= 1679 p1=5 p2=12434 p3=1 WAIT #23: nam='db file sequential read' ela= 183 p1=4 p2=917 p3=1 WAIT #23: nam='db file sequential read' ela= 4210 p1=5 p2=14410 p3=1 WAIT #23: nam='db file sequential read' ela= 186 p1=4 p2=996 p3=1 WAIT #23: nam='db file sequential read' ela= 15406 p1=5 p2=14903 p3=1 WAIT #23: nam='db file sequential read' ela= 13604 p1=4 p2=901 p3=1 WAIT #23: nam='db file sequential read' ela= 1629 p1=5 p2=14326 p3=1 WAIT #23: nam='db file sequential read' ela= 194 p1=4 p2=336 p3=1 WAIT #23: nam='db file sequential read' ela= 178 p1=5 p2=11525 p3=1 WAIT #23: nam='db file sequential read' ela= 2643 p1=4 p2=1091 p3=1 WAIT #23: nam='db file sequential read' ela= 11305 p1=5 p2=15423 p3=1 WAIT #23: nam='db file sequential read' ela= 4907 p1=4 p2=258 p3=1 WAIT #23: nam='db file sequential read' ela= 34664 p1=5 p2=11068 p3=1 WAIT #23: nam='db file sequential read' ela= 179 p1=4 p2=746 p3=1 WAIT #23: nam='db file sequential read' ela= 3800 p1=5 p2=13380 p3=1 WAIT #23: nam='db file sequential read' ela= 188 p1=4 p2=1108 p3=1 WAIT #23: nam='db file sequential read' ela= 5560 p1=5 p2=15570 p3=1 WAIT #23: nam='db file sequential read' ela= 186 p1=4 p2=964 p3=1 WAIT #23: nam='db file sequential read' ela= 554 p1=5 p2=14703 p3=1 dw_style = "sequential reads". Not the consecutive p2 (block#) values (with the occasional gap because the block is already in the buffer pool): WAIT #41: nam='db file sequential read' ela= 187 p1=5 p2=11426 p3=1 WAIT #41: nam='db file sequential read' ela= 12078 p1=5 p2=11428 p3=1 WAIT #41: nam='db file sequential read' ela= 250 p1=5 p2=11429 p3=1 WAIT #41: nam='db file sequential read' ela= 268 p1=5 p2=11430 p3=1 WAIT #41: nam='db file sequential read' ela= 11880 p1=5 p2=11431 p3=1 WAIT #41: nam='db file sequential read' ela= 659 p1=5 p2=11432 p3=1 WAIT #41: nam='db file sequential read' ela= 338 p1=5 p2=11433 p3=1 WAIT #41: nam='db file sequential read' ela= 259 p1=5 p2=11434 p3=1 WAIT #41: nam='db file sequential read' ela= 1403 p1=5 p2=11435 p3=1 WAIT #41: nam='db file sequential read' ela= 710 p1=5 p2=11436 p3=1 WAIT #41: nam='db file sequential read' ela= 208 p1=5 p2=11437 p3=1 WAIT #41: nam='db file sequential read' ela= 660 p1=5 p2=11438 p3=1 WAIT #41: nam='db file sequential read' ela= 11728 p1=5 p2=11439 p3=1 WAIT #41: nam='db file sequential read' ela= 455 p1=5 p2=11441 p3=1 WAIT #41: nam='db file sequential read' ela= 306 p1=5 p2=11442 p3=1 WAIT #41: nam='db file sequential read' ela= 302 p1=5 p2=11443 p3=1 WAIT #41: nam='db file sequential read' ela= 224 p1=5 p2=11445 p3=1 WAIT #41: nam='db file sequential read' ela= 584 p1=5 p2=11446 p3=1 WAIT #41: nam='db file sequential read' ela= 10497 p1=5 p2=11447 p3=1 WAIT #41: nam='db file sequential read' ela= 475 p1=5 p2=11448 p3=1 WAIT #41: nam='db file sequential read' ela= 219 p1=5 p2=11449 p3=1 WAIT #41: nam='db file sequential read' ela= 393 p1=5 p2=11450 p3=1 WAIT #41: nam='db file sequential read' ela= 15340 p1=5 p2=11451 p3=1 WAIT #41: nam='db file sequential read' ela= 264 p1=5 p2=11452 p3=1 WAIT #41: nam='db file sequential read' ela= 305 p1=5 p2=11453 p3=1 WAIT #41: nam='db file sequential read' ela= 235 p1=5 p2=11454 p3=1 WAIT #41: nam='db file sequential read' ela= 4683 p1=5 p2=11455 p3=1 WAIT #41: nam='db file sequential read' ela= 186 p1=5 p2=11456 p3=1 WAIT #41: nam='db file sequential read' ela= 618 p1=5 p2=11457 p3=1 WAIT #41: nam='db file sequential read' ela= 265 p1=5 p2=11458 p3=1 WAIT #41: nam='db file sequential read' ela= 5558 p1=5 p2=11459 p3=1 WAIT #41: nam='db file sequential read' ela= 197 p1=5 p2=11460 p3=1 WAIT #41: nam='db file sequential read' ela= 294 p1=5 p2=11461 p3=1 WAIT #41: nam='db file sequential read' ela= 241 p1=5 p2=11463 p3=1 WAIT #41: nam='db file sequential read' ela= 188 p1=5 p2=11464 p3=1 WAIT #41: nam='db file sequential read' ela= 234 p1=5 p2=11466 p3=1 WAIT #41: nam='db file sequential read' ela= 1990 p1=5 p2=11467 p3=1 WAIT #41: nam='db file sequential read' ela= 201 p1=5 p2=11468 p3=1 WAIT #41: nam='db file sequential read' ela= 256 p1=5 p2=11470 p3=1 WAIT #41: nam='db file sequential read' ela= 43232 p1=5 p2=11472 p3=1 WAIT #41: nam='db file sequential read' ela= 486 p1=5 p2=11473 p3=1 WAIT #41: nam='db file sequential read' ela= 1313 p1=5 p2=11474 p3=1 WAIT #41: nam='db file sequential read' ela= 6266 p1=5 p2=11475 p3=1 And these are the averages: oltp_style "random" single block reads: Average of ela 1815.234363 dw_style "sequential" single block reads: Average of ela 1454.069431 I would not call the difference in the average significant. However, to be honest, the oltp_style average is not from all entries, only from the first 65535 lines that fit into excel. But I believe the excerpt from the trace lines shows that the dw_style elapsed times are just as variable as the oltp_style ones. To complete the information. The test was performed on Oracle 10.1.0.3 on AIX (10.1.0.4 is not out on AIX yet :-() 5.3 with the tables allocated from a 8K blocksize LMT tablespace with 1M uniform extent size and no ASSM. The datafiles reside in a EMC Clariion CX700. At 03:15 PM 5/20/2005, Wolfgang Breitling wrote: >At 12:25 PM 5/20/2005, Christo Kutrovsky wrote: > > >the cpu cost is added on, not multiplied > >oops > > > > > It doesn't matter whether they are random or not, single block reads are > > > single block reads. > > > >I disagree with this one. Single block reads from dfmrc = 1 are very > >different then single block reads to access a table from a range scan. > >The range scan single block reads can be sequencial, on a freshly > >built index. > >I disagree and raise you one :-) > >I presume with "sequential read" you mean reads of consecutive blocks. If >we agree on that definition then it is my proposition that such a thing >exists only in an isolated single-user lab environment. If you consider a >SAN that is accessed by multiple users on potentially multiple databases >(and maybe other applications), then from the view of the disk read/write >head there is no such thing as a sequential read. For your individual >session you may think that you are reading consecutive blocks, but by the >time you issue the next read for the next, supposedly adjacent, block, the >chances that the head is still where you left it after your previous read >are remote. So from the overall system point of view, it becomes a random >read. >After I wrote this I had a terrible sense of deja-vu, hopped over to asktom >and sure enough found this quote: >"Even if it was, that would only make a difference in a single user >system! do >you really think the heads will remain positioned where you left them after a >read? As soon as you are done reading, the OS is off servicing some other >read >-- thats why controllers and such have their own CPU's in effect. There is >perhaps a 0.00001% chance the head will be where you left it in a multi-user >system." >http://asktom.oracle.com/pls/ask/f?p=4950:8:24292::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:730289259844, > >To be precise, Tom's quote is in response to a question on the demerits of >multiple extents, but I believe it is equally valid in this context. > >If I find some time over the long weekend I will try and put a testcase >together to validate our (Tom's and mine) assertion. > > > >Time exectation are the following: > >- sreadtim for sequencial = 0.2-0.5 ms > >- sreadtim for random = 5-10 ms Regards Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l