Oracle Instance Startup Parameters

Parameters are used to adjust memory strucutures and optimise performance, set global defaults, limitations and physical naming.

always_anti_join Always use this anti-join when possible
always_semi_join Always use this semi-join when possible
aq_tm_processes Number of AQ Time Managers to start
audit_trail Enable system auditing
background_dump_dest Detached process dump directory
backup_tape_io_slaves BACKUP Tape I/O slaves
bitmap_merge_area_size Maximum memory allow for BITMAP MERGE
blank_trimming Blank trimming semantics parameter
buffer_pool_keep Number of database blocks/latches in keep buffer pool
buffer_pool_recycle Number of database blocks/latches in recycle buffer pool
commit_point_strength Bias this node has toward not preparing in a two-phase commit
compatible Database will be completely compatible with this software version
control_file_record_keep_time Control file record keep time in days
control_files Control file names list
cpu_count Number of cpu's for this instance
create_bitmap_area_size Size of create bitmap buffer for bitmap index
cursor_space_for_time Use more memory in order to get faster execution
db_block_buffers Number of database blocks cached in memory
db_block_checking Data and index block checking
db_block_checksum Store checksum in db blocks and check during reads, forces Oracle to use checksums to verify data block contents. Setting DB_BLOCK_CHECKSUM = TRUE does cause a performance overhead.
db_block_lru_latches Number of lru latches
db_block_max_dirty_target Upper bound on modified buffers/recovery reads
db_block_size Size of database block in bytes
db_domain Directory part of global database name stored with CREATE DATABASE command
db_file_direct_io_count Sequential I/O block count
db_file_multiblock_read_count Database block to be read each IO
db_file_name_convert Datafile name convert pattern and string for standby/clone database
db_files Maximum allowable number of db files
db_name Database name specified in CREATE DATABASE
db_writer_processes Number of background database writer processes to start
dblink_encrypt_login Enforce password for distributed login always be encrypted
dbwr_io_slaves DBWR I/O slaves
disk_asynch_io Use asynch I/O for random access devices
distributed_transactions Maximum number of concurrent distributed transactions
dml_locks One for each table modified in a transaction
enqueue_resources Resources for enqueues
ent_domain_name Enterprise Domain Name
event Debug event control - default null string
fast_start_io_target Upper bound on recovery reads
fast_start_parallel_rollback Maximum number of parallel recovery slaves that may be used
fixed_date Fixed SYSDATE value
gc_defer_time How long to defer down converts for hot buffers (DFS)
gc_files_to_locks Mapping between file numbers and lock buckets (DFS)
gc_releasable_locks Number of releasable locks (DFS)
gc_rollback_locks Locks for the rollback segments (DFS)
global_names Enforce that database links have same name as remote database
hash_area_size Size of in-memory hash work area
hash_join_enabled Enable/disable hash join
hash_multiblock_io_count Number of blocks hash join will read/write at once
hi_shared_memory_address SGA starting address (high order 32-bits on 64-bit platforms)
hs_autoregister Enable automatic server DD updates in HS agent self-registration
ifile Include file in init.ora
instance_groups List of instance group names
instance_name Instance name supported by the instance
instance_number Instance number
java_max_sessionspace_size Maximum allowed size in bytes of a Java sessionspace
java_pool_size Size in bytes of the Java pool
java_soft_sessionspace_limit Warning limit on size in bytes of a Java sessionspace
job_queue_interval Wakeup interval in seconds for job queue processes
job_queue_processes Number of job queue processes to start
large_pool_size Size in bytes of the large allocation pool
license_max_sessions Maximum number of non-system user sessions allowed
license_max_users Maximum number of named users that can be created in the database
license_sessions_warning Warning level for number of non-system user sessions
lm_locks Number of locks configured for the lock manager
lm_procs Number of client processes configured for the lock manager
lm_ress Number of resources configured for the lock manager
local_listener Local listener
lock_name_space Lock name space used for generating lock names for standby/clone
lock_sga Lock entire SGA in physical memory
log_archive_dest Archival destination text string
log_archive_dest_1 Archival destination #1 text string
log_archive_dest_2 Archival destination #2 text string
log_archive_dest_3 Archival destination #3 text string
log_archive_dest_4 Archival destination #4 text string
log_archive_dest_5 Archival destination #5 text string
log_archive_dest_state_1 Archival destination #1 state text string
log_archive_dest_state_2 Archival destination #2 state text string
log_archive_dest_state_3 Archival destination #3 state text string
log_archive_dest_state_4 Archival destination #4 state text string
log_archive_dest_state_5 Archival destination #5 state text string
log_archive_duplex_dest Duplex archival destination text string
log_archive_format Archival destination format
log_archive_max_processes Maximum number of active ARCH processes
log_archive_min_succeed_dest Minimum number of archive destinations that must succeed
log_archive_start Start archival process on SGA initialization
log_buffer Redo circular buffer size
log_checkpoint_interval Number of redo blocks checkpoint threshold
log_checkpoint_timeout Maximum time interval between checkpoints in seconds
log_checkpoints_to_alert Log checkpoint begin/end to alert file
log_file_name_convert Logfile name convert pattern and string for standby/clone database
max_commit_propagation_delay Maximum age of new snapshot in .01 seconds
max_dump_file_size Maximum size (blocks) of dump file
max_enabled_roles Maximum number of roles a user can have enabled
max_rollback_segments Maximum number of rollback segments in SGA cache
mts_dispatchers Specifications of dispatchers
mts_listener_address Address(es) of network listener
mts_max_dispatchers Maximum number of dispatchers
mts_max_servers Maximum number of servers
mts_multiple_listeners Are multiple listeners enabled?
mts_servers Number of servers to start up
mts_service Service supported by dispatchers
nls_calendar NLS calendar system name
nls_comp NLS comparison
nls_currency NLS local currency symbol
nls_date_format NLS Oracle date format
nls_date_language NLS date language name
nls_dual_currency Dual currency symbol
nls_iso_currency NLS ISO currency territory name
nls_language NLS language name
nls_numeric_characters NLS numeric characters
nls_sort NLS linguistic definition name
nls_territory NLS territory name
nls_time_format Ttime format
nls_time_tz_format Time with timezone format
nls_timestamp_format Time stamp format
nls_timestamp_tz_format Timestampe with timezone format
O7_DICTIONARY_ACCESSIBILITY Version 7 Dictionary Accessibility Support
object_cache_max_size_percent Percentage of maximum size over optimal of the user session's object
object_cache_optimal_size Optimal size of the user session's object cache in bytes
open_cursors Maximum number of cursors per process
open_links Maximum number of open links per session
open_links_per_instance Maximum number of open links per instance
optimizer_features_enable Optimizer plan compatibility parameter
optimizer_index_caching Optimizer percent index caching
optimizer_index_cost_adj Optimizer index cost adjustment
optimizer_max_permutations Optimizer maximum join permutations per query block
optimizer_mode Optimizer mode
optimizer_percent_parallel Optimizer percent parallel
optimizer_search_limit Optimizer search limit
oracle_trace_collection_name Oracle TRACE default collection name
oracle_trace_collection_path Oracle TRACE collection path
oracle_trace_collection_size Oracle TRACE collection file max. size
oracle_trace_enable Oracle TRACE instance wide enable/disable
oracle_trace_facility_name Oracle TRACE default facility name
oracle_trace_facility_path Oracle TRACE facility path
os_authent_prefix Prefix for auto-logon accounts
os_roles Retrieve roles from the operating system
parallel_adaptive_multi_user Enable adaptive setting of degree for multiple user streams
parallel_automatic_tuning Enable intelligent defaults for parallel execution parameters
parallel_broadcast_enabled Enable broadcasting of small inputs to hash and sort merge joins
parallel_execution_message_size Message buffer size for parallel execution
parallel_instance_group Instance group to use for all parallel operations
parallel_max_servers Maximum parallel query servers per instance
parallel_min_percent Minimum percent of threads required for parallel query
parallel_min_servers Minimum parallel query servers per instance
parallel_server If TRUE startup in parallel server mode
parallel_server_instances Number of instances to use for sizing OPS SGA structures
parallel_threads_per_cpu Number of parallel execution threads per CPU
partition_view_enabled Enable/disable partitioned views
plsql_load_without_compile PL/SQL load without compilation flag
plsql_v2_compatibility PL/SQL version 2.x compatibility flag
pre_page_sga Pre-page sga for process
processes User processes
query_rewrite_enabled Allow rewrite of queries using materialized views if enabled
query_rewrite_integrity Perform rewrite using materialized views with desired integrity
rdbms_server_dn RDBMS's Distinguished Name
read_only_open_delayed If TRUE delay opening of read only files until first access
recovery_parallelism Number of server processes to use for parallel recovery
remote_dependencies_mode Remote-procedure-call dependencies mode parameter
remote_login_passwordfile Password file usage parameter
remote_os_authent Allow non-secure remote clients to use auto-logon accounts
remote_os_roles Allow non-secure remote clients to use os roles
replication_dependency_tracking Tracking dependency for Replication parallel propagation
resource_limit Master switch for resource limit
resource_manager_plan Resource mgr top plan
rollback_segments Declared ONLINE rollback segments list
row_locking Row-locking
serial_reuse Reuse the frame segments
serializable Serializable
service_names Service names supported by the instance
session_cached_cursors Number of cursors to save in the session cursor cache
session_max_open_files Maximum number of open files allowed per session
sessions User and system sessions
shared_memory_address SGA starting address (low order 32-bits on 64-bit platforms)
shared_pool_reserved_size Size in bytes of reserved area of shared pool
shared_pool_size Size in bytes of shared pool
sort_area_retained_size Size of in-memory sort work area retained between fetch calls
sort_area_size Size of in-memory sort work area
sort_multiblock_read_count Multi-block read count for sort
sql_trace Enable SQL trace
sql92_security Require select privilege for searched update/delete
standby_archive_dest Standby database archivelog destination text string
star_transformation_enabled Enable the use of star transformation
tape_asynch_io Use asynch I/O requests for tape devices
text_enable Enable text searching
thread Redo thread to mount
timed_os_statistics Internal os statistic gathering interval in seconds
timed_statistics Maintain internal timing statistics
transaction_auditing Transaction auditing records generated in the redo log
transactions Maximum number of concurrent active transactions
transactions_per_rollback_segment Number of active transactions per rollback segment
use_indirect_data_buffers Enable indirect data buffers (very large SGA on 32-bit platforms
user_dump_dest User process dump directory
utl_file_dir Accessible directories list

Recommended to Alter the Following Parameters after Database Creation

The V$PARAMETER Performance View

All the above values can be obtained by selecting from the V$PARAMETER dynamic performance view in Oracle SQL. The V$PARAMETER view has columns as shown below. All the entries in the V$PARAMETERS view can appear in the init.ora file. Note that some parameters are changeable at run-time (whilst the database is open) using the ALTER SYSTEM command.

The Parameter File

In Oracle7 on the parameter file is stored by default in the Oracle Home\Database directory and in Oracle8i in the Oracle Home\Admin\Pfile directory. These files can be moved. The IFILE parameter can be used to point to a parameter file stored in another location. Some characters are reserved in parameter values and must be escaped in order to be included. These characters are #, (, ), ", ', =, ', - and \.