Just curious on this one:
I have an application which is routinely choosing full table scans over a (by research) 100-fold more efficient index access path.
I have undertaken investigation of index_caching levels, and setting optimizer_index_caching to 20% (which is a rough estimation of what proportion of indexes are cached at any point in time across the whole application), does not change choice of path.
The maximum db_file_multiblock_read_count is set at 16, and looking in trace files, the maximum multiblock read IS 16. However... this is achieved very, very rarely. The 'average' multiblock_read_count would be nearer 10.
If I set db_file_multiblock_read_count at the session level to be just '1' less than the current 16, indexes come into more widespread use for almost everything.
So, what would anyone else do in my position?
[ I'm basically being hurried to make ammendments to settings to try and bring indexes into use - without reverting to HINTS and RULE mode operations. The business are howling for an immediate improvements of some processes. ]
My gut feeling is to reduce multiblock read to something less than the current 16 and uplift optimizer_index_caching from the current default (0) to 20.
Does anyone have any opinion encompassing the bringing into play of "optimizer_index_cost_adj" (which is currently set to the default of 100)?
I thought I had another point to make from my findings, but can't remember what it was now, and being interested in earlier rather than later feedback, I think I'll end here and fire it off for comments.
Click Here to Expand Forum to Full Width