DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: optimizer mode

  1. #1
    Join Date
    Jan 2002
    Posts
    45
    Hi friends & gurus:

    Ours is presently set to CHOOSE. If I want to change it to RULE, how do I go about doing this? I am trying to fix a problem that is caused by one of the applications that we are using, and among the suggestions is that we set the optimizer mode to RULE. I want to set the change at the database level. Any help would be appreciated.

    Thanks

    John

  2. #2
    Join Date
    Jan 2002
    Posts
    45
    My bad. Ours is Oracle 8.17 running on W2K.
    Also, I looked at the pfile but did not see it anywhere. However, when I queried against v$parameter, it says that the dbase is running on the default CHOOSE mode.

  3. #3
    Join Date
    Sep 2000
    Posts
    96
    You can set this one of 3 ways:

    1. Set in initialization parameters section of the Oracle Instance Mgr
    2. SQL stmt - update v$parameter set optimizer_mode = 'CHOOSE'
    3. update the init.ora file to contain this parameter and the value of CHOOSE

  4. #4
    Join Date
    Sep 2000
    Posts
    96
    Oops....you said you were trying to set to RULE based. Where I entered CHOOSE above, change to RULE.

  5. #5
    Join Date
    Jan 2002
    Posts
    45
    I tried to update the v$parameter, but was given the error:

    ORA-02030-can only select from fixed tables/views.

    I did look at the init.ora file but did not see the 'Optimizer Mode' entry anywhere. Please help.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by mallard

    3. update the init.ora file to contain this parameter and the value of CHOOSE
    This is the only valid way to change this parameter.
    Jeff Hunter

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925

    System Level
    Code:
             sqlplus sys@service_name
             ALTER SYSTEM SET OPTIMIZER_MODE= 'RULE';
              
              exit;
               
              cd %ORACLE_BASE%\admin\instance\pfile
            
               edit initSID.ora file to add 
                OPTIMIZER_MODE=RULE
    To make the changes in the session level
    Code:
                 sqlplus user@service_name
                  ALTER SESSION SET OPTIMIZER_MODE='RULE'
    
                   Now you are set to use the rule base optimization.
                   This would be local to the current session only.
    Other wise you can use the hints in the statements to use the rule base optimization.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Jan 2002
    Posts
    45
    Thanks Jeff. I did that by adding this parameter to the init.ora. There was none prior to this. Guess will have to stay late tonight since I can't shutdown/start the dbase. It's a production dbase. Thanks again for all the help.

    John

  9. #9
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    To get this change take effect you son't have to shutdown the server. Alll you need to do is issue the alter system. The purpose of putting it on the init.ora is such that when you get to restart the server, your changes aren't lost.

    So, don't bother of staying too late :| . instead just issue the system level command that I had mentioned on my previous posting.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  10. #10
    Join Date
    Jan 2002
    Posts
    45
    Hi Sam:

    I tried by issuing the 'alter system....' command, but I was given the error message:

    "ORA-02096-specified initialization parameter is not modifiable with this option."

    This leaves me with the last option, that is to go with Jeff's suggestion. I have already added an entry

    OPTIMIZER_MODE ='RULE'

    to the init.ora file. It just that I will have to shutdown/startup the dbase for it to take effect. Also, as with Windows, every little change will require the shutting/rebooting of the server anyway. Do you have any idea how I can get around this.


    Thanks in advance for all the help.

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