optimizer_mode and optimizer_goal

[Sweetie]

Hi

is alter session set optimzer_goal same as alter session set optimizer_mode?

also, there are 4 optimizer modes

rule
choose
all_rows
first_rows

I read from a book that by default oracle uses choose and first_rows, is this true?

Finally does all_rows and first_rows use table statistics? Or only choose use it. (from oracle reference guide it seems that only choose uses it though)

thank you

[hrishy]

Hello

is alter session set optimzer_goal same as alter session set optimizer_mode?

The answer is nope.

Because

optimizer goal tunes oracle to things like whether it should return the first few rows quickly (requirement of oltp env) or return all rows (req of DSS applications)

Optimizer mode is whaether oracle should use cost based optimizer applicable in version 7 and later or whether oracle should run in rule based mode.In cost based mode oracle will use the statistics for the objects if its available or will resort to rule based if statistics are absent.

In rule based mode orcale will not use the statistics .however this is now outdated.

wheather oracle will use statistics or not is dependent on two things
a)wheather the statistics are present
b)wheather the optimizer mode is set to choose

and not on first_rows and all_rows.

if you would like to refer to books Refer Oracle sql high performance tuning by guy harrison.

regards
Hrishy

[Sweetie]

hi I do this

SQL> r
  1* alter session set optimizer_mode=all_rows

Session altered.

SQL> show parameter optimize

NAME				     TYPE    VALUE
------------------------------------ ------- ------------------------------
optimizer_features_enable	     string  8.1.6
optimizer_index_caching 	     integer 0
optimizer_index_cost_adj	     integer 100
optimizer_max_permutations	     integer 80000
optimizer_mode			     string  ALL_ROWS
optimizer_percent_parallel	     integer 0
SQL> 
SQL> alter session set optimizer_goal=first_rows;

Session altered.

SQL> show parameter optimize

NAME				     TYPE    VALUE
------------------------------------ ------- ------------------------------
optimizer_features_enable	     string  8.1.6
optimizer_index_caching 	     integer 0
optimizer_index_cost_adj	     integer 100
optimizer_max_permutations	     integer 80000
optimizer_mode			     string  FIRST_ROWS
optimizer_percent_parallel	     integer 0


as you see altering optimizer_goal and optimizer_mode changes my session optimizer_mode, so they are not the same?

So all_rows and first_rows dont use statistics at all even the statistics are present? If so how do they use the cost based optimization?

thank you

[hrishy]

Hello

Yep there is still nothing wrong with my explanation thats bcoz
the parameter

optimizer_mode can take on values

o RULE - always use RBO (unless the query is hinted or accesses
an object the RBO doesn't know about)

o CHOOSE - Use RBO if no stats are on any of the tables in a
query. Use CBO if any are analyzed (or there are
hints or accessing an object the RBO cannot)

o FIRST_ROWS - Optimize with the CBO. Try to get a plan tha
gets the first row of the result set the fastest

o ALL_ROWS - Optimizer using CBO. Try to get a plan that
gets the last row of the result set the fastest.


As to which value you should use -- it would be up to you.
There is no "best" value for all (if there was, we would have
but one setting)...

In general, CHOOSE is appropriate.

if you are on oracle 7.3 and above the cost based optimizer will use statistics if staistics are present unless you overide them in your sql statement.

[Sweetie]

Hi

I am not asking which optimzer should I use, I want to know what is the difference of doing

alter session set optimzer_goal
and
alter session set optimzer_mode


And my second questions was: does ALL_ROWS and FIRST_ROWS use statistics?

[hrishy]

Hi

The optimiser Goal sets what way you want to retreive records ..Eg
First Rows. ( for Forms)
ALL ROWS ( for reports )

Whereas The Optimiser Mode lets you specify the kind of Optimisation
Oracle should use for executing the Query
Eg .COST BASED or RULE based

Finally does all_rows and first_rows use table statistics? Or only choose use it.

all_rows and first_rows will use statistics if the the underlying tables are analyzed if not they will not use statistics.

Here lemme explain with an example suppose you have a table called emp which has 50M rows

if you run a query like

Select enmae /*FIRST_ROWS*/
from emp

then the optimizer will use statistics if statistics are present and the optimizer mode is choose.

regards
Hrishy

[Sweetie]

hi

why when I do alter system set optimzier_mode and alter system set optimzer_goal I am changing optimizer_mode all the time?

See my previous post with example I tried

[hrishy]

Hello Sweetie

The OPTIMIZER_GOAL parameter of the ALTER SESSION statement can override the optimizer approach and goal established by the OPTIMIZER_MODE initialization parameter for an individual session.

The value of this parameter affects the optimization of SQL statements issued by stored procedures and functions called during the session, but it does not affect the optimization of recursive SQL statements that Oracle issues during the session.

so the answer to your question is YES.

i just reread the entire discussion here.

in the firs thread you asked me this .

I read from a book that by default oracle uses choose and first_rows, is this true

and the answer is yes.if you select a OLTP database oracle uses first_rows .

you can refer to Oracle sql high performance tuning by guy harrison.

regards
Hrishy

[julian]

Select enmae /*FIRST_ROWS*/ from emp

/*+ FIRST_ROWS*/ <------ note the +



[Edited by julian on 09-13-2001 at 07:56 AM]

[tamilselvan]

OPTIMIZER_MODE parameter may be set at INIT.ORA file, but not the OPTIMIZER_GOAL parameter.
The difference between OPTIMIZER_MODE and OPTIMIZER_GOAL is: the later can be set up at session level which will override the first one. Also remember that any recursive sql statements issued by Oracle will not use OPTIMIZER _GOAL settings but use OPTIMIZE_MODE settings only.