Re: Session optimizer goal and PLSQL

Thanks Jonathan for such a great explanation.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<992636196.17490.0.nnrp-10.9e984b29_at_news.demon.co.uk>...
> Sorry about the mis-quote; I stand corrected on
> the parameter name: luckily I was close enough.
> (It's hard enough remembering to spell it with a Z)
>
> SQL inside PL/SQL is indeed recursive. And I
> believe that this parameter ensures that such
> SQL follows the session level if set to true/1/yes.
>
> However it doesn't always seem to be work correctly,
> though I haven't spotted any strong pattern in the
> version/platform yet.
>
> Regarding your question to Sybrand - the optimizer
> mode active when you use tkprof will be the one
> used to generate the plan, irrespective of what
> was active when the query ran. Furthermore, the
> stats, indexes, etc. present when you use tkprof
> will dictate the path. However, the actual run-time
> execution plan is often recorded in the trace file
> too (unless the trace stopped before the relevant
> cursor closed) so you often get two execution
> plans for user SQL, so this should give you some
> clue if things look odd.
>
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
> Andrew wrote in message <2bfebe60.0106151118.2e19edd6_at_posting.google.com>...
> >"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
 news:<992557523.13471.0.nnrp-07.9e984b29_at_news.demon.co.uk>...
> >> This may be a result of the documentation
> >> lagging the software. I believe that it is due
> >> to the effect of the hidden parameter
> >> _optimizer_force_mode
> >> changing its default from FALSE to TRUE
> >> around version 8.1.7
> >>
> >You mean that _optimizer_mode_force (this is more correct spelling for
> >this parameter), which is set to 1 by default in 8.1.7 will force SQL
> >issued from PLSQL inherit session optimizer goal?
> >The description of the parameter in sys.x$ksppi is "force setting of
> >optimizer mode for user recursive SQL also". The question is: Are SQL
> >statements issued from PLSQL considered as recursive SQL?
> >Thanks.
Received on Fri Jun 15 2001 - 20:52:06 CDT