Re: too low optimizer_index_cost_adj causing bizarre index choice

There seems to be a perfectly good theoretical reason for this. But it would be interesting to know:

    Number of blocks below HWM
    Setting for db_file_multiblock_read_count     Maximum usable value for db_file_mbrc     The cost given by Explain Plan for the tablescan

before I confuse the issue further by expounding a hypothesis that may be totally misleading.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

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

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 09 May 2002 14:30

|Good morning listers,
|
|Some weeks ago I noticed a query plan that was using a
|bizarre index choice and opened a TAR. Here is that
|query:
|
|select DSS_EMPR.V_CLAIM_EMPR_HX.AMT_COPAY from
|DSS_EMPR.V_CLAIM_EMPR_HX where
|DSS_EMPR.V_CLAIM_EMPR_HX.BATCH_NUM between
|to_date('2000-01-01','yyyy-mm-dd') and
|to_date('2000-01-31','yyyy-mm-dd');
|
|Here is the query plan:
|
| SELECT STATEMENT (all_rows) Cost
|(4924,636953,12739060)
|
| 1 0 1 2
| TABLE ACCESS (analyzed) DSS_EMPR T_CLAIM_EMPR_HX
|(by index rowid) Cost (4924,636953,12739060)
|
| 2 1 1
| BITMAP CONVERSION (to rowids)
|
| 3 2 1
| BITMAP INDEX DSS_EMPR X_CLAIM_EMPR_HX_N01
|(full scan)
|
|
|The index x_claim_empr_hx_n01 is on the phmcy_gid
|column of the t_claim_empr_hx table.
|
|Now, here is the crux of the matter: phmcy_gid is
|referred to nowhere in the query, not in select nor in
|where nor even in order by.
|
|Worked through the tar with Oracle and they advised
|that Oracle can and will cost ALL indexes during a
|plan parse and eval, so it became a matter of
|discovering why the index was being incorrectly
|costed.
|
|Remembered that our optimizer_index_cost_adj was set
|to 1 (don't ask). When I upped this value to 2 or more
|and reran the query, it returned the appropriate FTS
|plan.
|
|hth,
|
|Jack Silvey
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).