Re: why does a higher cost run faster?

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