Tag: parameters

How to find Hidden Parameters in Oracle

Posted on by Juan Andres Mercado in Oracle 12c, Oracle 19

The oracle database has hundreds of hidden parameters, and a lot of those parameters are not documented.

Knowing about those parameters is important for dbas involved in database migrations and upgrades.

Many parameters have a high-performance improvement on different systems.

Here, I share how to query and change the oracle hidden parameters.

Query to find hidden parameters in oracle 12c , 18c and 19c

 set line 150
 col PARAMETER format a30
 col DESCRIPTION format a60
 col SESSION_VALUE format a10
 col INSTANCE_VALUE format a10
 spool HIDDEN_parameter_database.log


 SELECT a.ksppinm AS parameter,
        a.ksppdesc AS description,
        b.ksppstvl AS session_value,
        c.ksppstvl AS 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 a.ksppinm;

spool off

Now, after we save this list, is possible that we need to start with a pontificated change.

Hidden ParameHow can I set the value of a hidden parameter?

alter system set "_report_capture_cycle_time"=0 scope=spfile sid='*';

Remember, the importance that these parameters must be recommendations of the oracle support, because it can change the behavior of the engine and can leave us in the situation of not having support.