Default optimizer_index_caching value DUMB ...

Why does Oracle use such silly default values for optimizer_index_caching and optimizer_index_cost_adj?

With optimizer_index_caching set to ZERO, we're to assume no index blocks are ever in memory? This is simply untrue. Why can't Oracle dynamically adjust this setting based on the ratio/percentage of index:data blocks in the cache -- maybe via v$bh?

And, with optimier_index_cost_adj set to 100, an index scan is supposed to be as expensive as a FTS? -- this just doesn't make sense either. Can't Oracle look at the ratio between db_file_scattered_reads and db_file_sequential_reads to figure out the correct value?

Maybe I'm missing something here, but I find that with the defaults I see all sorts of dumb hash joins where a nested loops (ie, RBO) path is much better. What numbers do you find work well and push the CBO into the NL route much more?

Thanks in advance,

Domenic Received on Tue Oct 26 2004 - 09:01:47 CDT