What does ISDEFAULT mean in v$parameter?

There is a common misunderstanding among DBAs about the column ISDEFAULT in the view v$parameter. Some think that when this column is TRUE it means the current value of the parameter is the default value. This leads to wrong conclusions and sometimes wrong settings for even production environments.

The documentation says this about this column:

"Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)"

This explanation is not a clear one and different people may understand different things from it.

This column is dependent on the setting in the parameter file when the instance is started. It does not show if the current value is the default or not. It only shows if the parameter is set in the parameter file or not. When it is TRUE it means that you did not set this in the parameter file when starting the instance. When it is FALSE it means this parameter was set in the parameter file when starting the instance.

Here is a real life case about this. When changing a few parameters an Exadata DBA accidentally sets the parameter cell_offload_processing to FALSE using an alter system command. When he tries to take back the settings he looks at v$parameter.ISDEFAULT to find out if cell_offload_processing=FALSE is the default setting. He sees that ISDEFAULT=TRUE and arrives at the wrong conclusion that cell_offload_processing=FALSE is the default value and leaves the parameter that way. This causes all Exadata storage offloading to be disabled and may cause query times to go over the roof.

Let's look at this with an example on 11.2.0.2. This is from a database created with default parameters.


SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter
  2  where name='cell_offload_processing';


NAME                           VALUE                          ISDEFAULT ISMODIFIED
------------------------------ ------------------------------ --------- ----------
cell_offload_processing        TRUE                           TRUE      FALSE

We see that the parameter is set to TRUE, ISMODIFIED is false meaning we did not modify the parameter and ISDEFAULT is TRUE meaning we did not set this parameter in the parameter file.

Now let's change it.


SQL> alter system set cell_offload_processing=FALSE;


System altered.


SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter
  2  where name='cell_offload_processing';


NAME                           VALUE                          ISDEFAULT ISMODIFIED
------------------------------ ------------------------------ --------- ----------
cell_offload_processing        FALSE                          TRUE      SYSTEM_MOD

After we set it FALSE we see that ISMODIFIED reflected the change, but ISDEFAULT is still TRUE. From this if a DBA concludes that this is the default value and takes action based on that, the result will be wrong. As we did not set this parameter in the parameter file when starting the instance, ISDEFAULT still shows TRUE.

Let's do a restart and look at v$parameter again.


SQL> startup force;
ORACLE instance started.


Total System Global Area  839282688 bytes
Fixed Size                  2231128 bytes
Variable Size             583009448 bytes
Database Buffers          251658240 bytes
Redo Buffers                2383872 bytes
Database mounted.
Database opened.
SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter
  2  where name='cell_offload_processing';


NAME                           VALUE                          ISDEFAULT ISMODIFIED
------------------------------ ------------------------------ --------- ----------
cell_offload_processing        FALSE                          FALSE     FALSE

Now we see that ISDEFAULT is FALSE because the parameter was in the parameter file.

What if we set it the parameter to the default value.


SQL> alter system set cell_offload_processing=TRUE;


System altered.


SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter
  2  where name='cell_offload_processing';


NAME                           VALUE                          ISDEFAULT ISMODIFIED
------------------------------ ------------------------------ --------- ----------
cell_offload_processing        TRUE                           FALSE     SYSTEM_MOD

As you see even if the parameter has the default value now, ISDEFAULT still shows FALSE.

Again let's restart and look again.


SQL> startup force;
ORACLE instance started.


Total System Global Area  839282688 bytes
Fixed Size                  2231128 bytes
Variable Size             583009448 bytes
Database Buffers          251658240 bytes
Redo Buffers                2383872 bytes
Database mounted.
Database opened.
SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter
  2  where name='cell_offload_processing';


NAME                           VALUE                          ISDEFAULT ISMODIFIED
------------------------------ ------------------------------ --------- ----------
cell_offload_processing        TRUE                           FALSE     FALSE

The parameter has the default value and ISDEFAULT is still FALSE after a restart. This is again because even if we set the parameter to the default value it was in the parameter file anyway.

What's the correct way to fix this? If we reset the parameter instead of setting it explicitly it will revert back to the default value.


SQL> alter system reset cell_offload_processing;


System altered.


SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter
  2  where name='cell_offload_processing';


NAME                           VALUE                          ISDEFAULT ISMODIFIED
------------------------------ ------------------------------ --------- ----------
cell_offload_processing        TRUE                           FALSE     FALSE


SQL> startup force;
ORACLE instance started.


Total System Global Area  839282688 bytes
Fixed Size                  2231128 bytes
Variable Size             583009448 bytes
Database Buffers          251658240 bytes
Redo Buffers                2383872 bytes
Database mounted.
Database opened.
SQL> select name,VALUE,ISDEFAULT,ISMODIFIED from v$parameter
  2  where name='cell_offload_processing';


NAME                           VALUE                          ISDEFAULT ISMODIFIED
------------------------------ ------------------------------ --------- ----------
cell_offload_processing        TRUE                           TRUE      FALSE

Now we are back to the defaults as the reset command removed the parameter from the parameter file.

So, do not count on the ISDEFAULT column when you are trying to find if the current value of a parameter is the default value or not. The documentation is the most reliable source to find out the default values of the parameters.