RE: why does a higher cost run faster?
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
- To: <haroon@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 Dec 2005 14:12:10 -0800
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Haroon A. Qureshi
Sent: Thursday, December 15, 2005 12:49 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
