Oracle Administration
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle Administration

  1. #1
    Join Date
    Mar 2002
    Posts
    38

    Question

    What is the difference between changing the parameters in the Init.Ora file and altering the parameters at the SQL*Plus Prompt?

    Please Expain in depth giving the examples!
    SUROOP B

  2. #2
    Join Date
    Sep 2000
    Posts
    384
    say Optimizer_mode=choose in the init parameter

    The optimizer_mode will execute all the sql querry as per
    choose mode .

    If you want to use the optimizer_mode =rule or first_rows only for a short period of time and only for a specfic session

    you say
    alter session set optimizer_mode=rule ..

    this will take effect for that particular session
    say your sort_area_size =65656

    alter system set sort_area_size=10485760 DEFERRED ;

    this means all the new sessions the sort_area_size will be 10485760 but after the instance is bounced the old value (65656)takes into effect

    hope you understood ...
    Radhakrishnan.M

  3. #3
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Originally posted by omegamark
    say Optimizer_mode=choose in the init parameter

    The optimizer_mode will execute all the sql querry as per
    choose mode .

    If you want to use the optimizer_mode =rule or first_rows only for a short period of time and only for a specfic session

    you say
    alter session set optimizer_mode=rule ..

    hope you understood ...
    The optimizer_mode doesn't work when you change with alter statement.

    You should use for this

    alter session/system set optimizer_goal=choose/rule..

    At least in 8i in 9i I don't know

    Regards

    Angel

  4. #4
    Join Date
    Sep 2000
    Posts
    384
    pls see the ex that i have shown and i have tested on test database also ???
    I works good for me what I said ...


    SQL> show parameter optimizer_mode

    NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
    ---------------------------------------------------------------- ------- ----------------------------
    optimizer_mode string RULE
    SQL> alter session set optimizer_mode=choose;

    Session altered.

    SQL> show parameter optimizer_mode

    NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
    ---------------------------------------------------------------- ------- ----------------------------
    optimizer_mode string CHOOSE
    Radhakrishnan.M

  5. #5
    Join Date
    Jan 2000
    Location
    Silver Spring MD USA
    Posts
    105
    When you change a parameter value with ALTER SESSION, it only changes the setting for that current session. Other sessions continue to use the previous setting.

    ALTER SYSTEM is like changing it in init.ora file, and applies to all sessions on the system (sometimes immediate, sometimes deferred to new sessions depending on parameter). However, it does not update the actual init.ora file and so once the database is rebooted, it will revert back to the setting in the init.ora or the default.

    Query V$PARAMETER for columns ISSES_MODIFIABLE and ISSYS_MODIFIABLE.

    ISSES_MODIFIABLE shows whether this parameter can be modified via ALTER SESSION command.

    NAME ISSES
    --------------------------------- -----
    shared_pool_size FALSE
    optimizer_mode TRUE

    ISSYS_MODIFIABLE shows whether this parameter can be modified via ALTER SYSTEM and whether the modification is IMMEDIATE or DEFERRED to only new sessions.
    NAME ISSYS_MOD
    --------------------------------- ---------
    db_block_size FALSE
    sort_area_size DEFERRED
    timed_statistics IMMEDIATE

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