JL Computer Consultancy

Cost Based Oracle: Fundamentals

Appendix B: Optimizer Parameters


Addenda

Hidden Optimizer Environment (11th Feb 2006)

Errata

Non-existent hint (25th May 2010)

 

Addenda

Hidden Optimizer Environment

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).

Back to Book Index

Top of page.


Errata

Non-existent hint

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.


Back to Book Index

Top of page.

.