Toad World® Forums

is optimizer_goal in preferences working??


Hi !!

My question is: Does OPTIMIZER_GOAL/OPTIMIZER_MODE parameter in preferences working?? Last time i was tracing sql’s from sqlnav session, i didn’t observed setting OPTIMIZER_MODE parameter. I know that in earlier version of sqlnav it was worki’n. (as OPTIMIZER_GOAL).

So i traced session, where i change optimizer_goal and default date format.

Tkprof file in attachement. In this file is part about setting date format, but any word about OPTIMIZER_MODE.

If i’m thinking incorrectly please correct my observations.

Regards Piter (108 KB)


Hi Piter,

I’m going to have a look at this functionality shortly…
I’ll let you know what I find!!



Well, the short answer Piter, is no it is not working (for 10g databases).
It seems that 10g does not support the same optimizing parameters as earlier versions of Oracle.
You said that it worked in earlier versions of SQL Nav… was this against 10g databases, or other (earlier) Oracle versions?
My guess, is that it was earlier versions.
The functionality you are looking for does appear be be working in the SQL Tuning application. Have you tried to use the optimization functionality within SQL Tuning?
There is currently an issue raised against this functionality within SQL Nav, but it has been given a low priority.
How important is this functionality to you (i.e. to have it working within SQL Nav). I may be able to raise the priority of the current issues, and have it fixed in a future SQL Nav release.



I Thought that. This parameter simply sets optimizer_goal for session, so it was a trouble to connect via dblink from 9i to 10g. In that select, error occured : optimizer_goal is obsolete. This parameter was migrated to optimizer_mode in 10g, that’s why it’s not working.
About using this, sometimes it speed up DB Nav selects on some large databases. Not alwas ALL_ROWS in 10g is the fastest optimizer mode, so i could change this setting by tihs parameter in preferences.
Maybe you should set optimizer_goal for earlier verions of Oracle, but in 10g sets optimizer_mode parameter.
Regards Piter.


Hi Piter,

Thank you very much for the tips :-). We will take it into consideration and get it in a future Beta build for you.

Have a great weekend.



Nice to hear that!
I also wish you a great weekend!

Regards Piter.


Hi Piter,

Just to let you know, I have logged a change request for your suggestion!!



Thanks! :wink: