** We've been told that the table is 31G and the index 39G - but we haven't been told the values of BLOCKS and LEAF_BLOCKS.
For the index below are stats:
BLOCKS( from dba_segments) - 5088000
LEAF_BLOCKS(from dba_indexes) - 5219040
For the table below are the stats:
BLOCKS(from dba_segments) - 4409600
BLOCKS(from dba_tables) - 4353190
Jonathan Lewis wrote:
In the previous post I asked about (particularly) the optimizer_index_XXX parameters because an obvious explanation for the anomaly would be that the optimizer_index_cost_adj had been set to a value of about 1. F_BLOCKS.
These are the knobs that try men's souls.
optimizer_index_caching |integer |90 optimizer_index_cost_adj |integer |1
Bingo! (For those who don't have the relevant 1960's background, that's Englilsh for "I win!"
With a leaf-block count of 5,219,000 the cost of a full scan would normally be about 5.2M+; with optimizer_index_cost_adj = 1 (%) that drops to about 52,000 (as seen).
Assuming you've left lots of other parameters to default the cost of the parallel index fast full scan would be roughly (5,200,000 / 8) * (26/12) / (10 * 0.9) = 156,500+
If you've bumped the db_file_multiblock_read_count up to 128 (which is fairly common still) then it would be roughly (5200000 / 128) * (266/12) /(10 * 0.9) = 100,000+
Basically your settings for the optimizer_index_xxx parameters invite Oracle to do serial index range/full scans all the time.
Jonathan Lewis wrote:
The reference to the post is there because (a) I had written it and (b) it's still possible that it has some relevance**, and (c) it shows that in at least once case the hints don't behave as expected.
apologies, now that I see how my reply could have been read... It wasn't my intention to question the reference to your post, although it looks like that's pretty much what I did resp. wrote. Sometimes I get caught by my attempts of being smart (there is a suitable expression for that but it's not suited for a public forum I guess). Not being a native speaker doesn't make things better.
There's nothing to apologise for - I thought your point was valid, and one that I could have made clearer in my own comment. It's also useful for those who are prepared to read these threads carefully to see that one expert can pick up and expand on the ideas put forward by another.
Thanks a lot!!! After setting optimizer_index_cost_adj to 100 at the session level for stats collection, the plans are fine now
Not sure how to make the question answered. anyone can pl suggest?
You seem to be logging in with a different id (unless you've changed your name from one meaningless number to another).
I assume the id that created a question is the only id that can mark it answered.
Oh, you are right. i was switching between a different id to check progress on one SR. Marking it answered now.