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