Hidden Optimizer Environment (11th Feb 2006)
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).
.