RE: Confuzzled on OPTIMiZER_INDEX_COST_ADJ

John,

OPTIMIZER_INDEX_CACHING is set to 0 in both databases, all other parameters are also the same. We also kept OPTIMIZER_FEATURES_ENABLE at 8.1.7 in both databases. I tried the 10053 trace but I'm not getting any results in the trace file -- just the query?

Thanks.

-----Original Message-----
Sent: Wednesday, September 17, 2003 5:10 PM To: Multiple recipients of list ORACLE-L

Thomas,

What is OPTIMIZER_INDEX_CACHING set to? This one also influences the CBO as well as a host of other parameters (including SORT_AREA_SIZE, DB_FILE_MULTIBLOCK_READ_COUNT, .. etc). As well, Histograms and other stats can influence FTS vs Indexed reads. For a complete list of parameters that influence the CBO, you can look up my paper at http://www.geocities.com/john_sharmila/links.htm or look at a 10053 trace...

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: Thomas Jeff [mailto:jeff.thomas_at_thomson.net]
>Sent: Wednesday, September 17, 2003 1:55 PM
>To: Multiple recipients of list ORACLE-L
>Subject: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
>
>
>We recently upgraded a production DB to 9.2.0.2 The
>equivalent test tier
>was upgraded
>last month. After the production upgrade, one application immediately
>began experiencing
>performance issues for a given package where they did not
>encounter such
>problems in test.
>
>The problem was with one simple SQL statement within the package:
>
>SELECT * FROM PARTS WHERE PART_NO = :b1
>
>In production, we are seeing full table scans for this
>statement while in
>test it's using
>an index. We checked stats, indexes, etc, and they are all
>the same. So
>I then compared
>the optimizer parameters and it turns out that in test,
>optimizer_index_cost_adj is set to 100,
>but in production it's set to 80. If I do an alter session set
>optimizer_index_cost_adj to
>100 in prod, the statement runs exactly as in test, i.e, with
>index access.
>
>My understanding is that LOWER values of
>optimizer_index_cost_adj will bias
>the CBO towards
>index probes. So, this situation has me confused. What am I
>missing here?
>
>Thanks!
>
>--------------------------------------------
>Jeffery D Thomas
>DBA
>Thomson Information Services
>Thomson, Inc.
>
>Email: jeff.thomas_at_thomson.net
>
>Indy DBA Master Documentation available at:
>http://gkmqp.tce.com/tis_dba
>--------------------------------------------
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Thomas Jeff
> INET: jeff.thomas_at_thomson.net
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thomas Jeff
  INET: jeff.thomas_at_thomson.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).