Forcing rule based for a session

[MSaeed]

Is it possible to force a non interactive session into rule based optimizer mode?

The session is started by an application via OCI call to the d/b ? No change to the code for the app is possible, so it would have to be done via procedure/pkg calls. Something like
sql>alter session set optimizer_mode=rule;

The rest of the database can operate in COST based with the parameter optimizer_mode=choose and stats for the tables/indexes.

The application runs as one oracle user/schema.

Any help will be appreciated.

[sambavan]

I don't think it would be feasible to alter the session to rule base. Instead you could use the hints to enforce the rule base criterias in your sql statements.


Sam

[Zaggy]

I would check out system triggers and see if you could catch the login of that session. If the trigger runs in that sessions process then it should effect the session. This is all theory of course, but I hope this helps.

[denevge]

If the application has it's own schema, with it's own objects,
just remove all the statistics.

Oracle will automatically use rule-based optimization.

Hope this helps
Gert