NLS_SORT and SPM reproducibility

Mohamed Houri’s Oracle Notes

I wrote a blog article which aims mainly to show what optimizer parameter the CBO will use to reproduce aSPM baseline plan: the current environment parameters or the parameters used during the SPM plan baseline capture? Having tested in this blog article only the influence of the optimizer_mode (all_rows versus first_rows mode) I ended up with a conclusion that the CBO will use the optimizer_mode parameter stored during the SPM plan capture. I have also put a careful warning that this is not a conclusion one can spread to other CBO parameters without testing; particularly that I have already seen an otn thread dealing with the inability to reproduce a SPM plan because of a change in the _optim_peek_user_binds  hidden parameter. Then, a post on oracle list about the non reproducibility of a SPM baseline following an upgrade from 10gR2 to 11gR2 prompted me to investigate the influence…

View original post 309 more words