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:[EMAIL PROTECTED] >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: [EMAIL PROTECTED] > >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: [EMAIL PROTECTED] > >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: [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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [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).
