Issues with OICA/OIC (OPTIMIZER_INDEX_COST_ADJ/OPTIMIZER_INDEX_CACHING) parameters have already been mentioned many times. Recently I’ve noticed one more and I think I didn’t see this case somewhere else on the Internet so I’ll share it.
OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.
And I have nor NLJ, nor IN-list iterator here.
This time the results are amusing. As long as the OIC doesn’t change, everything goes pretty much similar to the heap-organized table, with the query cost being a little bit higher – that’s OK as secondary indexes, based on logical rowids, may be slightly less efficient than indexes on heap tables.
When the OIC changes, everything gets too crazy. By modifying the CBO index caching assumption to a mere 10%, the query cost drops by two orders of magnitude! This is tremendous shift in the costing which poses terrific consequences for legacy applications trying to utilize IOTs advantages.
The bug fix can’t be turned off with _fix_control for some reason and I don’t have an old Oracle database to run the test so I can’t confirm if the bug-fix is the culprit or not. Anyway it looks stupid. I’ll try to submit bug report for this issue.
This entry was written by Timur Akhmadeev, posted on March 21, 2013 at 23:00, filed under CBO, Oracle and tagged 10.2.0.3, indexes, IOT, OIC, OICA. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.
Thanks. Are you sure that 11.2.0.1 results are different from 11.2.0.2? I see exectly the same behavior in 11.2.0.2/3.
Previously i made a typo: On 11.2.0.2 result is same as on 11.2.0.1.