From oracle-l-bounce@freelists.org Thu Dec 15 16:12:26 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jBFMCQYB026269 for ; Thu, 15 Dec 2005 16:12:26 -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 jBFMCOAX026258 for ; Thu, 15 Dec 2005 16:12:24 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B9F142568D0; Thu, 15 Dec 2005 17:12:18 -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 26830-06; Thu, 15 Dec 2005 17:12:18 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 38704257BE5; Thu, 15 Dec 2005 17:12:18 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 15 Dec 2005 17:12:15 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DF69F257BE4 for ; Thu, 15 Dec 2005 17:12:15 -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 26840-01 for ; Thu, 15 Dec 2005 17:12:15 -0500 (EST) Received: from usscmail7.hds.com (usscmail7.hds.com [63.74.235.18]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3FEF5257BEF for ; Thu, 15 Dec 2005 17:12:15 -0500 (EST) Received: from mail.hds.com (usscmail9 [10.1.6.230]) by usscmail7.hds.com (8.11.7p1+Sun/8.11.5) with ESMTP id jBFMCAG23618; Thu, 15 Dec 2005 14:12:10 -0800 (PST) Received: from usscceb102.corp.hds.com (USSCCNETSLB08-VLAN4.hds.com [10.1.6.68]) by mail.hds.com (8.11.5-p0-rfc19719/8.11.5) with ESMTP id jBFMCVb25263; Thu, 15 Dec 2005 14:12:31 -0800 (PST) Received: from USSCCEVS101.corp.hds.com ([10.1.52.224]) by usscceb102.corp.hds.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 15 Dec 2005 14:12:11 -0800 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: why does a higher cost run faster? Date: Thu, 15 Dec 2005 14:12:10 -0800 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: why does a higher cost run faster? Thread-Index: AcYBuWNKZ2yEwgIXQN2AX4WQkEZpNgAB/rhg From: "John Kanagaraj" To: , X-OriginalArrivalTime: 15 Dec 2005 22:12:11.0043 (UTC) FILETIME=[9880BF30:01C601C4] X-archive-position: 29214 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: john.kanagaraj@hds.com Precedence: normal Reply-To: john.kanagaraj@hds.com 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 Haroon, Apart from the excellent optimizer related information given by Wolfgang '10053' Breitling, you should be aware that since this is an Apps Database, you will need to have performed some setup prior to executing your query. Since you are querying MTL_SYSTEM_ITEMS_B for a particular Organization, you need to have already set your ORG_ID that will be used by the predicate which uses FND_PROFILE. I ask because your query returned 0 rows.... If not, then you will need to use the 'execute apps.Fnd_Client_Info.set_org_context('')' call to set this. Aaaand, as a result of this, your cost may vary and the whole question is moot since the query did not achieve what you wanted it to. Also, keep in mind that Oracle Apps uses a different (and sensible IMHO) scheme for determining which columns need Histograms (ORG_ID is usually a favorite one, and it seems that MTL_SYSTEM_ITEMS_B does have histograms defined for this column). Have a look at my paper "Judicious Use of Histograms for Oracle Applications Tuning" available from http://oubpb.com/requestwp.html where I deal with how this is done. Hth, John Kanagaraj <>< DB Soft Inc Phone: 408-970-7002 (W) Co-Author: Oracle Database 10g Insider Solutions http://www.samspublishing.com/title/0672327910 ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Haroon A. Qureshi Sent: Thursday, December 15, 2005 12:49 PM To: oracle-l@freelists.org Subject: why does a higher cost run faster? 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? thanks, haroon -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l