optimizer_mode and optimizer_goal
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: optimizer_mode and optimizer_goal

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    139
    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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


  3. #3
    Join Date
    Oct 2000
    Posts
    139
    hi I do this

    Code:
    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

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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.




  5. #5
    Join Date
    Oct 2000
    Posts
    139
    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?

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    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

  7. #7
    Join Date
    Oct 2000
    Posts
    139
    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

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    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

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Select enmae /*FIRST_ROWS*/ from emp
    /*+ FIRST_ROWS*/ <------ note the +



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

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width