Re: Cost-based optimization in PL/SQL

On Thu, 23 Oct 1997 13:21:25 -0600, harrir_at_mbia.com wrote:

>I am having a problem using the cost-based optimizer. After setting the
>optimization mode for my session to ALL_ROWS (with an alter session
>command), SQL statements executed by themselves use a cost-based plan,
>but the same SQL statements inside PL/SQL stored procedures or anonymous
>blocks continue to use RULE based optimization. I have proven this by
>examining output from TKProf, which shows the recursive SQL statement
>(the one in the PL/SQL procedure) using an optimizer goal of RULE.
>Performance of the statement under RULE optimization is poor, which
>coincides with poor performance when executing the stored procedure or
>anonymous block. Performance of the statement when executed from SQL*Plus
>is excellent.
>
>The INIT.ORA parameter is set to RULE, as I am trying to evaluate the
>possibility of switching to the CBO. The tables all have good statistics.
>The database version is 7.3.3.
>
>Does anybody know why the PL/SQL engine is not using the optimization mode
>set with the ALTER SESSION command? Any help would be appreciated.
>

You're getting RULE based an a stored procedure after ALTER SESSION and executing the procedure from the SQL prompt? I'll have to try that one tomorrow, but the easiest fix is to use hints in the procedure.
Note on the rule to CBO transition. Most of the queries do run faster, but we have a few monster queries (5+ pages) in our app that are taking a serious beating. Look before you leap. It's not pure magic. Received on Sat Oct 25 1997 - 00:00:00 CDT