Re: why does a higher cost run faster?
- From: "Haroon A. Qureshi" <haroon@xxxxxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Thu, 15 Dec 2005 15:48:17 -0600
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 <breitliw@xxxxxxxxxxxxx>
>Subject: Re: why does a higher cost run faster?
>To: haroon@xxxxxxxxxxxx
>Cc: oracle-l@xxxxxxxxxxxxx
>
>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
>--
>//www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
