SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm = '_eliminate_common_subexpr';

note:315631.1

A1: The hidden parameters start with an "_".They can not be viewed from the output of 'show parameter'
or querying v$parameter unless and until they are set explicitly in init.ora.
However if you want to view all the hidden parameters and their default values the following query
could be of help. Connected as SYSDBA, execute:

SELECT a.ksppinm "Parameter",
       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 '/'
/

This will list all the hidden parameters with their default values.

A2: Now you can change the desired parameter by setting it explicitly in init.ora or using the alter..session set command for session level.

During startup, the database takes the parameters from spfile by default.
So you have four options to change the parameters:

While starting up the database startup with pfile, e.g.
SQL> startup pfile
Create the spfile from pfile using command "create spfile from pfile" and replace the old spfile with the new one.
For more information see: Note 249664.1 - Pfile vs SPfile
Use
SQL> alter system set "<_parameter-of-choice>" scope=spfile and bounce the database.
Use
SQL> alter..session set command for session level

NOTE:
It is never recommended to modify these hidden parameters without the assistance of Oracle Support.
Changing these parameters may lead to serious corruption issues, high performance degradation and other problems in the database.

It is not possible to see hidden parameters of other instances in a RAC cluster.

ITPub博客

查看隐藏参数

最新文章