Cost-based optimization in PL/SQL

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.

Thanks,
Roger Harris
Oracle DBA
MBIA Corp.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet