关闭

ORACLE隐含参数

853人阅读 评论(0) 收藏 举报

来源:http://jametong.itpub.net/

                                           ORACLE隐含参数
Last Updated on 01-JUL-97

   select ksppinm from x$ksppi
                where substr(ksppinm,1,1) = '_';

The following is a list of undocumented parameters.
1. _db_block_cache_protect
    On VMS, the DB_BLOCK_CACHE_PROTECT mechanism has been made much
    faster.  During normal use, having it turned on shouldn't be
    noticeable (the degradation is less than 1%).  Developers who
    link non-share will need PSWAPM privilege to use this feature.
    When DB_BLOCK_CACHE_PROTECT is turned on, developers may either
    use the VMS mailboxes with the M (MEMORY_LOG) command
    or they may just examine the ring buffer in the PGA (index
    SMPREI_, array SMPREB_) to determine what buffer requests have
    been made recently.
    DB_BLOCK_CACHE_PROTECT will prevent certain corruption from getting to
    disk; although, it may crash the foreground of the instance.  It will help
    catch stray writes in the cache. When you try to write past the buffer
    size in the sga, it will fail first with a stack violation.
    It seems that the db_block_cache_protect has a significant performance
    overhead.  Preliminary testing shows that it has considerable overhead
    (a single update took twice as long with the parameter set to TRUE).
2. _db_block_compute_checksums
    There is another new init.ora parameter, DB_BLOCK_COMPUTE_CHECKSUMS,that
    controls whether a checksum is put into every block before the block is
    written to disk.  The default is FALSE.  A block read validates an
    exiting checksum whether or not this option is enabled.  A block is marked
    as corrupt if a checksum fails.
    It helps determine corruption due to hardware problems.  The incarnation
    number and the sequence number are added to the end of the block to help
    catch corruption.
    If the problem (corruption) is in the middle of the block
    this test will not detect it. To detect this problem a checksum may be
    generated in the block header before every write and verified on every read.
3. _db_block_hash_buckets= "Number of database block hash buckets"
    The number of hash buckets is
    a) by default to be set to a prime number;
    b) coerced to a prime if there is an init.ora parameter setting.
    The value, if not a prime number > 2, is rounded up to the next highest
    prime.
    I would tend not to change it unless there is latch contention on the
hash
    chains. raising it to equal the number of buffers would clearly remove
any
    contention (basically, this is just saying that each buffer lives on its
own
    hash chain).  Having it set too small would mean that we might have to
scan
    over lots of buffers to find the one we want. I think the default is to
    make it 1/4 of the total number of buffers
4. _db_block_multiple_hashchain_latches
    "Use one latch per hash chain"
5. _db_handles
    "System-wide simultaneous buffer operations"
6. _db_handles_cached
    "Buffer handles cached each process"
7. _wait_for_sync
    " Wait for sync on commit "
    Wait_for_sync is an oracle generic parameter which, when set to
    false, will allow the system to complete commits without waiting
    for the redo-log buffer flushes to complete.
8. _db_block_max_scan_cnt="Maximum number of buffers to inspect when
    looking for free buffer"
    DB_BLOCK_MAX_SCAN_CNT is an init.ora parameter which specifies
    the number of unavailable buffers a process should scan before signaling
    DBWR to write dirty buffers from the buffer cache to disk.
9. _db_writer_scan_depth
    "Number of LRU buffers for dbwr to scan when looking for dirty buffers"
10a. _db_writer_scan_depth_increment
    "Add to dbwr scan depth when dbwr is behind"
10b. _db_writer_scan_depth_decrement
    Subtract from dbwr scan depth when dbwr is working too hard
11. _db_large_dirty_queue
    "Number of buffers which force dirty queue to be written
12. _db_block_write_batch
    Number of blocks to group in each DB Writer IO
    specifies the no of blocks to be written to the disk in one write
operation.
    Should be increased till write wait time and write complete waits starts
to
    increase.
    DBWR Free Low is the number of times DBWR is invoked because a user
    process found at least DB_BLOCK_WRITE_BATCH/2 buffers on the dirty list.
    This parameter specifies the number of blocks which should be written to
    disk at one time.
    This parameter should only be increased until the statistics
    Write Complete Waits and Write Wait Time show growth.  Write Complete
    Waits is the number of times a process waited for DBWR
    to write a current block before making a change to a buffer.
13. _db_block_cache_clone
    "Always clone data blocks on get (for debugging)"
    This parameter setting has a significantly adverse affect on performance
    and we were told to run without it.
14. _controlfile_enqueue_timeout
    /* use parameter value (default is 900) */
    /* System Parameter: control file enqueue timeout in seconds */
15. _db_no_mount_lock
    add hidden parameter to not acquire mount lock
    If hidden int.ora parameter _db_no_mount_lock is set to TRUE
    then no mount locks are acquired when the the database is mounted
    exlusive. This allows two databases with the same name to be
    simultaneously mounted. Note that this circumvents the mechanism
    to prevent two simultaneous startups of the same database, and is
    thus a dangerous parameter to set. This only affects ports that
    ar compiled with the SYS_DFS option enabled (i.e. VMS only).
    It does not get a mount lock on the database name during startup.
    This allows 2 instances with the same name to run on one machine

16. _log_checkpoint_recovery_check
    Check redo log after checkpoints.
    Add debugging code to check the red log after a checkpoint.  This
    code is intended to help find a problm that is generating ora-600 [3020]
    during recovery.  This code is enabed with a new init.ora parameter:
    _log_checkpoint_recovery_check=XXX, where XXX is the number of redo
    blocks to check.   This is called in LGWR after every checkpoint. If the
    init.ora parameter "_log_checkpoint_recovery_check" is zero (default)
    it does nothing. If it is a positive value then that many blocks of
    redo are scanned to see that the data file blocks on disk could be
    recovered if there was an immediate crash. This code was introduced
    to catch an elusive bug that results in OERI(3020) errors
    occasionally during crash recovery.
17. _switch_on_stuck_recovery
    REDO GENERATION HAS BEEN CHANGED SO THAT WE SHOULD NEVER ENCOUNTER
    A CHANGE IN THE FUTURE OF THE BLOCK. APPLYING REDO IN SCN ORDER NOW
    INSURES THAT CHANGES ARE APPLIED IN INCARNATION/SEQUENCE # ORDER. THERE
    IS A LOT OF CODE IN RECOVERY THAT IS NO LONGER NEEDED BECAUSE OF THIS
    CHANGE. THIS INCLUDES THE CODE TO BACKUP AND RESCAN AFTER REPAIRING
    CORRUPTIONS. THE CODE IS BEING LEFT IN TO AVOID LARGE CHANGES JUST
BEFORE
    V7.0 PRODUCTION RELEASE. THE FOLLOWING WILL INSURE THE UNNEEDED CODE
    IS NEVER EXECUTED UNLESS REDO WAS GENERATED BY A BETA RELEASE OR THE
SECRET
    INIT.ORA PARAMETER _SWITCH_ON_STUCK_RECOVERY IS SET TO TRUE.
    Check redo Log contents after checkpoint. This is called in LGWR after
    every checkpoint. If this parameter is zero (default) it does nothing.
    If it is a positive value then that many blocks of redo are scanned to
see
    that the data file blocks on disk could be recovered if there was an
    immediate crash. This code was introduced to catch an elusive bug that
    results in OERI(3020) errors occasionally during crash recovery.
    Checkpoint recovery check: this is the number of redo blocks that
    kcracl will verify after every LGWR generated checkpoint. Defaults to
zero
    for no checking.   When opening the named offline log for redo
application
    and to recalculate future change thread switching this parameter is
used.
18. _log_io_size=redo log IO chunk size (blocks/write)
    /* System Parameter: IO chunk size */
    1. that the value is o/s dependent.
    2. if left at 0, the value will be automatically determined for each log
       file.
19. _log_buffers_debug
    /* debugging: fill redo buffers with [well known] junk after writes */
    "debug redo buffers (slows things down)"
20. _log_debug_multi_instance
    /* debugging : pretend multi-instance */
    "debug redo multi instance code"
21. _log_entry_prebuild_threshold
    /* redo entry pre-build threshold */
    /* this is a bad idea for a uniprocessor , and is only helpful for a
    multiprocessor when there is latch contention */
    LOG_ENTRY_PREBUILD_THRESHOLD determines the maximum size of a redo entry
    to prebuild before the copy to the log buffer.  Increasing this
parameter
    reduces the time that the redo copy latch is held.  This parameter
should
    not be modified if it is a single processor environment or there will be
    memory contention.
22. _disable_logging
    If this is true, redo records will not be generated -- no recovery is
possible
    if the instance crashes.  It is mainly used for getting good
benchmarking
    results.
    Default is false
23. _log_blocks_during_backup
    TRUE value implies before any change is made to a db_block in the buffer
    cache, a *complete image* of the block is copied to the redo
    redo log.  (This explains why excessive redo would be generated for
    datafiles excessive redo would be generated for datafiles in hot backup
    mode.) There is a new init.ora parameter, LOG_BLOCKS_DURING_BACKUP,
    that controls whether block images ar written to the redo log during
    hot backup.
    Default is TRUE for VM, but port specific with the default defined in
    sparams.h.  This may beset to FALSE if the Oracle block size equals
    the O/S physical sector sie or if it is otherwise ensured that hot
backup
    reads consistent versios of blocks even if those blocks are being
written
    at the time.  Put anther way, this may be set to FALSE on machines that
    can guarantee the aomicity of a single block I/O request.
    Default is true
    Problem is due to split blocks.
24. _allow_resetlogs_corruption
    Added new secret init.ora parameter to override error 1138.
    When set to TRUE the
    resetlogs option will be allowed even if there are hot backups that need
    mor redo applied. Unless you are certain that absolutely all redo,
includig
    the online logs, has been applied, then a full export and import mst be
    done to insure the database is internally consistant.
    from 6.0 code
    /* if we crashed/shutdown during a hot backup it is over now and we
    ** are no longer logging blocks. If they did a manual recovery
    ** followed by a NORESETLOGS then the hot backup recovery flag can
    ** be cleared. The hot backup recovery flag may alsoneed clearing
    ** if RESETLOGS was allowed because of _allow_resetlogs_corruption
    ** parameter. */
    from the c file kcv.c 7.0 code
   /* if the resetlogs option is in effect we save the highest checkpoint
   ** scn that we see, to be used as the incomplete recovery scn. It only
   ** gets used if a resetlogs is done without any recovery. Only backup
   ** control file recovery will be allowed and it always rewrites the
   ** incomplete recovery scn. We use the highest scn we can find so that
   ** new resetlogs scn will be greater than anything in the database.
   ** This may not be strictly true if the user sets
   ** _allow_resetlogs_corruption, and there is a fuzzy file. Note that
   ** since resetlogs does not look at the log file headers, datafile
   ** checkpoints are all we have to look at.

25. _reuse_index_loop
    "number of blocks being examine for index block reuse"
    /* secret system parameter to control how agressive we should walk the
free
    ** list when attempting to reuse block - default is 5.
    ** Set to 0 for fast index operation which is susceptible to growth,
    ** Set to > 5 for slower index op but more agressive in reusing blocks
*/
        Controls the amount of work done when looking for a block to reusse
for     n index entry.  The value determines the number of blocks to
        check on the freelist when looking for a reusable block.
26. _mts_load_constants
    /* hidden init.ora to set server load balancing constants */
    /* fill in load balancing parameters (from _mts_load_constants) */
    * PID Controller - calculate control on number of servers using:
    * control = Kp * err + Kd * delta(err) + Ki * sum(err)
    * where Kp = proportional, Kd = derivative constant, Ki  = integral
constant
    * Kp,Kd,Ki can be changed with the hidden parameter _mts_load_constants
    * in order to tune the system as desired.
    This values should only be changed after gathering enough information to
    determine that the mts is not optimal.
27. _mts_fastpath
    /* hidden init.ora to enable dispatcher fastpath */
    default is false
     * Return TRUE if the system should implement the dispatcher network
     * fastpath.  When enabled, the dispatcher fastpath causes network i/o
     * to be buffered and only flushed when absolutely necessary.  When not
     * enabled, flushes will occur for every dirty virtual circuit buffer.

*** The following parameters are from the Kernel SQL Library manager
28. _kgl_multi_instance_lock
    Only for debugging.  all the  _kgl_multi_instance_xxxx
    "whether KGL to support multi-instance locks"
    Default is 0
29. _kgl_multi_instance_pin
    "whether KGL to support multi-instance pins"
    Default is 0.
30. _kgl_multi_instance_invalidation
    "whether KGL to support multi-instance invalidations"
    Default is 0.
31. _row_cache_instance_locks
    Kernel SQL Row cache management component, number of row cache instance
    locks
    default is 100
32. _row_cache_buffer_size
    "size of row cache circular buffer"
    default is 200
33. _messages
   " message queue resources - dependent on # processes "
   The asynchronous message mechanism allows  processes  to  send
   messages  to each other.   A process  may send a  message to a
   specified other  process (always without waiting for a reply),
   may wait for  a message to arrive on its queue, and may obtain
   the next message.   Messages sent  to  detached processes  are
   reliably delivered.  Messages sent to foreground processes are
   reliably  delivered  as  long as  the  process is active.  The
   mechanism also permits sending of a simple "reply", which is a
   one-byte message without  queuing.  It should normally be used
   to  reply to  asynchronous  messages,  and  this  is  a  safer
   technique  than regular messages for  responding to foreground
   processes.   This mechanism is not used in single process mode.
34. _cpu_count
    ksb - Kernel Service Background processes
    "number of cpu's for this instance"
    CPU_COUNT has to be set on some platforms in order for Oracle to take
    advantage of multi-processor system, on others it does not have effect
on
    performance since load balancing between processors is handled by the
o/s.
35. _debug_sga
    /* Debug SGA,  don't make the SGA a global section so we can set
watchpoints
36. _enqueue_locks
    ksq1 - Kernal Service enQueues (1)
    Definitions for enqueues client objects, "locks for managed enqueues"
37. _enqueue_hash
    "enqueue hash table length"
38. _enqueue_debug_multi_instance
    "debug enqueue multi instance"
     KST is a trace facility used for "realtime" tracing of events.  Below
     are defined KST macros that will enable the tracing of such things as
     latch operations, memory assignments, etc.  Tracing is done to a per
     process circular buffer stored in the SGA.  Access to these buffers
     is via fixed tables.  Tracing is enabled for classes of events,
     particular events, and ranges of events.
     The tracing state may be dynamically changed with the following syntax
     "ALTER TRACING"
        - "ON"
          - Processes begin logging events using the current enabled events
      - "OFF"
          - Processes stop tracing
        - "ENABLE" <event_string>
          - Add in the events indicated in <event_string> to those which are
            being traced.
        - "DISABLE" <event_string>
          - No longer trace events specified by <event_string>
39._trace_buffers_per_process
    Note that by default, tracing is NOT enabled.  In order to enable
tracing
    at instance startup time, add _trace_buffers_per_process = 1
40. _trace_block_size
    _trace_xxxxx (xxxxx = buffers_per_process, block_size, archive_start,
                 flushing, enabled, get_time_every, archive_dest etc.)
    These parameters are only there for debugging purposes. Customers
    or support will never have to use them.
41. _trace_archive_start
    "start trace process on SGA initialization"
42. _trace_flushing
    "TRWR should try to keep tracing buffers clean"
43. _trace_enabled
    By default, tracing is NOT enabled.  In order to enable tracing,
    _trace_enabled = true
44. _trace_events
    If you wish to enable tracing of waits at instance startup time, you can  either
    add the line '_trace_events = "WAT,ALL"' to init.ora or execute
    'alter tracing enable "WAT,ALL"' in a sqldba session.
    If you wish to limit tracing to specific events, you can use the
    the following syntax:
    alter tracing enable "WAT,<id>,<id>..."
    where "id" is either a specific event number, or an event range
    (event number 1 - event number 2).
45. _trace_archive_dest
    "trace archival destination"
46. _trace_file_size
    "trace file size"
    default is 10000 blocks
47. _trace_write_batch_size
    "trace write batch size"
    default is 32
48. _rollback_segment_initial
    "starting undo segment number"
    Default is 1. DO NOT SPECIFY 0 AS THAT HAPPENS TO BE THE SYSTEM ROLLBACK
49. _rollback_segment_count
    "number of undo segments"
    default is 0
50. _offline_rollback_segments
   If a rollback segment is not accessible because the file it is in
   is offline or corrupted, one can force the system to come up without
   the rollback segment by specifying the rollback segment in init.ora
   paramater '_offline_rollback_segments'. The system will come up by
   estimating the current system commit time since it cannot access
   transaction tble in the rollback segment. The system commit number
   is a conservative guess based on current time, the database creation
   time and the assumed transaction  rate of 8000 tps. ONE MUST MAKE
   SURE THAT THE SYSTEM TIME IS SET CORRECTLY WHEN FORCING THE SYSTEM UP
   USING '_OFFLINE_ROLLBACK_SEGENTS'. A trace is written with information
   about the estimated system commit number.
51. _corrupted_rollback_segments
    Mark a rollback segment as corrupted.
52. _label_tag_cache_size
    /* hidden size of the SGA label tag comparison cache (bitcache) */
     "number of tags in the label tag comparison cache"
    default is 200
53. _trace_get_time_every
    "Number of trace sequence numbers per call to slgcs()"
    default is 200
54. _vms_bg_priority
    "VMS process priority to give to background processes"
    default is 4
55. _sort_use_os_files_as_temporaries
     Use O/S files rather than temp segments for sorting.
56. _log_checkpoints_to_alert
    Whether checkpoint messages should be written to alert.log or not.
Turned
    off in benchmarks.
57. _large_memory_system  :
    Used in internal benchmarks. Doesn't concern us.
    "Configure system to use memory and save cpu cycles, paging, etc
    default is false
58. _wakeup_timeout
   This is WMONs sleeptime between checks of it's queue of processes to
wake.
59. _latch_wait_posting
   enable posting of processes that may be waiting for a latch after a
process
   frees the same latch (set this parameter to a value greater than one
   this parameter to a value greater than one for it to take effect).
60. _sql_connect_capability_code
   allows database links from a pre-7.0.15 release to work with release 7.1.
   It is necessary to set this parameter for database links from a V6 ?

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:14977次
    • 积分:190
    • 等级:
    • 排名:千里之外
    • 原创:4篇
    • 转载:13篇
    • 译文:0篇
    • 评论:0条
    文章分类