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:

_allow_resetlogs_corruption
_column_tracking_level
_db_aging_cool_count
_db_aging_freeze_cr
_db_aging_hot_criteria
_db_aging_stay_count
_db_aging_touch_time
_db_percent_hot_default
_db_percent_hot_keep
_db_percent_hot_recycle
_init_sql_file
_kghdsidx_count
_log_io_size
_realfree_heap_pagesize_hint
_recyclebin
_small_table_threshold
_system_trig_enabled
_trace_files_public
_use_ism
_use_ism_for_pga
_wait_for_sync
Also, see these undocumented parameters from this Oracle IBM terabyte RAM benchmark:
_NUMA_pool_size = 536870912
_collect_undo_stats=false
_awr_restrict_mode=true
_db_writer_flush_imu=false
_db_cache_pre_warm=FALSE
_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
_ksmg_granule_size=268435456
_two_pass=false
_session_idle_bit_latches=3000

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

select
*
from
v$parameter
where
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

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

PROMPT

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:

Oracle 隱藏引數使用

相關文章