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 <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
