The following is from the Tim Gorman paper · OPTIMIZER_INDEX_CACHING This initialization parameter represents a percentage value, ranging between the values of 0 and 99. The default value of 0 indicates to the CBO that 0% of database blocks accessed using indexed access can be expected to be found in the Buffer Cache of the Oracle SGA. This implies that all index accesses will require a physical read from the I/O subsystem for every logical read from the Buffer Cache, also known as a 0% hit ratio on the Buffer Cache. This parameter applies only to the CBO's calculations of accesses for blocks in an index, not for the blocks in the table related to the index. · OPTIMIZER_INDEX_COST_ADJ This initialization parameter is also a percentage value, ranging between 1 and 10000, representing a comparison between the relative cost of physical I/O requests for indexed access and full table-scans. The default value of 100 indicates to the cost-based optimizer that indexed access is 100% as costly (i.e., equally costly) as FULL table scan access. As it turns out, the default values for each of these parameters are wildly unsuitable and unrealistic. I'll prove this assertion later in this paper, but for now, suffice to say that OPTIMIZER_INDEX_CACHING should be set to 90 and OPTIMIZER_INDEX_COST_ADJ should be set to a value which usually ranges between 10 and 50 for most online transaction processing (OLTP) systems, For data warehousing or other decision-support systems (DSS), it might be prudent to simply set this parameter to 50.
-----Original Message----- Sent: Thursday, March 07, 2002 2:54 PM To: Multiple recipients of list ORACLE-L just changed index_cost_adj from 100 to 1 what is index_caching? -----Original Message----- Sent: Thu, March 07, 2002 3:04 PM To: Multiple recipients of list ORACLE-L have you tried adjusting optimzer_index_caching and optimizer_index_cost_adj? -----Original Message----- Sent: Thursday, March 07, 2002 1:23 PM To: Multiple recipients of list ORACLE-L I work in a dev shop - most of the sql is canned and pretty basic. We've been running CBO in all of our dev environments, but we have a few long txns that just take forever. At the request of some savvy developers, I turned on RBO, and it brought down execution times dramatically. I've been analyzing affected tables often (we do a lot of bulk load/unload for testing), and have played with partitioning and clustering, particularly on one table that's just a dog. CBO will always do a FTS where RBO uses the PK to retrieve data. Where to go next? I've been unable to alter the costs dramatically enough to make any real difference in execution time. thx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
