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, it is impossible to cover them all in this short article. Let's look at performance-related parameters first.
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. Here is a 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):
COLUMN parameter FORMAT a37 COLUMN description FORMAT a30 WORD_WRAPPED COLUMN "Session VALUE" FORMAT a10 COLUMN "Instance VALUE" FORMAT a10 SET LINES 100 PAGES 0 SELECT a.ksppinm "Parameter", a.ksppdesc "Description", b.ksppstvl "Session Value", c.ksppstvl "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 '/_parallel%' escape '/' ; 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
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.
About the Author
Don Burleson is one of the world's top Oracle Database experts with more than 20 years of full-time DBA experience. He specializes in creating database architectures for very large online databases and he has worked with some of the world's most powerful and complex systems. A former Adjunct Professor, Don Burleson has written 32 books, published more than 100 articles in national magazines, serves as Editor-in-Chief of Oracle Internals and edits for Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Don's Web sites include DBA-Oracle, Remote-DBA, Oracle-training, remote support and remote DBA.
- Mike Ault's Oracle Internals Monitoring & Tuning Scripts – Mike Ault, 2003, Rampant TechPress
- Oracle Utilities - Using Hidden Programs, Import/Export, SQL*Loader, Oradebug, Dbverify, Tkprof – Dave Moore, 2003, Rampant TechPress
- Using the swap_join_inputs hint – OTN
- Using the bitmap join index – OTN
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.