Hidden Optimizer Environment (11th Feb 2006)
Non-existent hint (25th May 2010)
Page 473: v$sql_optimizer_env. I make the comment that the list of values in v$sql_optimizer_env is much shorter than the list dumped in the 10053 trace. Of course, I had overlooked the first rule of investigating dynamic performance views – if something seems to be missing from the v$, check the underlying x$. Thanks to Julian Dyke, then for reminding me to do this.
· v$sys_optimizer_env turns out to be x$qkscesys
· v$ses_optimizer_env turns out to be x$qksceses
· v$sql_optimizer_env turns out to be x$kqlfsqce
When you examine the x$, you find that the values listed in the 10053 trace are all in the x$, and the order that they appear in the trace matches the index order in the x$ (allowing for the fact that the non-default values are pulled to the top and printed in a separate section in the trace file).
Page 465: While discussing general principles about checking parameter names for matches with optimization effects I reference the hint /*+ no_index_combine() */ as a way of dealing with individual cases of btree/bitmap conversions causing problems/ Unfortunately there is no such hint. I’m not sure why I thought this, and didn’t make absolutely sure about it before publishing. The best I’ve done with blocking unwanted conversions is to use the /*+ no_index() */ hint listing the indexes that were erroneously appearing in the conversion. If there is an index you want used as a btree index, you obviously can’t include it in the no_index() – but adding an /*+ index_rs_asc() */ if you want it used with a range scan might help.