René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Initialization Parameters

The initialization parameters can be set in the init.ora file.
There are two common ways to find out to what value an initialization parameter is set:
The parameters can be changed for the currently connected session with a alter session set ... command.
If a parameter should be set in another session, dbms_system.set_bool_param_in_session or dbms_system.set_int_param_in_session can be used.

Audit related parameters

NLS related parameters

Optimizer related parameters

PL/SQL related parameters

See here.

Parameters affecting SGA

The following initialization parameters affect the size of the SGA:

Other parameters

ALLOW_FREELIST_GROUPS

This parameter was obsoleted after Oracle V6 and default to true since. At that time, it needed to be set in order to specify the freelist groups parameter in the storage clause.

ALWAYS_ANTI_JOIN

This parameter became obsolete in 9i.

BACKGROUND_DUMP_DEST

background_dump_dest specifies the directory (folder) where trace files of background processes are being written. It also specifies the location for the alert.log file.
It is also used for ORA-00600 errors.
See also max_dump_file_size.

BITMAP_MERGE_AREA_SIZE

BUFFER_POOL_KEEP

Deprecated in favour of db_keep_cache_size

BUFFER_POOL_RECYCLE

Deprecated in favour of db_recycle_cache_size

COMMIT_WRITE

Comes new with Oracle 10g R2, see also commit (sql) and On setting commit_write.

COMMIT_WORK

Comes new with Oracle 10g R2, see also here.

COMPATIBLE

The value of this parameter specifies the version that the database must adhere to.
With Oracle 10g, the value of this parameter must be set at least to 9.2; and once it was set to 10 it cannot be lowered afterwards.
The value of this parameter can be determined with dbms_utility.db_version.

CONTROL_FILES

Every database must have at least one control file that describes important characteristics of the database. This parameter specifies their location.
See Creating a Database

CONTROL_FILE_RECORD_KEEP_TIME

This parameter controls the minimum number of days that a reusable record is kept in the control file.
Its range is 0 .. 365 (=1 year)
control_file_record_keep_time also governs the size of controlfiles.

CORE_DUMP_DEST

See also max_dump_file_size.

CURSOR_SHARING

This parameter influences hard parses and soft parses and is, according to metalink note 223299.1, one of the top parameters affecting performance.
The parameter can be set to either exact, similar or force.

DB_BLOCK_CHECKSUM

Specifies if integrity checking is enabled as block level.
See also log_block_checksum

DB_BLOCK_LRU_LATCHES

This parameter became obsolete in 9i.

DB_BLOCK_MAX_DIRTY_TARGET

This parameter became obsolete in 9i.

DB_CACHE_ADVICE

According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_CREATE_FILE_DEST

DB_CREATE_FILE_DEST sets the default location for Oracle-managed datafiles. This location is also used as the default for Oracle-managed control files and online redo logs if DB_CREATE_ONLINE_LOG_DEST_n is not specified.
You can specify a file system directory as the default location for the creation of datafiles, control files, and online redo logs. However, the directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files, and a file thus created is an Oracle-managed file.
This parameter can be useful while creating a database.

DB_CREATE_ONLINE_LOG_DEST_n

DB_CREATE_ONLINE_LOG_DEST_ n(where n= 1, 2, 3, ... 5) sets the default location for Oracle-managed control files and online redo logs. You should specify at least two parameters: DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2. This provides greater fault tolerance for the logs if one of the destinations should fail.
If more than one directory is specified, then the control file or online redo log is multiplexed across the directories. One member of each online redo log is created in each directory, and one control file is created in each directory.
The directory must already exist; Oracle does not create it. The directory must have appropriate permissions that allow Oracle to create files in it. Oracle generates unique names for the files, and a file thus created is an Oracle-managed file.
This parameter can be useful while creating a database

DB_DOMAIN

DB_FILE_DIRECT_IO_COUNT

This parameter became obsolete in 9i.

DB_FILE_MULTIBLOCK_READ_COUNT

This parameter specifies how many blocks will be read at once when Oracle performs a full table scan or an index range scan. It doesn't affect reads on blocks that are indexed (in which case only one block is read).
The value for this parameter should be chosen carefully. The OS on which Oracle is running should be capable of reading db_file_multiblock_read_count*db_block_size in one I/O request. If it is set too high, the optimizer will think that full table scan are cheap and will prefer them to the usage of indexes. On the other hand, setting it to low makes the optimizer choose indexes more often than necessary. By the way, the preference of indexes or full table scans is also influenced by optimizer_index_cost_adj.

DB_FILE_NAME_CONVERT

This parameter is needed if a standby database does not have the same layout on the disk for its files as the primary database.
See also log_file_name_convert

DB_FILES

The maximum number of database files that can be opened for a database.

DB_FLASHBACK_RETENTION_TARGET

This is one of the relevant parameters for Flashback DB.

DB_NAME

This parameter must have the same value as the database name.

DB_RECOVERY_FILE_DEST

This is one of the relevant parameters for Flashback DB.

DB_RECOVERY_FILE_DEST_SIZE

This is one of the relevant parameters for Flashback DB.

DB_WRITER_IO_SLAVES

db_writer_io_slaves simulates asynchronous IO, but they do not perform asynchronous IO, and thus, they're only meaningful if the OS does not support asynchronous IO.
If the OS supports asynchronous, multible dbwr processes should be used and disk_asynch_io be set to true.

DB_16K_CACHE_SIZE

According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_2K_CACHE_SIZE

According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_32K_CACHE_SIZE

According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_4K_CACHE_SIZE

According to metalink note 223299.1, this is one of the top parameters affecting performance.

DB_8K_CACHE_SIZE

According to metalink note 223299.1, this is one of the top parameters affecting performance.

DISK_ASYNCH_IO

See also db_writer_io_slaves.

EVENT

, anchor=>'event') 
event=event_name action
This parameter allows to set a diagnostic event.
Multiple events must be seperated by colons: 
event="<event 1>:<event 2>: <event 3>: <event n>"

GC_DEFER_TIME

This parameter became obsolete in 9i.

GC_RELEASABLE_LOCKS

This parameter became obsolete in 9i.

GC_ROLLBACK_LOCKS

This parameter became obsolete in 9i.

GLOBAL_NAMES

HASH_AREA_SIZE

The default is 64K, which is far too small for most cases. A range of 512KB to 1MB should be considered.
The memory for a hash join (up to the value specified with hash_area_size) is allocated from the cursor work heap (in the uga.)
See also sort_area_size.

HASH_MULTIBLOCK_IO_COUNT

This parameter became obsolete in 9i.

INSTANCE_NAME

INSTANCE_NUMBER

INSTANCE_NODESET

This parameter became obsolete in 9i.

JOB_QUEUE_INTERVAL

This parameter became obsolete in 9i.

JOB_QUEUE_PROCESSES

Controls how many jobs can run; see also dbms_job.

LM_LOCK

This parameter became obsolete in 9i.

LM_RESS

This parameter became obsolete in 9i.

LOCK_NAME_SPACE

LOCK_SGA

On platform that support it, this parameter can be set to true which will lock the entire SGA into physical memory.

LOG_ARCHIVE_DEST

Deprectated in Enterprise Edition in favour of log_archive_dest_n.

LOG_ARCHIVE_DEST_n

LOG_ARCHIVE_DEST_n (as well as log_archive_dest) can only be used if the database is running in archive log mode.
A common misstake when moving from the (deprecated) log_archive_dest to log_archive_dest_n is to forget one of the attributes such as SERVICE= or LOCATION= which causes a ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE when it altered with the alter system command.
Attributes:
  • SERVICE
    A standby destination
    See archiving to standby.
    Use the lgwr option to specify LGWR transmission or the arch option to specify ARCH transmission.
  • LOCATION
    A local file system path, must be defined at least once.
  • DELAY=minutes
    delays applying of the redo log at the standby site.
v$archive_dest_status allows to query the status (and possibly the errors) for each of the defined archive destinations.

LOG_ARCHIVE_DEST_STATE_n

log_archive_dest_state_N specifies the state for log_archive_dest_N.

LOG_ARCHIVE_FORMAT

The following expandables can be used:
  • %s: log sequence number
  • %S: log sequence number, zero filled
  • %t: thread number
  • %T: thread number, zero filled
  • %d: DBID

LOG_ARCHIVE_START

This parameter is deprecated in Oracle 10g
This parameter determines if the background process ARCH is started. It can be set to either true or false.
Of course, it makes no sense, if this parameter is set to true if the database is running in noarchive log mode. If ARCH is started with the database being in noarchive log mode, messages like media recovery disabled will be written into the alert.log file.

LOG_BLOCK_CHECKSUM

See also DB_BLOCK_CHECKSUM

LOG_CHECKPOINT_INTERVAL

See Events that trigger a checkpoint
The unit of this parameter is measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.

LOG_CHECKPOINT_TIMEOUT

See Events that trigger a checkpoint

LOG_FILE_NAME_CONVERT

This parameter is needed if a standby database does not have the same layout on the disk for its files as the primary database.
See also db

FIXED_DATE

Fixed date can be set to a date in the following format:
YYYY-MM-DD HH24-MI-SS
If set, sysdate returns this date instead of the current date. 
alter session set nls_date_format = 'dd.mon.yyyy hh24:mi:ss';
alter system set fixed_date='2004-03-02 22:23:24';
select sysdate from dual;

SYSDATE
--------------------
02.mar.2004 22:23:24

MAX_DUMP_FILE_SIZE

This parameter specifies the maximum size for dump files such as trace files.
The unit of this parameter is measured in physical operating system blocks unless it has a suffix M or K, in which case the unit is Megabyte and Kilobyte, respectively. Note, the size of physical operating system blocks is not equal to the size of DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.

MAX_IDLE_TIME

O7_DICTIONARY_ACCESSIBILITY

Default was true until 8i, and is false since 9i.
false: only privileged users can access the data dictionary.
true: any user who has been granted select any table can select from tables owned by sys. Alternatively, select_catalog_role can be granted.
The parameter should (probably) be set to false. Users that need access to sys owned table should then be granted the select any dictionary privilege.
The setting of this parameter influences grant ... ANY .. to ... statements.

OS_AUTHENT_PREFIX

See OS authentication

OPEN_CURSORS

This parameter defines how many cursors a session (not the cumulative sum of all sessions) can open at most.

PGA_AGGREGATE_TARGET

According to metalink note 223299.1, this is one of the top parameters affecting performance.

PROCESSES

The value of processes affects the value that the kernel parameter SEMMSL (Maximum number of semaphores in a semaphore set): it should be equal to the value of processes + 10.
If there are more than on instance on a box, the value of the instance with the greatest processes must be taken.
It affects also the optimal setting for SEMMNS (Number of semaphores in the system): 2*highets process value + 1*other process values + 10 * count of instances.

QUERY_REWRITE_ENABLED

See Create Materialized View
This parameter must be set to true to make use of function based indexes. Additionally query_rewrite_integrity must be set to trusted.

QUERY_REWRITE_INTEGRITY

This parameter can be set to either
  • enforced
  • trusted
  • stale_tolerated
This parameter must be set to trusted to make use of function based indexes. Additionally query_rewrite_enabled must be set to true.
See also create materialized view

REMOTE_ARCHIVE_ENABLE

REMOTE_LISTENER

REMOTE_LOGIN_PASSWORDFILE

remote_login_passwordfile specifies if Oracle checks for a password file and if this password file is shared among databases.
The following values are possible:
  • none
    Oracle ignores the password file if it exists.
  • exclusive
    Password file is exclusively used by one database. Any user can be added to the password file.
  • internal
    Used for Oracle Parallel Server
  • shared
    The password file is shared among databases. However, the only users that can be authenticated are sys (and obsoletly: internal).
    If the password file is shared, only SYS can be added to the password file.

RESOURCE_LIMIT

This parameter must be set to true to enforce resource limits assigned to a user through profiles
See also On profile.

RESOURCE_MANAGER_PLAN

Setting this parameter activates the resource manager.
If the paramter is set with a prepending FORCE:, the plan can only be changed by the database administrator.

ROLLBACK_SEGMENTS

Defines the rollback segments that the instance will aquire at startup
On startup, Oracle devides transactions by transactions_per_rollback_segment. If the result is greater than the number of rollback segments actually brought online by the rollback_segments init param, additional rollback segments will be brought online.

SESSIONS

SESSION_CACHED_CURSORS

See Cached cursors.

SHARED_SERVERS

SORT_AREA_SIZE

The default is 64K, which is far too small for most cases. A range of 512KB to 1MB should be considered.
The memory for a sort (up to the value specified with sort_area_size) is allocated from the cursor work heap (in the uga).
See also hash_area_size.

SORT_AREA_RETAINED_SIZE

SORT_MULTIBLOCK_READ_COUNT

This parameter became obsolete in 9i.

SPFILE

Specifies the spfile to be used.

STANDBY_ARCHIVE_DEST

This parameter specifies the location of archived redo logs that come from a primary database.
The value of this parameter is displayed in the v$archive_dest view.

STAR_TRANSFORMATION_ENABLED

STANDBY_FILE_MANAGEMENT

If not set to auto, newly created tablespaces in a standby environment must be recreated manually on the standby servers as well. Similarly, newly added datafiles must be copied to the standby servers as well.

STATISTICS_LEVEL

According to metalink note 223299.1, this is one of the top parameters affecting performance.
It can be set to one of
  • ALL
  • TYPICAL
  • BASIC

SQL_TRACE

Setting sql_trace=true is a prerequisite for using tkprof. It can also be set for a single session with alter session set sql_trace.
After setting sql_trace to true, a trace file will be written.
There is also dbms_support that should allow to trace sessions with more information.
See also diagnostic event 10046.
sql_trace seems to be deprecated since 10.2, but not removed. It still behaves as in earlier versions of Oracle.

TEXT_ENABLE

This parameter became obsolete in 9i.

TIMED_STATISTICS

This parameter must be true in order to gather timing information in v$system_event
It is also useful when using tk prof.

TRANSACTIONS

TRANSACTIONS_PER_ROLLBACK_SEGMENT

UNDO_MANAGEMENT

Set to AUTO to use Oracle 9i's new automatic undo management.
See Undo Tablespaces.

UNDO_RETENTION

Specifies for how many seconds undo information is kept.
See Undo Tablespaces and dbms_flashback.

UNDO_SUPPRESS_ERRORS

This parameter is important if
  • An Oracle database is upgraded to version 9i,
  • the upgraded database uses Undo Tablespaces,
  • There are still applications that use SET TRANSACTION USE ROLLBACK SEGMENT
If in such a case the parameter is set to TRUE (default is FALSE), there won't be any errors; although it gets written into the alert log.
See undo tablespaces.

UNDO_TABLESPACE

Specifies the undo tablespaces when using automatic undo management.

USER_DUMP_DEST

The value of user_dump_dest specifies the destination (path to a operating system directory) where user processes will write trace files.
It is also used for ORA-00600 errors.
See also max_dump_file_size.

USE_POST_WAIT_DRIVER

Setting this value to true makes Oracle use post-wait drivers instead of semaphores.

UTL_FILE_DIR

This parameter specifies one more more locations to where files can be written and from where files can be read using utl_file.
Specifying multiple directories in the spfile: 
alter system set utl_file_dir='/foo/bar/dir1','/foo/baz/dir2','/tmp' scope=spfile
Thanks to Timothy Trauernicht who notified me of an error here.

WORKAREA_SIZE_POLICY

According to metalink note 223299.1, this is one of the top parameters affecting performance.

Hidden parameters

Parameters whose name starts with an underscore are hidden. Usually, they should not be touched! Oracle won't probably support the database if one of these parameters were changed.

_ALLOW_RESETLOGS_CORRUPTION

Allows resetlogs even if it will cause corruption.

_COLUMN_TRACKING_LEVEL

If set to 1 (the default), will cause SMON to update sys.col_usage$ with information regarding access patterns on table columns.

_DB_AGING_COOL_COUNT

Touch count set when buffer cooled.

_DB_AGING_FREEZE_CR

Make CR buffers always be 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

This parameter specifies a time period in which the touch count of a buffer within the buffer cache can at most be increased once.

_DB_PERCENT_HOT_DEFAULT

Percent of default buffer pool considered hot.

_DB_PERCENT_HOT_KEEP

Percent keep buffer pool considered hot.

_DB_PERCENT_HOT_RECYCLE

Percent recycle buffer pool considered hot.

_INIT_SQL_FILE

This parameter points to the file that is executed upon creation of the database (create database). As of 9i, the value is ?/rdbms/admin/sql.bsq.

_KGHDSIDX_COUNT

Controls the number of shared area subpools.

_LOG_IO_SIZE

The unit of this parameter is measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle.

_REALFREE_HEAP_PAGESIZE_HINT

_RECYCLEBIN

This hidden parameter is available in 10g. If set to false, then tables are purged immediately at a drop table.

_SMALL_TABLE_THRESHOLD

_SYSTEM_TRIG_ENABLED

Defaults to true and Oracle recommends setting it to false only during database upgrade.
If this parameter is set to false, then system triggers won't be executed.

_TRACE_FILES_PUBLIC

Trace files (such as those created by a block dump are only readable by oracle, unless this parameter is set to true.
Setting this parameter to true causes a security risk as sensitive data might be written into the trace files!

_USE_ISM

If a system features ISM (intimate shared memory), Oracle uses it by default. This can be disabled by setting _use_ism to false.
As far as I can see, solaris is the only OS that has ISM.

_USE_ISM_FOR_PGA

_WAIT_FOR_SYNC

If set to false, a transaction that commits does not wait until the redo is flushed. However, a database can then not be restored if it crashes.

Thanks

Thanks to Guy Lambregts who spotted an error on this page and helped correct it.