Oracle DB 11gR2 – How to list all hidden/undocumented parameters

How to list all hidden/undocumented parameters in Oracle Database

Oracle database is extremely powerful due to many reasons. One of it is the level of tuning and optimisation that you can perform on it. You can optimise an Oracle database using parameters in PFile or SPFile. It has many parameters, both documented and undocumented for you to configure. Documented parameters are easy to configure due to the extremely clear documentation by Oracle. However, this does not apply to undocumented or hidden parameters. Of course, the parameters are hidden for a reason and it should NOT be changed unless you are extremely confident of what you are changing!

The query below will be able to list down all the hidden parameters for the database release.

SQL> select a.ksppinm, b.ksppstvl
 FROM x$ksppi a, x$ksppcv b
 WHERE a.indx=b.indx;

This is the output in Oracle Database 11.2.0.3. There are about than 2500 hidden parameters. I’m only showing a portion about it.

KSPPINM                                  KSPPSTVL
---------------------------------------- ---------------------------------------------
 _appqos_qt                              10
 _ior_serialize_fault                    0
 _shutdown_completion_timeout_mins       60
 _inject_startup_fault                   0
 _latch_recovery_alignment               65534
 _spin_count                             1
 _latch_miss_stat_sid                    0
 _max_sleep_holding_latch                4
 _max_exponential_sleep                  0
 _other_wait_threshold                   0
 _other_wait_event_exclusion             0

Hope this helps!

Regards,
WeiShan

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: