Re: Tuning Question: Can 'rule based' optimizer enforced on a tablespace

Hi Helen

Your vendor suggestion is crap. You dont select optim mode on objects, but on sql statements. Might be the named tablespace holds objects used merely by some application and they stick to rule based (not uncommon, but poor choise) The application should alter session set optimizer_[goal | mode] = RULE. If you can change the application, might give the glogin.sql or login.sql a try. Works like a .profile on unix systems.

Also choose is a poor one, user interactice session should use FIRST_ROWS and reports ALL_ROWS, and some statements should have hints to guide to god optimizer mode and index use and what have you.
With choose and any table statistics => ALL_ROWS, no statistics whatever => RULE. The interactive user seems forgotten here. Most database do have them.

/Svend Jensen

Helen wrote:

> We are running Oracle EE 8.1.6.0.0 on Solaris 2.6
>
> We currently set the optimizer to be 'choose'.
>
> My understanding is that one can change the optimizer on session
> level, instance level or statement leve.
>
> But our product vendor suggested to use 'rule based' for a certain
> tablespace. Can this be done and how?
>
> Thank you very much!
Received on Thu Sep 06 2001 - 13:48:12 CDT