Db_file_multiblock_read_count
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.
T.
Re: Db_file_multiblock_read_count
Quote:
Originally posted by AJW_ID01
So, what would anyone else do in my position?
Does anyone have any opinion encompassing the bringing into play of "optimizer_index_cost_adj" (which is currently set to the default of 100)?
If I were in your position, I would run a quick test at the session level with optimizer_index_cost_adj around 25 or so. I'd run some of the queries that would obviously use the index and see if that helped (Make sure they are reparsed). I suspect you will get more bang for the buck with this setting.
If not, decrease the dbfmbrc to 8. Personally, I like knowing that IF a FTS has to happen it will happen 16 (or ever 32) blocks at a time.
Mileage may vary.