Oracle-L: Confuzzled on OPTIMiZER_INDEX_COST

From: Thomas Jeff <jeff.thomas_at_thomson.net>
Date: Wed, 17 Sep 2003 12:54:56 -0800
Message-ID: <F001.005D0406.20030917125456@fatcity.com>

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). Received on Wed Sep 17 2003 - 15:54:56 CDT

Confuzzled on OPTIMiZER_INDEX_COST_ADJ