-
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
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|