At one point in your DBA journey, you will come across Oracle hidden parameters, whether you want it or not.

An upgrade will happen, and all of a sudden query performance goes south.It turns out there is a fix for that with a hidden parameter.

You encounter an ORA-00600 error, and there is a hidden parameter to fix that.

There is an Oracle bug, and you guessed it, there is a hidden parameter for that.

Let’s pretend you are working through a critical problem, and your boss is watching over your shoulder to see what you are typing.
One of the Oracle support note, asks you to check the value of a hidden parameter and alter it to a specific value.
Since you are THE DBA, you are expected to get the answer right away.
I bet you don’t remember the X$ views, where the values are stored for hidden parameter. And I don’t blame you!

I wouldn’t remember it either! (Have you seen those names: x$ksppi,x$ksppcv?)

What do you do in situations like these? You create scripts, so you get the answers right away!

Dbaparadise.com is here to your rescue as well!

1. What Are Hidden Parameters And Why Should I Care?
2. How Can I See The Value Of A Hidden Parameter?
3. How Can I See All Non-Default Hidden Parameters In The Database?
4. How Can I Change The Value Of A Hidden Parameter?

1. What Are Hidden Parameters And Why Should I Care?

Did you ever wonder why hidden parameters are called hidden?

First thing that comes to my mind: these parameters don’t show up in v$parameter view, unless they are set explicitly.

Oracle says that hidden parameters are undocumented parameters. That is not completely true.
As bug fixes and workarounds are published for different problems, the hidden parameters become documented.
Just run a search on Oracle’s Support site for the following parameter, as an example: _push_join_predicate, plenty of documentation will show up.

Few things to remember about hidden parameters,

  • names start with an underscore
  • you should not mess with them, unless it is necessary.
    When is it necessary? to fix a bug, to fix a recurring error, when Oracle recommends setting it, and when and if you know what you are doing.

A Good DBA needs to know what hidden parameters are set in the database and their values, especially during upgrade or database migrations. This is important, in order to ensure a smooth upgrade, without major events.

2. How Can I See The Value Of A Hidden Parameter?

As I stated above, you can only see the value of a hidden parameter in v$parameter view, if the value has been explicitly set.

HRTST (SYS) >show parameter push_join
HRTST (SYS) >select name, value from v$parameter where name like '%push_join_predicate%';

no rows selected

HRTST (SYS) >alter system set "_push_join_predicate" = FALSE scope=both sid='*';

System altered.

HRTST (SYS) >show parameter push_join

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_push_join_predicate                 boolean     FALSE

HRTST (SYS) >select name, value from v$parameter where name like '%push_join_predicate%';

NAME                            VALUE
------------------------------- ------------------------------------------
_push_join_predicate            FALSE

What if, you want to see the value of one hidden parameter, even if the parameter was not set explicitly? In this case v$parameter might not be able to help you.
You need to query these 2 views: x$ksppi,x$ksppcv

?View Code ENGLISH
/*
Script name: hidden_parameter.sql
 
Example:     @hidden_parameter.sql PARAM_NAME
 
Created By   Diana Robete
Copyright:   @2017 dbaparadise.com
*/
 
col name for A40
col value for A30
col default for A15
col type for A10
col description for A50
 
set linesize 200
set verify off
set define '&'
 
define hidden_param=&1
 
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf default,
decode
(a.ksppity, 1, 'boolean', 
            2,'string',
            3,'number',
            4,'file', 
 a.ksppity) type,
a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
and lower(a.ksppinm) like lower('%&hidden_param%')
/
 
Output:
 
HRTST (SYS) >@hidden_parameter.sql push_join_predicate
 
NAME                  VALUE           DEFAULT  TYPE       DESCRIPTION
--------------------- --------------- -------- ---------- -------------------------------------------
_push_join_predicate  FALSE           TRUE     boolean    enable pushing join predicate inside a view

3. How Can I See All Set Hidden Parameters In The Database?

You need to create a report that shows all the hidden parameters in your database, that have been explicitly set.
You can create such a report in multiple ways. You can query v$parameter, or you can query the views listed above. You can also create an init.ora file from spfile, and these parameters will be displayed.

The script below will give you a list of all the hidden parameters you have set in your database.

?View Code ENGLISH
/*
Script name: hidden_parameters_set.sql
 
Example:     @hidden_parameters_set.sql
 
Created By   Diana Robete
Copyright:   @2017 dbaparadise.com
*/
 
col name for A40
col value for A30
set linesize 200
set pagesize 100
 
select name, value from v$parameter where name like '\_%' escape '\';
 
Output:
 
NAME                                     VALUE
---------------------------------------- ------------------------------
_ktb_debug_flags                         8
_push_join_predicate                     FALSE

Some of the parameters listed, could be set to their default values, and will show up in v$parameter because these parameters where modified at one point.

4. How Can I Change The Value Of A Hidden Parameter?

You can change a hidden parameter, the same way as you would any other init.ora parameters, keeping in mind that you need to include the parameter name in double quotes, otherwise you get an errors message:

alter system set _push_join_predicate=FALSE scope=spfile sid='*'
                 *
ERROR at line 1:
ORA-00911: invalid character

The correct way to do it:

alter system set "_push_join_predicate"=FALSE scope=spfile sid='*';

From now on, you are all set to query hidden parameters in Oracle, in no-time!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana


3 thoughts on “Unhiding The Oracle Hidden Parameters

  1. Dear Diana,

    Very good & interesting material. I have one question i.e. how to find the hidden object in Oracle database?

    1. Hi Khudadad, what do you mean by hidden object? The article is about hidden parameters, and it provides the query to find the value of the hidden parameter.

      Diana

Leave a Reply

Your email address will not be published. Required fields are marked *