I was wrong

Thanks to dear Maria Colgan for her intervention, I am publicly admitting the error of my ways and declaring to never use the drug of the optimizer_index_cost_adj parameter again. I publicly admit (again) that I have (on occasion) used an extremely high OICA setting to try and "force" Exadata to use full table scans over index scans. I was wrong and I am sorry.

Snicker...

I've had a great time at the Enkitec Extreme Exadata Expo (E4) this week in Dallas. I eagerly awaited the presentation of Maria Colgan ("The Optimizer Lady") where she promised to "show through the use of real-world examples what you can do to ensure the Optimizer fully understands the capabilities of the platform it is running on without having to mess with initialization parameters or Optimizer hints."

During her presentation today, she did offer a couple of ways (made available in Exadata patch bundles...only one of which is currently available) to help clue the optimizer in on a couple of Exadata features so that it can make adjustments to cost computations. The patch that is available allows you to collect system stats in "Exadata" mode. By doing so, the MBRC system statistic is actually recorded and made available to the optimizer and results in a reduction of the cost of a full scan. This ultimately results in full scans being selected more frequently (versus an index scan operation) which opens up the possibility of more smart scans being able to kick in. As Maria told us, the MBRC value is currently not retained in the system stats values and the optimizer uses a "default" of 8 (and has since Oracle 8 days). The patch allows a system stats collection done using the Exadata mode to collect and retain an actual (more realistic) MBRC value that can provide more accuracy.

I'm still holding out for options to affect specific individual SQL statements on a case-by-case basis versus a more broad brush approach that can cause all SQL to be affected, but this looks like a good start. In the meantime, I promise to do my very best to refrain from resorting to using OICA.

Disclaimer: I'm not promising to *not* use other parameters or hints...yet.  :)

Thanks to everyone who attended E4 and I hope you shared my opinion that it was a fantastic event. I'm already looking forward to doing it again next year!