Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Using Oracle's Undocumented Initialization Parameters


Oracle Tips by Burleson
 

Almost every DBA knows about Oracle's documented initialization parameters; most of them are easy to look up and use. However, not everyone knows about the undocumented parameters, and few know how or when to use them. There are many parameters that Oracle will not allow DBAs to use unless specifically directed to by Oracle support. DBAs should be aware that use of certain undocumented parameters will result in an unsupported system. I will attempt to identify where parameters should be used only under Oracle support’s guidance and where a DBA can safely utilize these high-powered tools.

What’s the Difference?

The major difference between the documented and undocumented parameters is that the undocumented parameters begin with an underscore character. In many cases, the undocumented parameters were either documented at one time in previous releases or will be in future releases. Those parameters that have been, or will be, documented are usually those that are safe to use. The undocumented parameters that have never been really documented and the ones that never will be are those for which it is difficult to determine their use safety. When in doubt, get guidance from Oracle support. And always back up the database before using any of the questionable parameters so that you have a way to restore a "supported" version on which you can get help.

So Many Parameters, So Little Time

Let’s begin this discussion with what an expert has to say on the topic. Rich Niemiec of TUSC cites, in Oracle Performance Tuning Tips and Techniques (Oracle Press, 2000), the following 13 undocumented parameters as his favorites:

_ALLOW_RESETLOGS_CORRUPTION. May be only way to start a db backed-up open without setting backup on tablespaces; will result in unsupported system. See detailed section on using _allow_resetlogs_corruption which follows..

_CORRUPTED_ROLLBACK_SEGMENTS. Only way to start up with corrupted public rollback segments. Can be used without fear of desupport.

_ALLOW_READ_ONLY_CORRUPTION. Allows you to open a database 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. You should not use a database for normal use that has been opened in this manner, as it will not be supported.

_SPIN_COUNT. Sets the number of spins a process will undergo before trying to get a latch. If CPU is not fully loaded, a high value may be best; for a fully loaded CPU, a smaller value may help. Usually defaults to 2000. Can be changed without fear of desupport. Flips from undocumented to documented depending on version.

_LOG_ENTRY_PREBUILD_THRESHOLD. Formerly documented, now is undocumented; is the minimum size of entry in blocks that will be prebuilt for redo log entries; usually set to 30.

_LATCH_SPIN_COUNT. Shows how often a latch request will be taken

_DB_BLOCK_WRITE_BATCH. Formerly documented, now undocumented; the number of blocks that the db writers will write in each batch; defaults to 512 or DB_FILES*DB_FILE_SIMULTANEOUS_WRITES/2 up to a limit of one-fourth  the value of DB_BLOCK_BUFFERS.

_CPU_COUNT. Flips from undocumented to documented. Should be set automatically; but on some platforms, doesn't; set to the number of CPUs. This determines several other parameters.

_INIT_SQL_FILE. The initialization  SQL script run by Oracle when a db is created. This should be sql.bsq; if you change it, you may not be supported.

_TRACE_FILES_PUBLIC. Changes the privileges on trace files such that everyone can read them. Should be Okay to change at will.

_FAST_FULL_SCAN_ENABLED. Enables (or disables) fast full index scans if only indexes are required to resolve the queries. Change at will.

_CORRUPT_BLOCKS_ON_STUCK_RECOVERY. Can sometimes get a corrupted db up; you probably won't be supported if done without Oracle support’s blessing. Immediately export the tables you need and rebuild the db if used.

_ALWAYS_STAR_TRANSFORMATION. Helps to tune data warehouse queries if you have a properly designed data warehouse.

_SMALL_TABLE_THRESHOLD. Sets the size of table considered a small table. A small table is automatically pinned into the buffers when queried. Defaults to 2 percent in Oracle.

Niemiec lists another 13 parameters that bear mentioning:

_DEBUG_SGA. Has no noticeable effect.

_LOG_BUFFERS_DEBUG. Slows things down.

_REUSE_INDEX_LOOPS. The blocks to examine for index block reuse.

_SAVE_ESCALATES. Not sure what it does; no measurable effects. According to Steve Adams, Oracle may take an exclusive lock earlier than required to save lock escalations; if this is set to FALSE, it won’t. Don’t mess with it.

_OPTIMIZER_UNDO_CHANGES. Reverts to pre-6.0.3 optimizer for IN statements. This one is required in certain versions of the Oracle Applications. According to K. Gopalakrishnan of the India Times, this parameter has nothing to do with cost-based optimization. In Version 6, somewhere around V6.0.36, if he remembers correctly, Ed Peeler made several changes to the way the optimizer made choices. Many queries in the existing Oracle Applications at that time relied in particular on the old way the optimizer worked to be certain to come up with the intended plan. The code was written prior to hints, so many tricks were used to influence the optimizer to come up with a certain plan and were scattered throughout the code. When the new database release was made, tests showed that many critical applications processes ran catastrophically slower. Fortunately, the old optimizer code had not been stripped out yet, and a way to use the old optimizer code was allowed via the “hidden*” parameter _undo_optimizer_changes (probably so that Oracle could test the old versus the new optimizer internally). So Oracle released this parameter to the applications community. This was long before the cost-based optimizer even existed.

_DSS_CACHE_FLUSH. Enables full cache flush for parallel; effect is not measurable.

_DB_NO_MOUNT_LOCK. Doesn't get a mount lock; no noticeable effect.

_AFFINITY. Defaults to TRUE; enables or disables CPU affinity.

_CORRUPT_BLOCKS_ON_STUCK_RECOVERY. Tried in a corrupt recovery and didn't do anything; no noticeable effect

_CURSOR_DB_BUFFERS_PINNED. Lists additional buffers a cursor can pin; no noticeable effect. The default value is max(db_block_buffers/processes-2,2). One note from Oracle development says that playing around with this parameter will almost always damage performance, so it was made hidden starting in 8.0.3.

_DB_BLOCK_NO_IDLE_WRITES. Disables writes of blocks when the db is idle; may result in an unusable system if used. If you get an instance Crash with the following Error ORA-00600: internal error code, arguments: [kcbbzo_2], [1], [25974], setting this parameter may help.

_DISABLE_LOGGING. Even I won't support you if you set this one. If this parameter is set to TRUE, redo records will not be generated, and recovery is not possible if the instance crashes or is terminated with shutdown abort.

_IO_SLAVES_DISABLED. Disables I/O slaves, probably not the safest way.

Other Notables

Other parameters, covered in various notes on Metalink, ORACLE8i Internal Services for Waits, Latches, Locks and Memory, by Steve Adams (Oracle Press, 1999), and other boards include the following:

_DISTRIBUTED_LOCK_TIMEOUT. Sets the amount of time a lock used in a distributed transaction will be held, usually 5. Safe to reset without worry of desupport.

_ROW_CACHE_CURSORS. Maximum number of cached recursive cursors used for data dictionary cache queries. This sets the size of the array in the PGA used for the data dictionary cursors. It takes 28 bytes per cached cursor.  Long-running procedures that do a lot of data dictionary lookups can benefit from higher values. If the view K$KQDPG shows excessive overflows of the cache, a small increase may help. This view is for the current process only. The ORADUBUG or DBMS_SYSTEM.SET_EV can be used to grab a trace of a different process. In the resulting trace, find the X$KQDPG structure, count 12 blocks, and use the hexadecimal values to get the values. Recursive calls can be caused by firing of database triggers, execution of SQL statements within stored procedures and functions and anonymous PL/SQL blocks, and enforcement of referential integrity constraints. Setting this value higher may reduce these recursive calls.

_LOG_BLOCKS_DURING_BACKUP. From Oracle support, but we still do not recommend the use of this parameter on production systems. There is a known problem with performance on systems using certain configurations of Veritas with Solaris 2.6. Contact Veritas for further information. Use of this parameter will result in an unsupported system.

_DB_WRITER_SCAN_DEPTH_INCREMENT. Controls the rate at which the scan depth increases if the db writer is idle; defaults to one-eighth the difference between the upper and lower bounds of the scan depth.

_DB_WRITER_SCAN_DEPTH_DECREMENT. Controls the rate at which the scan depth decreases of the db writer is working too hard. The X$KVIT table gives the details of the db writer; you can find the details using the following SELECT statement, courtesy of Steve Adams at www.ixora.com.au:

SELECT
Kvitdsc,
Kvitval
FROM
Sys.x$kvit
WHERE
Kvittag IN ('kcbldq', 'kcbsfs') OR
Kvittag LIKE 'kcbsd_'
/

_DB_LARGE_DIRTY_QUEUE. Defaults to one-sixteenth of the write batch size limit. Sets the frequency at which DBWR writes. Should be decreased gradually in one-block increments until buffer waits are eliminated. The cost of reducing this parameter is in CPU usage, so shouldn't be changed without good reason.

_DB_BLOCK_MAX_SCAN_CNT. Defaults to one-fourth of the working set size, rounded down. If this number of blocks is scanned, then the free buffer request fails. The failure of a scan increments the dirty buffers inspected parameter in the V$SYSSTAT view. If there are no free buffer waits, and there are dirty buffers inspected, then there is a potentially serious problem and the parameter should be tuned.

_ENQUEUE_HASH_CHAINS. Derived from the PROCESSES parameter, so is the value for ENQUEUE_RESOURCES, which is directly affected by _ENQUEUE_HASH_CHAINS; therefore, if you explicitly set ENQUEUE_RESOURCES, you will need to adjust _ENQUEUE_HASH_CHAINS to a prime number just less than the value of ENQUEUE_RESOURCES. If the value of _ENQUEUE_HASH is not set to a prime number, long enqueue hash chains could develop. Unless you are receiving ORA-00052 or ORA-00053 errors, this parameter and ENQUEUE_RESOURCES probably don’t need adjusting. The default value is equal to CPU_COUNT.

_ENQUEUE_LOCKS. Use V$RESOUCE_LIMIT to see if you need more locks or ENQUEUE_RESOUORCES. A lock takes 60 bytes; a resource, 72 bytes. To increase the enqueue_locks value in v$resource limit, you have to increase the _enqueue_limit value in init.ora.

_USE_ISM. Determines if intimate shared memory is used. On some platforms this can cause problems, and _USE_ISM should be set to FALSE instead of its default of TRUE.

_DB_BLOCK_HASH_BUCKETS. In releases prior to 9i, this was set to twice the value of DB_BLOCK_BUFFERS. Unfortunately, this should be set to a prime number to keep the hash chains from getting out of hand; therefore, on releases prior to 9i, resetting this to a prime number near the value of twice the DB_BLOCK_BUFFERS is a good idea and will not result in loss of support. According to Steve Adams, _db_block_hash_buckets could be used to set both the number of hash chains and latches in previous releases. From 7.1, it was constrained to prime numbers, and used to default to next_prime(db_block_buffers / 4).

db_block_buffers, and the _db_block_hash_latches parameter must be used to control the number of hash latches if necessary. This parameter is constrained to binary powers so that Oracle can calculate which latch to use with a simple SHIFT operation, rather than a DIVIDE operation. The default number of hash latches depends on db_block_buffers. If db_block_buffers is less than 2052 buffers, then the default number of latches is 2 ^ trunc(log(2, db_block_buffers - 4) - 1).

If db_block_buffers is greater than 131075 buffers, then the default number of latches is 2 ^ trunc(log(2, db_block_buffers - 4) - 6). If db_block_buffers is between 2052 and 131075 buffers, then there are 1024 latches by default. Sites that have used _db_block_hash_buckets to combat cache buffer chains latch contention under previous releases should allow the value to default when upgrading to Oracle 8i. Remember that contention for these latches is almost always a symptom of one or more blocks being very hot due to unresolved application or SQL tuning problems. Adams may be correct; however, I have seen improvements on 8.1.7 by setting these values as in previous releases.

_DB_BLOCK_HASH_LATCHES. Usually set to 1024, which is usually too small; set it to near 32K (32768) for better performance. Up to release 8.0.

_KGL_LATCH_COUNT. Value defaults to 7. This determines the number of latches that control the shared pool. If you need a large shared pool, or have a large number of items that are placed in the shared pool, set this to a larger prime number. According to Oracle support: In general, on systems that have multiple CPUs and/or when parsing a lot of SQL with few shared cursors, it is recommended to set it to 1. On all other systems it must be set to the default, in which case the latch contention may not be significant compared to the cost of building a new cursor for each SQL statement. However, I tend to agree with Steve Adams: The default is the least prime number greater than or equal to cpu_count. The maximum is 67. It can safely be increased to combat library cache latch contention, as long as you stick to prime numbers. That said, it is only effective if the activity across the existing child library cache latches is evenly distributed as shown in V$LATCH_CHILDREN.

Here are some undocumented parameters for maximizing DBWR performance:

* Increase _db_block_write_batch (hidden parameter in Oracle8, obsolete in Oracle8i).

* Decrease _db_block_max_scan_count, _db_writer_scan_depth, and _db_writer_scan_depth_increment to decrease the dirty buffer backlog.

* Adjust_db_writer_chunk_writes, which controls the number of writes DBWR should try to group into one batch I/O operation.

* Adjust _db_block_med_priority_batch_size for regular writes.

* Adjust _db_block_hi_priority_batch_size for urgent writes such as when LRUW is full or there are no free buffers or when free buffers are below limit.

Parameters That Must Be Used

Here I attempt to list the specific conditions when the following undocumented parameters must be changed:

_DB_HANDLES. In versions before 8.1.7.2, if set too high, this may cause ORA-04031 because of bug 1397603.

_DB_HANDLES_CACHED. Before 8.1.7.2, may need to be set to 0.

_ELIMINATE_COMMON_SUBEXPR. If left set to TRUE, may cause some queries using IN clauses to return too many rows or bad answers; set to FALSE in 8.1.7.0 and 8.1.7.1.

_IGNORE_DESC_IN_INDEX. May have to set to TRUE on some platforms (such as AIX) in versions 8.1.6 and 8.1.7 if you get ORA-03113 and ORA-07445 errors when you try different management or DML operations on descending indexes. On some platforms may have to use the event-setting “10612 trace name context forever, level 1” to reset it.

_INIT_SQL_FILE. This may have to be reset to use 32K block sizes on some platforms, as they will use the sql.bsq.32K file instead of the sql.bsq file.

_MV_REFRESH_SELECTIONS. Is 8.1.7 only, and if you have multilevel joins in your materialized view, setting this to TRUE may allow fast refreshes.

_NEW_INITIAL_JOIN_ORDERS. Set to TRUE when upgrading to 11.5.4 on 8.1.7.

_OGMS_HOME. Used in Oracle Parallel Server. Set explicitly or may default to /tmp, which could result in file loss and inability to start Oracle Parallel Server.

_SQLEXEC_PROGRESSION_COST. For Oracle Application 11.5.4 in 8.1.7, set to 0.

_UNNEST_SUBQUERY. Where you cannot use the UNNEST hint, may improve performance.

_USE_ISM. Set to FALSE on Solaris 2.6 or you may have system crashes and poor performance (depending on Oracle version may be USE_ISM).

_DB_ALWAYS_CHECK_SYSTEM_TS. Always perform block check and checksum for SYSTEM tablespace; this defaults to TRUE. You may need to set this to FALSE after upgrade from a pre-8i version of Oracle. If you need to set this to FALSE to restart the DB, immediately export and rebuild the database, as it detected corruption in the data dictionary (probably in the C_TS# cluster, but that is another story).

_DB_CACHE_ADVICE. Will turn on the buffer cache sizing advisory if set to ON to help you perform cache sizing.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter