From oracle-l-bounce@freelists.org Tue May 17 19:33:08 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4I0X89c012217 for ; Tue, 17 May 2005 19:33:08 -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 j4I0X84Z012213 for ; Tue, 17 May 2005 19:33:08 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 123C21946B6; Tue, 17 May 2005 18:30:19 -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 31430-06; Tue, 17 May 2005 18:30:19 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 859B4194629; Tue, 17 May 2005 18:30:18 -0500 (EST) Message-Id: <6.2.1.2.2.20050517171232.102022e0@pop.centrexcc.com> Date: Tue, 17 May 2005 17:28:29 -0600 To: kutrovsky.oracle@gmail.com From: Wolfgang Breitling Subject: Re: 10g System statistics - single and multi Cc: oracle-l In-Reply-To: <52a152eb0505171516306c0ac8@mail.gmail.com> References: <52a152eb0505171232556f3711@mail.gmail.com> <428A57C7.7040002@centrexcc.com> <52a152eb05051713544f014099@mail.gmail.com> <428A5D27.40908@centrexcc.com> <52a152eb0505171516306c0ac8@mail.gmail.com> Mime-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-archive-position: 19813 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.2 required=5.0 tests=AWL,LINES_OF_YELLING, UPPERCASE_25_50 autolearn=no version=2.63 Don't forget the cpu cost. What is your cpuspeed? Using your data and a cpuspeed of 1000000000 to marginalize/eliminate the cpu cost factor I get: SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ ---------- ------------------------------ SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 05-17-2005 17:15 SYSSTATS_INFO DSTOP 05-17-2005 17:15 SYSSTATS_INFO FLAGS 1 SYSSTATS_MAIN CPUSPEEDNW 400.897016 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM 2 SYSSTATS_MAIN MREADTIM 16 SYSSTATS_MAIN CPUSPEED 1000000000 SYSSTATS_MAIN MBRC 128 SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN SLAVETHR 13 rows selected. 17:15:53 ora101.scott> set autot traceonly explain 17:16:03 ora101.scott> select * from newkutro 17:16:16 2 / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6251 Card=2000 Bytes=26000) 1 0 TABLE ACCESS (FULL) OF 'NEWKUTRO' (TABLE) (Cost=6251 Card=2000 Bytes=26000) exactly what is expected (because of _table_scan_cost_plus_one=TRUE). If I lower the cpuspeed to 1200, I get 17:21:07 ora101.scott> select * from newkutro; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6548 Card=2000 Bytes=26000) 1 0 TABLE ACCESS (FULL) OF 'NEWKUTRO' (TABLE) (Cost=6548 Card=2000 Bytes=26000) Practically the same as you At 04:16 PM 5/17/2005, Christo Kutrovsky wrote: >100 000 / 128 * 16/2 =3D 6250 > >select * from newkutro > >reports a cost of 6549. > >Also by halfing MBRC almost doubles the cost. >By doubling SREADTIM it almost halves the cost, but by a different amount. Don't ignore the CPU cost "factor" - mathematically speaking not a factor but a summand. And it may not be fixed either, even for a fixed cpu speed. The estimated cpu cycles may be different for different # of estimated (physical) IO. >To me it doesnt make sense to check for mread > sread . opinions ? Whether it makes sense or not, that's the current rules. There are many more "sanity checks" and assumptions in the CBO, some of which I would question much more than this one. Regards Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l