RE: _* initialization parameters (old MetaLink paper + Query)

From: Brian MacLean <bmaclean_at_homebid.com>
Date: Tue, 9 May 2000 07:44:58 -0700
Message-Id: <10492.105237@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFB9C5.26178FFC
Content-Type: text/plain;

charset="iso-8859-1"

The standard query used to see them all (as sys or internal):

select x.inst_id,

       x.indx+1 num,
       ksppinm name,
       ksppity type,
       ksppstvl value,
       ksppstdf ISDEFAULT,
       decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE')ISSES_MODIFIABLE,

decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FA LSE')ISSYS_MODIFIABLE, decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')ISMODIFIED,

       decode(bitand(ksppstvf,2),2,'TRUE','FALSE')ISADJUSTED,
       ksppdesc DESCRIPTION

from x$ksppi x, x$ksppcv y
where (x.indx = y.indx)
order by 3;

This is something I found on MetaLink a few years ago. I looked for an updated version but couldn't find one:

Oracle: Explanation of Oracle undocumented parameters Last Updated on 01-JUL-97 The following is a list of undocumented parameters.

_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

by default to be set to a prime number;
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

_db_large_dirty_queue
"Number of buffers which force dirty queue to be written
_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

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.

_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.

_controlfile_enqueue_timeout /* use parameter value (default is 900) */ /* System Parameter: control file enqueue timeout in seconds */
_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
_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.

_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.

_log_io_size=redo log IO chunk size (blocks/write) /* System Parameter: IO chunk size */
that the value is o/s dependent.
if left at 0, the value will be automatically determined for each log

file.

_log_buffers_debug /* debugging: fill redo buffers with [well known] junk after writes */
"debug redo buffers (slows things down)"
_log_debug_multi_instance /* debugging : pretend multi-instance */
"debug redo multi instance code"
_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 ?

Brian P. Mac Lean
Senior Oracle Database Administrator
OCPv8/Oracle Master
HomeBid.Com
8700 N. Gainey Center Drive
Scottsdale, AZ 85258

Tel:480.609.4624 
Cel:602.617.6075 
Fax:480.609.4646 
Net:brian.maclean_at_homebid.com <mailto:Net:brian.maclean_at_homebid.com>

Who needs friends when you can sit alone in your room and drink?

-----Original Message-----
From: djordjej [mailto:djordjej_at_netcom.ca] Sent: Monday, May 08, 2000 6:34 PM
To: Multiple recipients of list ORACLE-L Subject: Re: REF : _* initialization parameters (to all who have replied)

Some info you can find in Oracle Press, Oracle Performance Tuning, Tips&Techniques by Richard Niemiec.

Djordje

-----Original Message-----
From: Benhayoune khalid < benhayoune_at_maroclear.co.ma <mailto:benhayoune_at_maroclear.co.ma> >
To: Multiple recipients of list ORACLE-L < ORACLE-L_at_fatcity.com <mailto:ORACLE-L_at_fatcity.com> >
Date: Monday, May 08, 2000 3:19 PM
Subject: REF : _* initialization parameters (to all who have replied)

Excuse my poor English !

Think you for your replies.

What i need is not a short description of these parameters but a description like what we find in "Oracle Server : Reference" chapter 1 : initialization parameters or something more detailed.

Think you in advance.

------_=_NextPart_001_01BFB9C5.26178FFC
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">

<TITLE>_* initialization parameters</TITLE>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<META content=3D"MSHTML 5.00.2722.2800" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =

class=3D138432314-09052000>The=20
standard query used to see them all (as sys or = internal):</DIV>
<DIV><SPAN=20 class=3D138432314-09052000> </DIV> <DIV><SPAN=20 class=3D138432314-09052000>select=20
x.inst_id,        x.indx+1=20 num,        ksppinm=20 name,        ksppity=20 type,        ksppstvl=20 value,        ksppstdf=20 ISDEFAULT,       =20 decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE')ISSES_MODIFIABLE, &nbs= p;     =20
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE'= ,'FALSE')ISSYS_MODIFIABLE,       =20 decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')ISMODIFIED= ,       =20 decode(bitand(ksppstvf,2),2,'TRUE','FALSE')ISADJUSTED,   &n= bsp;   =20
ksppdesc DESCRIPTION   from x$ksppi x, x$ksppcv y =  where=20
(x.indx =3D y.indx)  order by 3;</DIV> <DIV><SPAN=20 class=3D138432314-09052000> </DIV> <DIV> </DIV>
<DIV>This=20
is something I found on MetaLink a few years ago.  I looked for an = updated=20
version but couldn't find one:</DIV> <DIV><SPAN=20 class=3D138432314-09052000> </DIV> <DIV><SPAN=20 class=3D138432314-09052000>Oracle: Explanation of Oracle undocumented=20 parameters  Last Updated on 01-JUL-97      = The=20
following is a list of undocumented parameters.    1. =

_db_block_cache_protect        On = VMS, the=20
DB_BLOCK_CACHE_PROTECT mechanism has been made much  =    =20
faster.  During normal use, having it turned on shouldn't=20 be        noticeable (the degradation = is less=20
than 1%).  Developers who        = link=20
non-share will need PSWAPM privilege to use this = feature.   =20
     When DB_BLOCK_CACHE_PROTECT is turned on, = developers may=20
either        use the VMS mailboxes = with the M=20
(MEMORY_LOG) command        or they = may just=20
examine the ring buffer in the PGA (index   =20     SMPREI_, array SMPREB_) to determine what buffer = requests=20
have        been made=20 recently.        = DB_BLOCK_CACHE_PROTECT will=20
prevent certain corruption from getting to   =20     disk; although, it may crash the foreground of = the=20
instance.  It will help     =     catch=20
stray writes in the cache. When you try to write past the=20 buffer         size in the sga, = it will=20
fail first with a stack violation.    =     It=20
seems that the db_block_cache_protect has a significant=20 performance        overhead.  = Preliminary=20
testing shows that it has considerable overhead   =20     (a single update took twice as long with the = parameter=20
set to TRUE).        2.=20 _db_block_compute_checksums        = There is=20
another new init.ora parameter, DB_BLOCK_COMPUTE_CHECKSUMS, that =20     controls whether a checksum is put into every = block=20
before the block is      written to disk.  = The=20
default is FALSE.  A block read validates an  =    =20
exiting checksum whether or not this option is enabled.  A block = is=20
marked      as corrupt if a checksum = fails. =20
     It helps determine corruption due to hardware=20 problems.  The incarnation      number and = the=20
sequence number are added to the end of the block to help catch=20 corruption.      If the problem (corruption) is = in the=20
middle of the block      this test will not = detect it.=20
To detect this problem a checksum may be      = generated=20
in the block header before every write and verified on = every read. =20
     3. _db_block_hash_buckets=3D "Number of = database block=20
hash buckets"        The number of = hash=20
buckets is      a) by default to be set to a = prime=20
number;        b) coerced to a prime = if there=20
is an init.ora parameter setting.    =     The=20
value, if not a prime number > 2, is rounded up to the next=20 highest       prime.  =     I=20
would tend not to change it unless there is latch contention on the = hash =20
     chains. raising it to equal the number of = buffers would=20
clearly remove any      contention (basically, = this is=20
just saying that each buffer lives on its own  =     hash=20
chain).  Having it set too small would mean that we might have to=20 scan      over lots of buffers to find the one = we want.=20
I think the default is to       make it = 1/4 of the=20
total number of buffers      4.=20 _db_block_multiple_hashchain_latches    =    =20
"Use one latch per hash chain"      5.=20 _db_handles        "System-wide = simultaneous=20
buffer operations"      6.=20 _db_handles_cached        "Buffer = handles=20
cached each process"      7.=20 _wait_for_sync        " Wait for sync = on=20
commit "      Wait_for_sync is an oracle = generic=20
parameter which, when set to      false, will = allow the=20
system to complete commits without waiting      = for the=20
redo-log buffer flushes to complete.    8.=20 _db_block_max_scan_cnt=3D"Maximum number of buffers to inspect =

when&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; looking for free buffer"&nbsp; =
<BR>&nbsp;&nbsp;&nbsp;=20

DB_BLOCK_MAX_SCAN_CNT is an init.ora parameter which specifies =20     the number of unavailable buffers a process = should scan=20
before signaling      DBWR to write dirty = buffers from=20
the buffer cache to disk.      9.=20 _db_writer_scan_depth        "Number = of LRU=20
buffers for dbwr to scan when looking for dirty buffers" =20     10a.=20
_db_writer_scan_depth_increment       = "Add to=20
dbwr scan depth when dbwr is behind"      = 10b.=20
_db_writer_scan_depth_decrement      Subtract = from dbwr=20
scan depth when dbwr is working too hard      = 11.=20
_db_large_dirty_queue      "Number of buffers = which=20
force dirty queue to be written      12.=20 _db_block_write_batch        Number = of blocks=20
to group in each DB Writer IO      specifies = the no of=20
blocks to be written to the disk in one write  = operation. =20
     Should be increased till write wait time and = write=20
complete waits starts to      = increase. =20
     DBWR Free Low is the number of times DBWR is = invoked=20
because a user      process found at least=20 DB_BLOCK_WRITE_BATCH/2 buffers on the dirty list.  =    =20
This parameter specifies the number of blocks which should be written=20 to       disk at one time. =20     This parameter should only be increased until = the=20
statistics      Write Complete Waits and Write = Wait Time=20
show growth.  Write Complete       = Waits is=20
the number of times a process waited for DBWR   =    =20
to write a current block before making a change to a buffer.  =  =20
 13. _db_block_cache_clone      "Always = clone data=20
blocks on get (for debugging)"      This = parameter=20
setting has a significantly adverse affect on performance =20

<BR>&nbsp;&nbsp;&nbsp; and we were told to run without it.&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; <BR>14. _controlfile_enqueue_timeout&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; /* use parameter value (default is 900) */&nbsp; =

/* System Parameter: control file enqueue = timeout in=20
seconds */      15.=20 _db_no_mount_lock        add hidden = parameter=20
to not acquire mount lock      If hidden = int.ora=20
parameter _db_no_mount_lock is set to TRUE      = then no=20
mount locks are acquired when the the database is mounted =20     exlusive. This allows two databases with the = same name to=20
be      simultaneously mounted. Note that this=20 circumvents the mechanism      to prevent two=20 simultaneous startups of the same database, and is  =    =20
thus a dangerous parameter to set. This only affects ports that =20     ar compiled with the SYS_DFS option enabled = (i.e. VMS=20
only).      It does not get a mount lock on the = database=20
name during startup.      This allows 2 = instances with=20
the same name to run on one machine    16.=20 _log_checkpoint_recovery_check      Check redo = log after=20
checkpoints.      Add debugging code to check = the red=20
log after a checkpoint.  This      code is = intended=20
to help find a problm that is generating ora-600 [3020] =20     during recovery.  This code is enabed with = a new=20
init.ora parameter:     =20 _log_checkpoint_recovery_check=3DXXX, where XXX is the number of = redo =20
     blocks to check.   This is called in = LGWR after=20
every checkpoint. If the      init.ora = parameter=20
"_log_checkpoint_recovery_check" is zero (default)  =    =20
it does nothing. If it is a positive value then that many blocks = of =20
     redo are scanned to see that the data file = blocks on disk=20
could be      recovered if there was an = immediate crash.=20
This code was introduced      to catch an = elusive bug=20
that results in OERI(3020) errors      = occasionally=20
during crash recovery.    17. = _switch_on_stuck_recovery =20
     REDO GENERATION HAS BEEN CHANGED SO THAT WE = SHOULD NEVER=20
ENCOUNTER      A CHANGE IN THE FUTURE OF THE = BLOCK.=20
APPLYING REDO IN SCN ORDER NOW      INSURES = THAT CHANGES=20
ARE APPLIED IN INCARNATION/SEQUENCE # ORDER. THERE  =    =20
IS A LOT OF CODE IN RECOVERY THAT IS NO LONGER NEEDED BECAUSE OF = THIS =20
     CHANGE. THIS INCLUDES THE CODE TO BACKUP AND = RESCAN AFTER=20
REPAIRING      CORRUPTIONS. THE CODE IS BEING = LEFT IN TO=20
AVOID LARGE CHANGES JUST BEFORE      V7.0 = PRODUCTION=20
RELEASE. THE FOLLOWING WILL INSURE THE UNNEEDED CODE =20     IS NEVER EXECUTED UNLESS REDO WAS GENERATED BY A = BETA=20
RELEASE OR THE SECRET      INIT.ORA = PARAMETER=20
_SWITCH_ON_STUCK_RECOVERY IS SET TO TRUE.      = Check=20
redo Log contents after checkpoint. This is called in LGWR after =20     every checkpoint. If this parameter is zero = (default) it=20
does nothing.       If it is a positive = value then=20
that many blocks of redo are scanned to see   =    =20
that the data file blocks on disk could be recovered if there was = an  =20
     immediate crash. This code was introduced to = catch an=20
elusive bug that      results in OERI(3020) = errors=20
occasionally during crash recovery.      = Checkpoint=20
recovery check: this is the number of redo blocks that  =20     kcracl will verify after every LGWR generated = checkpoint.=20
Defaults to zero       for no = checking.  =20
When opening the named offline log for redo application  =20     and to recalculate future change thread = switching this=20
parameter is used.        18.=20 _log_io_size=3Dredo log IO chunk size (blocks/write)   =20

<BR>&nbsp;&nbsp;&nbsp; /* System Parameter: IO chunk size */&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; 1. that the value is o/s dependent.&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; 2. if left at 0, the value will be automatically =

determined for each log        =20 file.      19. = _log_buffers_debug   =20
     /* debugging: fill redo buffers with [well = known] junk=20
after writes */      "debug redo buffers (slows = things=20
down)"    20. =
_log_debug_multi_instance   =20

<BR>&nbsp;&nbsp;&nbsp; /* debugging : pretend multi-instance */&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; "debug redo multi instance code"&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; <BR>21. =

_log_entry_prebuild_threshold   =20     /* redo entry pre-build threshold */ =20     /* this is a bad idea for a uniprocessor , and = is only=20
helpful for a      multiprocessor when there is = latch=20
contention */      LOG_ENTRY_PREBUILD_THRESHOLD =

determines the maximum size of a redo entry  =     to=20
prebuild before the copy to the log buffer.  Increasing this=20 parameter      reduces the time that the redo = copy latch=20
is held.  This parameter should      not = be=20
modified if it is a single processor environment or there will be  =

memory contention.      = 22.=20
_disable_logging        If this is = true, redo=20
records will not be generated -- no recovery is  = possible =20
     if the instance crashes.  It is mainly used = for=20
getting good benchmarking      results. =20     Default is false      = 23.=20
_log_blocks_during_backup        TRUE = value=20
implies before any change is made to a db_block in the = buffer  =20
     cache, a *complete image* of the block is copied = to the=20
redo       redo log.  (This explains = why=20
excessive redo would be generated for   =    =20
datafiles excessive redo would be generated for datafiles in hot = backup =20
     mode.) There is a new init.ora parameter,=20 LOG_BLOCKS_DURING_BACKUP,      that controls = whether=20
block images ar written to the redo log during  =     hot=20
backup.        Default is TRUE for = VM, but=20
port specific with the default defined in     =20 sparams.h.  This may beset to FALSE if the Oracle block size = equals =20
     the O/S physical sector sie or if it is = otherwise ensured=20
that hot backup      reads consistent versios = of blocks=20
even if those blocks are being written      at = the=20
time.  Put anther way, this may be set to FALSE on machines = that =20
     can guarantee the aomicity of a single block I/O =

request.      Default is true =20     Problem is due to split blocks. =20     24. =
_allow_resetlogs_corruption   =20     Added new secret init.ora parameter to override = error=20
1138.        When set to TRUE = the =20
     resetlogs option will be allowed even if there = are hot=20
backups that need      mor redo applied. Unless = you are=20
certain that absolutely all redo, includig  =     the=20
online logs, has been applied, then a full export and import mst = be =20
     done to insure the database is internally=20 consistant.          from = 6.0=20
code      /* if we crashed/shutdown during a = hot backup=20
it is over now and we      ** are no longer = logging=20
blocks. If they did a manual recovery      ** = followed=20
by a NORESETLOGS then the hot backup recovery flag can =20     ** be cleared. The hot backup recovery flag may = alsoneed=20
clearing      ** if RESETLOGS was allowed = because of=20
_allow_resetlogs_corruption      ** parameter. = */ =20
         from the c file kcv.c 7.0 =

code     /* if the resetlogs option is in effect we = save the=20
highest checkpoint       ** scn that we = see, to be=20
used as the incomplete recovery scn. It only    =   =20
** gets used if a resetlogs is done without any recovery. Only=20 backup       ** control file recovery will = be=20
allowed and it always rewrites the       = **=20
incomplete recovery scn. We use the highest scn we can find so=20 that       ** new resetlogs scn will be = greater=20
than anything in the database.       ** = This may=20
not be strictly true if the user sets    =    **=20
_allow_resetlogs_corruption, and there is a fuzzy file. Note = that  =20
    ** since resetlogs does not look at the log file = headers,=20
datafile       ** checkpoints are all we = have to=20
look at.     25. _reuse_index_loop =20     "number of blocks being examine for index block=20 reuse"      /* secret system parameter to = control how=20
agressive we should walk the free      ** = list when=20
attempting to reuse block - default is 5.      = ** Set to=20
0 for fast index operation which is susceptible to growth, =20     ** Set to > 5 for slower index op but more = agressive=20
in reusing blocks  */  =
        =20 Controls the amount of work done when looking for a block to = reusse =20
 for     n index entry.  The value = determines the=20
number of blocks to   =
        =20 check on the freelist when looking for a reusable = block.   =20
   26. _mts_load_constants    =    =20
/* hidden init.ora to set server load balancing constants */ =20     /* fill in load balancing parameters (from=20 _mts_load_constants) */      * PID Controller - =

calculate control on number of servers using:  =     *=20
control =3D Kp * err + Kd * delta(err) + Ki * sum(err) =20     * where Kp =3D proportional, Kd =3D derivative = constant,=20
Ki  =3D integral  constant      * = Kp,Kd,Ki=20
can be changed with the hidden parameter _mts_load_constants =20     * in order to tune the system as desired. =20     This values should only be changed after = gathering enough=20
information to      determine that the mts is = not=20
optimal.    27. _mts_fastpath  =     /*=20
hidden init.ora to enable dispatcher fastpath */  =    =20
default is false       * Return TRUE if = the system=20
should implement the dispatcher network  =      *=20
fastpath.  When enabled, the dispatcher fastpath causes network = i/o =20
      * to be buffered and only flushed when = absolutely=20
necessary.  When not       * enabled, = flushes=20
will occur for every dirty virtual circuit buffer.  =    =20
   *** The following parameters are from the Kernel SQL = Library=20
manager      28.=20 _kgl_multi_instance_lock        Only = for=20
debugging.  all the  _kgl_multi_instance_xxxx =20     "whether KGL to support multi-instance = locks" =20
     Default is 0    29.=20 _kgl_multi_instance_pin      "whether KGL to = support=20
multi-instance pins"      Default is 0. =20     30. _kgl_multi_instance_invalidation =20     "whether KGL to support multi-instance=20 invalidations"      Default is 0. =20     31. =
_row_cache_instance_locks   =20     Kernel SQL Row cache management component, = number of row=20
cache instance       locks =20     default is 100    32.=20 _row_cache_buffer_size        "size = of row=20
cache circular buffer"      default is = 200 =20
     33. _messages     " = message queue=20
resources - dependent on # processes "     The = asynchronous=20
message mechanism allows  processes  to  = send   =20
    messages  to each other.   A = process  may=20
send a  message to a       specified=20 other  process (always without waiting for a = reply),   =20
    may wait for  a message to arrive on its queue, = and may=20
obtain       the next message.   = Messages=20
sent  to  detached processes  are   =20    reliably delivered.  Messages sent to foreground = processes=20
are       reliably  delivered  = as =20
long as  the  process is active.  The   =20    mechanism also permits sending of a simple "reply", = which is=20
a       one-byte message without =20 queuing.  It should normally be used    =   =20
to  reply to  asynchronous  messages,  and  = this =20
is  a  safer       = technique  than=20
regular messages for  responding to foreground   =20    processes.   This mechanism is not used in = single=20
process mode.        34.=20 _cpu_count        ksb - Kernel = Service=20
Background processes      "number of cpu's for = this=20
instance"      CPU_COUNT has to be set on some = platforms=20
in order for Oracle to take       = advantage of=20
multi-processor system, on others it does not have effect = on  =20
     performance since load balancing between = processors is=20
handled by the o/s.      35.=20 _debug_sga        /* Debug SGA,  = don't=20
make the SGA a global section so we can set  watchpoints  =

<BR>&nbsp;&nbsp;&nbsp; <BR>36. _enqueue_locks&nbsp;&nbsp;&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; ksq1 - Kernal Service enQueues (1)&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; Definitions for enqueues client objects, "locks =

for=20
managed enqueues"      37.=20 _enqueue_hash        "enqueue hash = table=20
length"      38.=20 _enqueue_debug_multi_instance        = "debug=20
enqueue multi instance"       KST is a = trace=20
facility used for "realtime" tracing of events.  Below =20      are defined KST macros that will enable = the tracing=20
of such things as         latch=20 operations, memory assignments, etc.  Tracing is done to a=20 per         process circular = buffer=20
stored in the SGA.  Access to these buffers   =20      is via fixed tables.  Tracing is = enabled for=20
classes of events,         = particular=20
events, and ranges of events.       The = tracing=20
state may be dynamically changed with the following = syntax   =20
      "ALTER TRACING"   =20         - "ON"    =

- Processes = begin=20
logging events using the current enabled events   =20       - "OFF"   =20           - Processes = stop=20
tracing    =
         -=20 "ENABLE" <event_string>   =20           - Add in the = events=20
indicated in <event_string> to those which are   =20             = being=20
traced.           = -=20
"DISABLE" <event_string>   =20           - No longer = trace=20
events specified by <event_string>   =20

<BR>&nbsp;&nbsp;&nbsp; =
<BR>39._trace_buffers_per_process&nbsp;&nbsp;&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; Note that by default, tracing is NOT =

enabled.  In=20
order to enable tracing      at instance = startup time,=20
add _trace_buffers_per_process =3D 1      = 40.=20
_trace_block_size        _trace_xxxxx = (xxxxx =3D=20
buffers_per_process, block_size, archive_start, =20            &n= bsp;    =20
flushing, enabled, get_time_every, archive_dest etc.)    =

These parameters are only there for debugging = purposes.=20
Customers      or support will never have to = use=20
them.      41. _trace_archive_start =20     "start trace process on SGA = initialization" =20
     42. _trace_flushing   =20     "TRWR should try to keep tracing buffers = clean" =20
     43. _trace_enabled  =     By=20
default, tracing is NOT enabled.  In order to enable = tracing,  =20

<BR>&nbsp;&nbsp;&nbsp; _trace_enabled =3D true&nbsp; =
<BR>&nbsp;&nbsp;&nbsp;=20
<BR>44. _trace_events&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; If you =

wish to=20
enable tracing of waits at instance startup time, you can  =20     either      add the line =

'_trace_events =3D "WAT,ALL"' to init.ora or execute  =20     'alter tracing enable "WAT,ALL"' in a sqldba=20 session.      If you wish to limit tracing to = specific=20
events, you can use the      the following = syntax: =20

<BR>&nbsp;&nbsp;&nbsp; alter tracing enable =
"WAT,&lt;id&gt;,&lt;id&gt;..."&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; where "id" is either a specific event number, or =

an event=20
range      (event number 1 - event number = 2). =20

<BR>&nbsp;&nbsp;&nbsp; <BR>45. _trace_archive_dest&nbsp;&nbsp;&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; "trace archival destination"&nbsp; =
<BR>&nbsp;&nbsp;&nbsp;=20
<BR>46. _trace_file_size&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; =

"trace file=20
size"      default is 10000 blocks =20     47. =
_trace_write_batch_size   =20     "trace write batch size"  =    =20
default is 32      48.=20 _rollback_segment_initial        = "starting=20
undo segment number"      Default is 1. DO NOT = SPECIFY 0=20
AS THAT HAPPENS TO BE THE SYSTEM ROLLBACK      = 49.=20
_rollback_segment_count        = "number of undo=20
segments"      default is 0  =    =20
 50. _offline_rollback_segments       = If a=20
rollback segment is not accessible because the file it is = in   =20
    is offline or corrupted, one can force the system to = come up=20
without       the rollback segment by = specifying=20
the rollback segment in init.ora       = paramater=20
'_offline_rollback_segments'. The system will come up = by   =20
    estimating the current system commit time since it = cannot=20
access       transaction tble in the = rollback=20
segment. The system commit number       is = a=20
conservative guess based on current time, the database=20 creation       time and the assumed=20 transaction  rate of 8000 tps. ONE MUST MAKE   =20    SURE THAT THE SYSTEM TIME IS SET CORRECTLY WHEN = FORCING THE=20
SYSTEM UP       USING = '_OFFLINE_ROLLBACK_SEGENTS'.=20
A trace is written with information       = about the=20
estimated system commit number.      51.=20 _corrupted_rollback_segments        = Mark a=20
rollback segment as corrupted.    52.=20 _label_tag_cache_size        /* = hidden size of=20
the SGA label tag comparison cache (bitcache) */ =20      "number of tags in the label tag = comparison=20
cache"      default is 200    = 53.=20
_trace_get_time_every        "Number = of trace=20
sequence numbers per call to slgcs()"      = default is=20
200      54. = _vms_bg_priority   =20

<BR>&nbsp;&nbsp;&nbsp; "VMS process priority to give to background=20
processes"&nbsp; <BR>&nbsp;&nbsp;&nbsp; default is 4&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; <BR>55.=20

_sort_use_os_files_as_temporaries    =     =20
Use O/S files rather than temp segments for sorting.  =20     56. =
_log_checkpoints_to_alert   =20     Whether checkpoint messages should be written to =

alert.log or not. Turned       off in=20 benchmarks.         57.=20 _large_memory_system  :        = Used in=20
internal benchmarks. Doesn't concern us.      = "Configure=20
system to use memory and save cpu cycles, paging, etc =20     default is false    58.=20 _wakeup_timeout     This is WMONs sleeptime between = checks of=20
it's queue of processes to wake.     59.=20 _latch_wait_posting     enable posting of processes = that may=20
be waiting for a latch after a process     =   =20
frees the same latch (set this parameter to a value greater than = one =20
    this parameter to a value greater than one for it to = take=20
effect).    60. _sql_connect_capability_code =20    allows database links from a pre-7.0.15 release to = work with=20
release 7.1.     It is necessary to set this = parameter for=20
database links from a V6 ?   </DIV> <DIV> </DIV>
<DIV><SPAN=20 class=3D138432314-09052000> </DIV> Brian P. Mac = Lean=20
 Senior Oracle = Database=20
Administrator <FONT color=3D#808080 face=3D"Comic Sans MS"=20

size=3D1>OCPv8/Oracle Master</FONT> <BR><FONT color=3D#808080 =
face=3D"Comic Sans MS"=20
size=3D1>HomeBid.Com</FONT> <BR><FONT color=3D#808080 face=3D"Comic =

Sans MS"=20
size=3D1>8700 N. Gainey Center Drive Scottsdale, AZ  85258 = <FONT=20
color=3D#808080 face=3D"Comic Sans MS" size=3D1>Tel:480.609.4624 = <FONT=20
color=3D#808080 face=3D"Comic Sans MS" size=3D1>Cel:602.617.6075 = <FONT=20
color=3D#808080 face=3D"Comic Sans MS" size=3D1>Fax:480.609.4646 = <FONT=20
color=3D#808080 face=3D"Comic Sans MS" size=3D1><A=20 href=3D"mailto:Net:brian.maclean_at_homebid.com">Net:brian.maclean_at_homebid.= com</A>
Who needs friends when = you can sit=20
alone in your room and drink?  <BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px"> <DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr>-----Original Message----- From: djordjej=20 [mailto:djordjej_at_netcom.ca] Sent: Monday, May 08, 2000 6:34 =

PM To: Multiple recipients of list = ORACLE-L Subject: Re:=20
 REF : _* initialization parameters (to all who have=20 replied) </DIV>
 <DIV>Some info you can find in Oracle = Press, Oracle=20
 Performance Tuning, Tips&Techniques by Richard = Niemiec.</DIV>

  <DIV><FONT color=3D#000000 size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT color=3D#000000 size=3D2>Djordje&nbsp;</FONT></DIV>
  <BLOCKQUOTE=20

style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; = PADDING-LEFT: 5px">

<DIV>-----Original = Message----- From:=20

Benhayoune khalid <<A=20
=
href=3D"mailto:benhayoune_at_maroclear.co.ma">benhayoune_at_maroclear.co.ma</A= >> To:=20

Multiple recipients of list ORACLE-L <<A=20 Received on Tue May 09 2000 - 09:44:58 CDT