Re: Session optimizer goal and PLSQL

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.