How to check if parameters are dynamically switchable

[Former Member]

Hello buddies,

How can I check if these parameters are dynamically switchable ?

OPTIMPEEK_USER_BINDS = FALSE

OPTIMIZERMJC_ENABLED = FALSE

SORTELIMINATION_COST_RATIO = 10

I do not have them set on the system, so I think that tranzaction rz10 or rz11 will not help.

Thanks for help.

[Former Member · Accepted solution]

Hello,

These are not SAP profile parameters but Oracle database parameters, so there is no question of 'dynamic switchable'.

If you set them in database, then you need to restart database.

Thanks

[Former Member · Accepted solution]

HI,

Thanks for the answer.

I already set in one server this parameter using scope = both, so it will be modified also in the spfile.

Now that I have done it, you said I need to restart the DB.

What about if I do not restart it ?

and leave it like this untill I will have a maintenance window and will do it then ?

Thanks in advance,

Jordan.

[Former Member · Accepted solution]

Hello,

Parameter will take effect only after the database restart.

That's ok, you can leave the restart until next maintenance window.

But the better option is, to set the parameter during the maintenance window itself where you can go for restart at that time itself and can revert the parameter if something goes wrong (to be on safer side).

Thanks

[Former Member · Accepted solution]

In addition to what Volker pointed out:

Have a look at SAP Note 601157 - Oracle9i: Server Parameter File

(Despite its title it is valid for Oracle 10g and 11g as well.)

The parameters that can be changed dynamically are:
SQL>select name, value, issys_modifiable
    from v$parameter
    where issys_modifiable = 'IMMEDIATE';
For these parameters, you can specify scope = memory, scope = both or scope = spfile.

regards

[Former Member · Accepted solution]

Hi Joe, Volker, DatabaseSAP,

Thanks for the useful info too.

I have stated this command:

SQL>select name, value, issys_modifiable

from v$parameter

where issys_modifiable = 'IMMEDIATE';

but the problem is that my parameters are not set yet.

So I am afraid that I cannot check any of:

OPTIMPEEK_USER_BINDS = FALSE

OPTIMIZERMJC_ENABLED = FALSE

SORTELIMINATION_COST_RATIO = 10

but only after I set them.

So Volker, you are saying that ALTER SYSTEM: IMMEDIATE it means that I do not need to restart the DB , right ?

What would be the best command to set these parameters ?

for example:

alter system set OPTIMPEEK_USER_BINDS = FALSE scope=both sid='SID';

?

Sorry, I am quite new in Oracle, but I start to like it

Thanks in advance,

[volker_borowski2 · Accepted solution]

Hi,

I think some of the underscore Parameter are in V$PARAMETER2 ?!?!?

And there might be a V$SYSTEMPARAMETER (?) as well...

Check

select owner,synonym_name form dba_synonyms where synonym_name like '%PARAMETER%';

V.

and yes, a change of a dynamicly changeable parameter is effective immediately, allthough

it might take some time to activate it (i.E. if your reduce db_cache_size it might take a few minutes to free the memory).

Edited by: Volker Borowski on Sep 22, 2011 8:35 PM

[stefan_koehler · Accepted solution]

Hello Jordy,

this depends on the definition of the view v$parameter and the "hidden" parameters.

To get a complete parameter overview (and its description) - check this SQL:

http://www.jlcomp.demon.co.uk/params.html

Regards

Stefan

[Former Member · Accepted solution]

Hi Volker,

SQL> select owner,synonym_name from dba_synonyms where synonym_name like '%PARAMETER%';

OWNER SYNONYM_NAME

-

---

-

---

PUBLIC DBA_HIST_PARAMETER

PUBLIC DBA_HIST_PARAMETER_NAME

PUBLIC USER_REPPARAMETER_COLUMN

PUBLIC ALL_REPPARAMETER_COLUMN

PUBLIC DBA_REPPARAMETER_COLUMN

PUBLIC DBA_LOGSTDBY_PARAMETERS

PUBLIC ALL_APPLY_PARAMETERS

PUBLIC DBA_APPLY_PARAMETERS

PUBLIC ALL_CAPTURE_PARAMETERS

PUBLIC DBA_CAPTURE_PARAMETERS

PUBLIC USER_ADVISOR_SQLW_PARAMETERS

OWNER SYNONYM_NAME

-

---

-

---

PUBLIC DBA_ADVISOR_SQLW_PARAMETERS

PUBLIC DBA_ADVISOR_PARAMETERS_PROJ

PUBLIC USER_ADVISOR_PARAMETERS

PUBLIC DBA_ADVISOR_PARAMETERS

PUBLIC DBA_ADVISOR_DEF_PARAMETERS

PUBLIC NLS_DATABASE_PARAMETERS

PUBLIC NLS_INSTANCE_PARAMETERS

PUBLIC NLS_SESSION_PARAMETERS

PUBLIC GV$LOGMNR_PARAMETERS

PUBLIC GV$HS_PARAMETER

PUBLIC GV$NLS_PARAMETERS

OWNER SYNONYM_NAME

-

---

-

---

PUBLIC GV$PARAMETER_VALID_VALUES

PUBLIC GV$SPPARAMETER

PUBLIC GV$SYSTEM_PARAMETER2

PUBLIC GV$SYSTEM_PARAMETER

PUBLIC GV$PARAMETER2

PUBLIC GV$PARAMETER

PUBLIC GV$OBSOLETE_PARAMETER

PUBLIC V$LOGMNR_PARAMETERS

PUBLIC V$HS_PARAMETER

PUBLIC V$NLS_PARAMETERS

PUBLIC V$PARAMETER_VALID_VALUES

OWNER SYNONYM_NAME

-

---

-

---

PUBLIC V$SPPARAMETER

PUBLIC V$SYSTEM_PARAMETER2

PUBLIC V$SYSTEM_PARAMETER

PUBLIC V$OBSOLETE_PARAMETER

PUBLIC V$PARAMETER2

PUBLIC V$PARAMETER

39 rows selected.

[volker_borowski2 · Accepted solution]

Hi Jordy,

the select was more meant to get the name of the other V$ Parameter tabs for you,

so that you can adopt Jo's query to the other tables to find the params missing.

Volker

[volker_borowski2]

Hi,

OPTIMPEEK_USER_BINDS is dynamic since Version 10

OPTIMIZERMJC_ENABLED is dynamic

SORTELIMINATION_COST_RATIO is dynamic since Version 10

There are A LOT Parameters that can be changed dynamicly.

http://www.orafaq.com/parms/

Volker