parameter remove

[Former Member]

Hi,

any one can help me how to removed optimizer_index_cost_adj parameter.

[fidel_vales · Accepted solution]

Hi,

The question is how to remove the parameter, not what value has to have.

The answer has been provided alredy. The command to remove any parameter is

alter system reset <parameter> scope=SPFILE;

and restart the instance.

OK, there are parameters that can be changed without restarting the instance, then the scope would be "both".

Now the confusion you have is that you still see the parameter.

Yes, that is normal.

A LOT of oracle parameters if they are not set will have a DEFAULT value.

The default value of OPTIMIZER_INDEX_COST_ADJ is 100.

so if you sse that its value is 100 it may be that it is not set or hat you have set it to the default value.

Remember, if SAP says NOT to set the parameter, that is different from setting it to the default value.

Now, you see that OPTIMIZER_INDEX_COST_ADJ is 100. Is it set to the defaul value or not?

very easy, you check it in the view GV$PARAMETER2, for example:

SELECT INSTANCE, NAME, VALUE, ISDEFAULT from GV$PARAMETER2 WHERE NAME = 'optimizer_index_cost_adj';

The name must be in lowecase.

If the value of the column ISDEFAULT is TRUE you have "removed" the parameter.

BTW, I alwasy recommend to check the documentation before asking any question:

Managing Initialization Parameters Using a Server Parameter File

Thanks

Fidel.

[Former Member · Accepted solution]

Hi Fidel,

Thanks for your reply. After execute the below command

SELECT INSTANCE, NAME, VALUE, ISDEFAULT from GV$PARAMETER2 WHERE NAME = 'optimizer_index_cost_adj';

i get the below message

SQL> SELECT INSTANCE,NAME,VALUE,ISDEFAULT from GV$PARAMETER2 WHERE NAME ='optimizer_index_cost_adj';

SELECT INSTANCE,NAME,VALUE,ISDEFAULT from GV$PARAMETER2 WHERE NAME ='optimizer_index_cost_adj'

       *

ERROR at line 1:

ORA-00904: "INSTANCE": invalid identifier

when I try the below command its show no row seleted.

SQL> SELECT INST_ID,NAME,VALUE,ISDEFAULT from GV$PARAMETER2 WHERE NAME = 'opimizer_index_cost_adj';

no rows selected

I want just remove the parameter but after removing its take a default value 100.

Its not removed please help.

Thn

Manas

[fidel_vales · Accepted solution]

OK,

I do it:

col inst_id for 999
col name for a30
col value for a15
SELECT INST_ID, NAME, VALUE, ISDEFAULT from GV$PARAMETER2 WHERE NAME = 'optimizer_index_cost_adj';

INST_ID NAME                           VALUE           ISDEFA
------- ------------------------------ --------------- ------
      1 optimizer_index_cost_adj       20              FALSE


ALTER SYSTEM RESET optimizer_index_cost_adj scope=SPFILE;

SELECT INST_ID, NAME, VALUE, ISDEFAULT from GV$PARAMETER2 WHERE NAME = 'optimizer_index_cost_adj';

INST_ID NAME                           VALUE           ISDEFA
------- ------------------------------ --------------- ------
      1 optimizer_index_cost_adj       20              FALSE

The same as I have to restart, lets do it:

SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

STARTUP
...
SELECT INST_ID, NAME, VALUE, ISDEFAULT from GV$PARAMETER2 WHERE NAME = 'optimizer_index_cost_adj';
INST_ID NAME                           VALUE           ISDEFA
------- ------------------------------ --------------- ------
      1 optimizer_index_cost_adj       100             TRUE

et voilá, the parameter has been reset and it is taking its default value.

If the query shows nothing, then you are typing something wrong.

and as mentioned before (If you have read me) the parameter will never be "removed" if we understand the verb remove as "eliminate or get rid of" or "take (something) away or off from the position occupied" which are the two definitions I find in the dictionary

[Former Member · Accepted solution]

Hi Fidel,

I think i typed error anything I also get the following value.

SQL>

SQL> SELECT INST_ID, NAME, VALUE, ISDEFAULT from GV$PARAMETER2 WHERE NAME = 'optimizer_index_cost_adj';

INST_ID NAME                           VALUE           ISDEFA

------- ------------------------------ --------------- ------

      1 optimizer_index_cost_adj       100             TRUE

SQL> show parameter optimizer_index_cost_adj

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_cost_adj             integer     100

Any process that the parameter remove that means its not show any value.

[fidel_vales · Accepted solution]

Manas . wrote:


SQL>
SQL> SELECT INST_ID, NAME, VALUE, ISDEFAULT from GV$PARAMETER2 WHERE NAME = 'optimizer_index_cost_adj';

INST_ID NAME                           VALUE           ISDEFA
------- ------------------------------ --------------- ------
      1 optimizer_index_cost_adj       100             TRUE



Any process that the parameter remove that means its not show any value.

Hi,

Haven't you read completely my answer?

if so, try to understand.

If you still are so <...> that you can't understand after my two replies, then

NO, THERE IS NO PROCESS TO REMOVE TOTALLY A PARAMETER WHEN ORACLE HAS DECIDED THAT IT MUST HAVE A DEFAULT VALUE.

in your case, ISDEFAULT=TRUE means that the parameter is NOT SET.

[Reagan]

Have you tried using ALTER SYSTEM RESET clause?

alter system reset optimizer_index_cost_adj scope=SPFILE;

Regards

RB

[Former Member]

HI

i remove the parameter from pfile and newly create spfile but the parameter not going,.

its shows

SQL> show parameter optimizer_index_cost_adj

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_cost_adj             integer     100

[hugo_amo]

Hi Manas,

Parameter  OPTIMIZER_INDEX_COST_ADJ need to be set to 20 if your system is an OLTP system, if not, then you need to remove this setting. This is an error in the EWA report.
A correction is available in ST-SERR 701_2101_1 SP14
as per SAP Note 1451202 -

Various corrections on ORACLE checks for ST-SER 701_2010_1 Point 22 makes reference to issues with OLAP OLTP recommendations and it is resolved with SP14.

Regards,

Hugo