From oracle-l-bounce@freelists.org Thu Dec 15 15:50:08 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jBFLo3iI022985 for ; Thu, 15 Dec 2005 15:50:03 -0600 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 jBFLnbAX022802 for ; Thu, 15 Dec 2005 15:49:37 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EAFBD253B22; Thu, 15 Dec 2005 16:48:25 -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 22947-04; Thu, 15 Dec 2005 16:48:25 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5C848252F52; Thu, 15 Dec 2005 16:48:25 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 15 Dec 2005 16:48:23 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B33532530C6 for ; Thu, 15 Dec 2005 16:48:22 -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 22932-09 for ; Thu, 15 Dec 2005 16:48:22 -0500 (EST) Received: from omr4.netsolmail.com (omr4.netsolmail.com [216.168.230.140]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BD68E252A2E for ; Thu, 15 Dec 2005 16:48:21 -0500 (EST) Received: from vmms9.netsolmail.com (IDENT:mirapoint@[216.168.230.155]) by omr4.netsolmail.com (8.12.10/8.12.10) with ESMTP id jBFLmI5D024456; Thu, 15 Dec 2005 16:48:18 -0500 (EST) Received: from vmms9.netsolmail.com (localhost.netsolmail.com [127.0.0.1]) by vmms9.netsolmail.com (Mirapoint Messaging Server MOS 3.2.2-GA) with ESMTP id PRI45978; Thu, 15 Dec 2005 16:48:17 -0500 (EST) Message-Id: <200512152148.PRI45978@vmms9.netsolmail.com> Received: from 69.218.30.10 by vmms9.netsolmail.com (Mirapoint Messaging Server MOS 3.2.2-GA) with HTTP/1.1; Thu, 15 Dec 2005 15:48:17 -0600 Date: Thu, 15 Dec 2005 15:48:17 -0600 From: "Haroon A. Qureshi" Subject: Re: why does a higher cost run faster? To: breitliw@centrexcc.com Cc: oracle-l@freelists.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-archive-position: 29207 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: haroon@qureshi.name Precedence: normal Reply-To: haroon@qureshi.name X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Not scanned: please contact your Internet E-Mail Service Provider for details X-MailScanner-From: oracle-l-bounce@freelists.org i removed the function call and replaced it with the returned value. the plan is the same (full table scan) with the same performance. regarding the sys stats, we are running oracle 11i. the rule of thumb has been not to gather stats on the sys/system objects (along with it depends :) ). going with the it depends scenario, we've seen performance to be better without the stats on the system objects. the organization_id is a primary key in the table. the data is broken out below. the function call returns org_id 86. ORGANIZATION_ID COUNT(*) --------------- ---------- 86 172394 101 150699 102 31681 ---- Original message ---- >Date: Thu, 15 Dec 2005 14:20:17 -0700 >From: Wolfgang Breitling >Subject: Re: why does a higher cost run faster? >To: haroon@qureshi.name >Cc: oracle-l@freelists.org > >How many rows does the query actually return? The optimizer, based on >the statistics available to it (and its built-in assumptions) estimates >that 118186 rows will be returned and for that it figures a full table >scan to be more efficient. You can see that in the costs it assigned to >the full table access vs. the index access. >If the index access is so much faster that means that one of the >following is true: >a) the available statistics do not accurately reflect the reality of the >data distribution (maybe organization_id is skewed, then possibly a >histogram could help) >b) your are using Oracle 9i or later and have not collected system >statistics. This could be viewed as a subclass of point a. >c) the built-in assumptions are violated - e.g. predicate independence >(this particular assumption wouldn't apply in your case, but there are >others). >d) you hit an optimizer bug ( rather unlikely for your simply query, >except possibly the use of the pl/sql function in the predicate - is the >plan the same if you replace the function call with the value returned?). > > >Haroon A. Qureshi wrote: >> i have the following query in oracle apps: >> >> 1 select inventory_item_id code, description meaning, >> description >> 2 from mtl_system_items_b >> 3 where organization_id = fnd_profile.value('ORG_ID') >> 4 and 63 = 63 >> 5* order by 2 >> >> no rows selected >> >> Elapsed: 00:03:04.89 >> >> Execution Plan >> ---------------------------------------------------------- >> 0 >> SELECT STATEMENT Optimizer=CHOOSE (Cost=7794 Card=118186 >> Bytes=3663766) >> 1 0 >> SORT (ORDER BY) (Cost=7794 Card=118186 Bytes=3663766) >> 2 1 >> TABLE ACCESS (FULL) OF 'MTL_SYSTEM_ITEMS_B' (Cost=7073 >> Card=118186 Bytes=366 >> 3766) >> >> the cost is 7073. when i force it use a concatenated index on >> organization_id and description, the cost jumps to 110381 but >> runs instantaneously. >> >> SQL> start q1 >> >> no rows selected >> >> Elapsed: 00:00:00.04 >> >> Execution Plan >> ---------------------------------------------------------- >> 0 >> SELECT STATEMENT Optimizer=CHOOSE (Cost=110381 Card=118186 >> Bytes=3663766) >> 1 0 >> SORT (ORDER BY) (Cost=110381 Card=118186 Bytes=3663766) >> 2 1 >> TABLE ACCESS (BY INDEX ROWID) OF 'MTL_SYSTEM_ITEMS_B' >> (Cost=109660 Card=1181 >> 86 Bytes=3663766) >> 3 2 >> INDEX (RANGE SCAN) OF 'MTL_SYSTEM_ITEMS_B_N2' >> (NON-UNIQUE) (Cost=865 Card= >> 118186) >> >> any ideas on why that is? am i not gathering my stats correctly? >> >-- >Regards > >Wolfgang Breitling >Centrex Consulting Corporation >www.centrexcc.com >-- >http://www.freelists.org/webpage/oracle-l > > -- http://www.freelists.org/webpage/oracle-l