Message-ID: <F001.005D0418.20030917140940@fatcity.com>
Date: Wed, 17 Sep 2003 14:09:40 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: ml-errors@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: John Kanagaraj <john.kanagaraj@hds.com>
Subject: RE: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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@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@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@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@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@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@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).