RE: why does a higher cost run faster?

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Thu, 15 Dec 2005 14:12:10 -0800
Message-ID: <BEE6A332AA61424EAE305CF89D6F75C81E6EE9@USSCCEVS101.corp.hds.com>

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('<your org>')' 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Haroon A. Qureshi Sent: Thursday, December 15, 2005 12:49 PM To: oracle-l_at_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

Received on Thu Dec 15 2005 - 16:12:26 CST