Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: why does a higher cost run faster?

Re: why does a higher cost run faster?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 15 Dec 2005 23:21:58 -0000
Message-ID: <004901c601ce$58698940$6902a8c0@Primary>

Your information is not self-consistent:

> The function call returns org_id 86.
> ORGANIZATION_ID COUNT(*)
> --------------- ----------
> 86 172394
> 101 150699

BUT
>>> 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

So the function cannot be returning 86.

Coincidentally, if it were to return 86, then the execution plan would have got the cardinality about right (118,186 compared to 172,394)

>>> SELECT STATEMENT Optimizer=CHOOSE (Cost=7794 Card=118186 Bytes=3663766)

Oracle picks the tablescan because it is expecting a LOT of data - but the function-call is supplying a value that returns no data, so obviously an indexed access path will be the quickest way of finding nothing, and a pretty slow way to find 118,000 (scattered) rows.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 29th Nov 2005

>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_at_centrexcc.com>
>>Subject: Re: why does a higher cost run faster?
>>To: haroon_at_qureshi.name
>>Cc: oracle-l_at_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
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 15 2005 - 17:22:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US