Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle undocumented hidden parameters tips

Oracle Tips by Burleson Consulting

In part 1 of this series we explored ways to extract undocumented packages, hints, utilities and executables from Oracle. Now we are ready to explore some ways that the undocumented Oracle parameters are used to aid the Oracle professional in managing and tuning their systems.

Oracle makes a huge disclaimer that the undocumented initialization parameters are usually only used in emergencies. However, those who want to manipulate the internal mechanisms of Oracle to customize the behavior to their systems find the undocumented parameters very useful.

WARNING! Using undocumented parameters without the consent of Oracle can make your system un-supported and you may be on your own if you experience data corruption. Don't say I didn't warn you!

With over 100 undocumented parameters (see listing 1) it impossible to cover them all in this short article. Let's look at performance-related parameters first.

For specialized tasks such as database tuning, it's possible to set the Oracle undocumented ("hidden" parameters).  You should carefully test all undocumented parameters and get permission from Oracle technical support before using undocumented parameters in production.  Here is a common list of some of the undocumented parameters:

 Also, see these undocumented parameters from this Oracle IBM terabyte RAM benchmark:

_NUMA_pool_size = 536870912
 _undo_autotune = false
 _imu_pools = 358
 _optimizer_cache_stats = false
 _optimizer_cost_model = io
 _cursor_cache_frame_bind_memory = true
 _db_writer_coalesce_area_size = 16777216
 _kghdsidx_count = 1

Many savvy Oracle professionals 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 latch parameters: Whenever index contention is experienced (as evidenced by process waits), adjusting the following parameters may be helpful.

  • _db_block_hash_buckets - Defaults to 2 x db_block_buffers but should be the nearest prime number to the value of 2x db_block_buffers.
  • _db_block_hash_latches - Defaults to 1024 but 32768 is a sometimes a better value.
  • _kgl_latch_count - Defaults to zero which is means 1+number of CPUs. Lock contention can often be reduced by re-setting this value to 2*CPUs +1.
  • _latch_spin_count - This parameter shows how often a latch request will be taken.
  • _db_block_write_batch - Formerly documented, now undocumented. It is the number of blocks that the db writers will write in each batch. It defaults to 512 or db_files*db_file_simultaneous_writes/2 up to a limit of one-fourth the value of db_cache_size.

Oracle parallel query parameters: OPQ is an amazing facility for improving the speed of large-table full-table scans, and some DBAs are not aware that there are a dozen hidden parameters that can be changed to affect the behavior of parallel queries. Earl Shaffer provides this query to display the parameters.  Note that this script must be run from the SYS user as only the SYS user can access the X$ internal tables.

Note:  To see then an undocumented parameter has changed you cheek to see if it has moved into the v$parameter table:

-- changed undocumented parameters list

   substr(name, 0,1) ='_';


Connect sys/xxx as sysdba

REM File: underscoreparms.sql
REM Desc: see underscore '_' AKA hidden DB parameters
set echo off lines 149 pages 9999 feed off
clear col
clear break
clear compute
ttitle off
btitle off
COLUMN Param FORMAT a42 wrap head 'Underscore Parameter'
COLUMN Descr FORMAT a75 wrap head 'Description'
COLUMN SessionVal FORMAT a7 head 'Value|Session'
COLUMN InstanceVal FORMAT a7 head 'Value|Instnc'
ttitle skip 1 center 'All Underscore Parameters' skip 2

spool underscoreparms.lis

a.ksppinm Param ,
b.ksppstvl SessionVal ,
c.ksppstvl InstanceVal,
a.ksppdesc Descr
x$ksppi a ,
x$ksppcv b ,
x$ksppsv c
a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '/_%' escape '/'


clear col
clear break
clear compute
ttitle off
btitle off

spool off

NAME                                VALUE
----------------------------------- ------------------------------
_parallel_adaptive_max_users        1
_parallel_default_max_instances     1
_parallel_execution_message_align   FALSE
_parallel_fake_class_pct            0
_parallel_load_bal_unit             0
_parallel_load_balancing            TRUE
_parallel_min_message_pool          64560
_parallel_recovery_stopat           32767
_parallel_server_idle_time          5
_parallel_server_sleep_time         10
_parallel_txn_global                FALSE
_parallelism_cost_fudge_factor      350

The most important of these hidden parallel parameters is the _parallelism_cost_fudge_factor. This parameter governs the invocation of OPQ by the cost-based SQL optimizer when parallel_automatic_tuning=true. By adjusting this parameters you can control the threshold for invoking parallel queries.

Now let's look at some common DBA undocumented parameters. The following undocumented parameters are the most commonly-used in Oracle administration.

Corruption undocumented parameters

These parameters allow you to ignore corrupt data blocks when your database is corrupted. These should only be used in emergencies.

  • _allow_resetlogs_corruption - This parameter may be the only way to start a db backed-up open without setting backup on tablespaces, it will result in an unsupported system.
  • _corrupted_rollback_segments - The only way to start up with corrupted public rollback segments. This undocumented parameter can be used without fear of invalidating support.
  • _allow_read_only_corruption - This parameter allows the database to be opened even if it has corruption. This should only be used to export as much data from a corrupted database as is possible before re-creating a database. A database that has been opened in this manner should not be used in a normal manner, as it will not be supported.
  • _corrupt_blocks_on_stuck_recovery ? This parameter can sometimes be useful for getting a corrupted database started. However, it probably won't be supported if done without Oracle's blessing. Immediately export the tables needed and rebuild the database if used.

SQL Optimizer undocumented parameters:

These parameters control the internal behavior of the cost-based SQL optimizer (CBO).

  • _fast_full_scan_enabled - This enables (or disables) fast full index scans, if only indexes are required to resolve the queries.
  • _always_star_transformation - This parameter helps to tune data warehouse queries, provided that the warehouse is designed properly.
  • _small_table_threshold - This sets the size definition of a small table. A small table is automatically pinned into the buffers when queried. Defaults to 2 percent in Oracle9i.

Data Buffer behavior parameters:

For the very brave DBA, you can change the caching and aging rules within the Oracle db_cache_size and change the way that Oracle keeps data blocks in RAM memory. While these parameters are somewhat dangerous, some savvy DBAs have been able to get more efficient data caching by adjusting these values:

  • _db_aging_cool_count - Touch count set when buffer cooled
  • _db_aging_freeze_cr - Make CR buffers always be FALSE too cold to keep in cache
  • _db_aging_hot_criteria - Touch count which sends a buffer to head of replacement list
  • _db_aging_stay_count - Touch count set when buffer moved to head of replacement list
  • _db_aging_touch_time - Touch count which sends a buffer to head of replacement list
  • _db_block_cache_clone - Always clone data blocks on get (for debugging)
  • _db_block_cache_map - Map / unmap and track reference counts on blocks (for debugging)
  • _db_block_cache_protect - Protect database blocks (true only when debugging)
  • _db_block_hash_buckets - Number of database block hash buckets
  • _db_block_hi_priority_batch_size - Fraction of writes for high priority reasons
  • _db_block_max_cr_dba - Maximum Allowed Number of CR buffers per dba
  • _db_block_max_scan_cnt - Maximum number of buffers to inspect when looking for free
  • _db_block_med_priority_batch_size - Fraction of writes for medium priority reasons

A Case study in undocumented parameters

If you have a limited number of active sessions you may wish to override the PGA governor that only allows any single task to consume 5% of the total PGA.  Laurent Schneider notes in Oracle MOSC that overriding the PGA defaults made a large batch processes run more than 8x faster:

"I set appropriate values for pga_aggregate_target and _pga_max_size...

alter system set pga_aggregate_target=6G; 
alter system set "_pga_max_size"=2000000000; 

...and I gave the query some hints "NOREWRITE FULL USE_HASH ORDERED".

As a result, it boosted my query performance from 12 hours to 1.5 hour."

Ah, if only it were that easy, just change an undocumented setting and batch jobs magically run times faster.  Laurent Schneider notes some perils and reliability issues relating to this parameter and says "this parameter often leads to an ORA-4030, even when plenty of memory available, for some obscure reasons". 

While Laurent abandoned the undocumented approach in favor of parallelizing the batch job, the promise of eight times faster execution speeds are very tempting.  Once you get permission from Oracle Technical Support to set an undocumented parameter, they can work with  to resolve errors.  While they may not address bugs, they may be able to provide alternatives and workarounds.

Also see these hidden parameter tips:



These Oracle undocumented parameters are especially useful to the senior Oracle DBA who needs to go beyond the recommended level of detail and wants to change the internal behavior of their SGA. The undocumented parameters are also a lifesaver for performing re-starts of corrupted databases, but we must always remember that these parameters are hidden for a reason. They are very powerful and undocumented, so you should only play with them if you have a clear understanding about how they change the internal behavior of Oracle.

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational