Top 13 Undocumented Oracle Initialization Parameters

The following list is my list of the top 13 undocumented initialization parameters in order of importance. Your top 13 may vary somewhat, depending on your need for one of these parameters. Although the following warning describes well the risks associated with using these parameters, I note that the fastest RAC TPC (Transaction Processing Council) benchmark uses 17 undocumented parameters, as do many of the TPC benchmarks that I’ve seen.

CAUTION!!

These 13 parameters are neither supported by Oracle, nor do I recommend them on a production system. Use them only if directed by Oracle Support and if you have thoroughly tested them on your crash-and-burn system (and your closest friend has been using them for years). Undocumented initialization parameters can lead to database corruption (although some of them can get your database back up when it becomes corrupted).

• _ALLOW_RESETLOGS_CORRUPTION This parameter saves you when you have corrupted redo logs. It allows the database to open with the datafiles at different SCN synchronization levels. This means some datafiles may contain changes that other datafiles do not (like the RBS or UNDO tablespace). This parameter may allow you to get to your data, but there is no easy way to determine if the data that is available after using this parameter is logically consistent. Regardless of data consistency, the DBA has to rebuild the database afterward. Failure to do so results in multiple ORA-600s occurring within the database at a later time.
• _CORRUPTED_ROLLBACK_SEGMENTS Setting this parameter can be a means of last resort when you have corrupted rollback segments, which you can list with this parameter to be skip. The _CORRUPTED_ROLLBACK_SEGMENTS parameter can force the database open after a failed recovery, but at a very high cost. _CORRUPTED_ROLLBACK_SEGMENTS allows the database to open by assuming every transaction in the rollback segments is a complete, committed transaction. This leads to logical corruption throughout the database and can easily corrupt the data dictionary. An example would be where you transfer money from one bank to another. The transaction would only be complete if you can verify that all parts of it are complete. In Oracle, when creating a table, think of all the individual dictionary objects that are updated: fet$, uet$, tab$, ind$, col$, etc. By setting this parameter, you allow table creation to succeed, even if only fet$ was updated, but not uet$, or even if tab$ was updated, but not col$. Use it when you have no other means of recovery and export/import/rebuild soon after.

CAUTION

These first two parameters do not always work or may corrupt the database so badly that you cannot perform once the database is open. If they are used and do not work, then Oracle Support cannot do anything to salvage the database if the DBA breaks down and calls Support, but the DBA can do several things before using these parameters that will allow other recovery methods to be used afterward. So if you must use these parameters, please ensure that you use them with the help of Oracle Support. One good reason to use Oracle Support in this effort is the fact that the _ALLOW_RESETLOGS_CORRUPTION parameter is problematic, often requiring an event to be set as well, to get the database open.

• _HASH_JOIN_ENABLED Enables/disables hash joining if you have the memory needed. The default value is TRUE.
• _IN_MEMORY_UNDO Make in-memory undo for top-level transactions (Oracle’s IMU really messed up my block-level tuning presentation as blocks in memory became more complex). The default is TRUE. Some applications have you set this to FALSE, so please check your application carefully for this parameter. This is one of the things that makes Oracle fast, so be careful changing this parameter!
• _TRACE_FILES_PUBLIC This parameter allows users to see the trace output without giving them major privileges elsewhere. The default is FALSE; both Oracle/SAP set this to TRUE.
• _FAST_FULL_SCAN_ENABLED This allows fast full index scans if only the index is needed. The default is TRUE, but Applications often recommends this be set to FALSE.
• _KSMG_GRANULE_SIZE This is the granule size multiple for SGA granule pieces of memory such as SHARED_POOL_SIZE and DB_CACHE_SIZE.
• _HASH_MULTIBLOCK_IO_COUNT Number of blocks that a hash join will read/write at once.
• _INDEX_JOIN_ENABLED Used to enable/disable the use of index joins. Default is TRUE.
• _OPTIMIZER_ADJUST_FOR_NULLS Adjust selectivity for null values. Default is TRUE.
• _TRACE_POOL_SIZE Trace pool size in bytes.
• _B_TREE_BITMAP_PLANS Enable the use of bitmap plans for tables with only b-tree indexes. The default is TRUE, but many applications recommend setting this to FALSE.
• _UNNEST_SUBQUERY Enables the unnesting of correlated or complex subqueries. The default is TRUE.

TIP

Undocumented initialization parameters can corrupt your database! Some of them can help you salvage a corrupted database. Try to use these only when all other choices have failed and with the help of Oracle Support.

Here are five additional initialization parameters that you can use for latch contention:

• _KGL_LATCH_COUNT Number of library cache latches (set this to the next prime number higher than 2*CPU). Setting this parameter too high (>66) causes ORA-600 errors (Bug 1381824). KGL stands for Kernel Generic Library or the Library Cache portion of the shared pool (there is also a Dictionary Cache portion). Many *KGL* undocumented parameters are available for debugging and improving shared pool performance.
• _LOG_SIMULTANEOUS_COPIES The number of redo copy latches (or simultaneous copies allowed into the redo log buffer). Redo records are written to the redo log buffer requiring the redo copy latch when changes are made. Use this parameter to reduce the contention on multi-CPU systems.
• _DB_BLOCK_HASH_BUCKETS Must be prime (set to next prime number higher than 2 * Cache buffers) in version 9i and 10g (there is an algorithm change in 11g). This parameter should not be a problem or need to be set as of 10g. On my system, this value was set to a default of 1048576 when there were only 332930 db block buffers (certainly eliminating hash chain issues). A _DB_BLOCK_HASH_LATCHES parameter (set to 32768, by default, in my system) is also available. My system is just under 7G MEMORY_TARGET.
• _DB_BLOCK_MAX_CR_DBA The maximum number of consistent read (CR) blocks for a given database block address (DBA). The default is 6 (six copies maximum of a CR block). Before this parameter was set, large use applications updating many rows within the same block caused so many CR versions that there were incredible issues with latches on the hash chain looking for the correct version of a given block (see the previous parameter for related information).
• _SPIN_COUNT Determines how often the processor takes a new request (reduces CPU time-outs). Also determines how many times a process tries to get a latch until it goes to sleep (when it is a willing-to-wait latch). Many processes spinning to get a latch can cost a lot of CPU, so be careful if you increase this value. In Oracle 7, this parameter was called the _LATCH_SPIN_COUNT.