Oracle has many of initialization parameters, which are hidden and undocumented. Many savvy Oracle professionals are known to commonly adjust the hidden parameters to improve the overall performance of their systems. However, because these are “undocumented” parameters, most Oracle professionals rely on publications such as “Oracle Internals” to get insights into the proper setting for the hidden parameters.

Oracle also makes makes a huge disclaimer that the undocumented initialization parameters are usually only used in emergencies. Some of these parameters are OS specific and used in unusual recovery situations. Hence, there parameters should be manipulated carefully and preferably not without recommendation from an Oracle professional.

You will not find these hidden parameters in V$PARAMETER or see them with SHOW PARAMETERS command as these are hidden. All these parameter start with “_” (underscore), like _INDEX_JOIN_ENABLED, _IGNORE_DESC_IN_INDEX and _SMM_AUTO_MAX_IO_SIZE (used in Exadata environment).

Almost all of these parameters have an immediate and system wise effect, so, again – they should be handled with carefully. The good thing is that it is possible to change these parameters locally, for your session only. For example, if you wan to set _INDEX_JOIN_DISABLED to false (it is true by default) for your particular session only, you can run the following SQL:

ALTER SESSION SET “_INDEX_JOIN_ENABLED” = FALSE;

Here is a simple SQL query that you can use to find these parameters:

SELECT a.ksppinm "Parameter", a.ksppdesc "Description",
b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/'
ORDER BY 1;