Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle9i database parameters reduce downtime
Nov 25, 2002
Donald Burleson

Sophisticated database management systems such as Oracle need maintenance, and many database maintenance procedures require you to modify the database system's parameters. Because Oracle is so flexible, it requires over 250 documented parameters and nearly 100 undocumented parameters. Some of these parameters require you to stop and restart the database, while others let you make changes without downtime. In this Daily Feature, I'll point out some of the common Oracle parameter types and show you what they do.

Oracle9i parameters

Oracle is extremely complex to maintain. Prior to the debut of the Oracle9i dynamic database, the database administrator (DBA) needed to schedule downtime in order to bounce the database system when making a change to a global parameter. ("Bouncing" means to bring down and restart the database instance.) Of course, downtime compromises the promise of continuous availability.

Listing A shows Oracle9i's parameters.

In order to get around the contrary demands of database maintenance and continuous availability, Oracle9i has the ability to change almost all of the 250 system parameters. This exciting new feature for Oracle promises the following benefits:

  • Continuous availability: The ability to dynamically change its own configurations means that an Oracle database can have true continuous availability. Oracle9i has the ability to reconfigure itself instantly, which means that you can handle even sophisticated changes, such as database reorganizations, without service interruptions.
  • Dynamic tuning: Because you can change the Oracle9i database in real time, you can reconfigure the system based on current processing demands. For example, if the shared pool is running low on RAM memory, you can dynamically steal RAM page frames from the data buffers and reassigned them into the shared pool. This exciting new ability lays the foundation for self-tuning Oracle databases, and future releases of Oracle may be able to dynamically reconfigure themselves based on changes in processing patterns. Prior to Oracle9i, you could see current performance problems but couldn't change the Oracle database without stopping and restarting the system.

The ability to dynamically change the run-time configuration of Oracle9i is a huge benefit that has helped make Oracle the world's most powerful and complex database. However, some Oracle characteristics can't be changed dynamically, such as the internal character set and other parameters that require very short service interruptions, such as archive logging. To fully understand the Oracle9i dynamic database, let's take a closer look at each of these classes of parameters.

The different types of Oracle9i parameters

Within Oracle9i we see three general types of system parameters: immutable parameters, semi-immutable parameters, and dynamic system parameters. These types of parameters are categorized according to the amount of service interruption that is required to change the value of the parameter.

Immutable parameters

Immutable parameters comprise a small subset of Oracle parameters that can't be changed without completely rebuilding the Oracle database. For example, the database block sizes are set at database creation time, and changing the default block size requires exporting the entire database to a flat file and reimporting the data into pages with larger data blocks. In Oracle9i, tables and indexes can be moved dynamically into tablespaces with different block sizes, so this isn't an important issue. The database character sets are another good example of immutable parameters. If the database is created with a congee character set, it can't easily be changed to an ASCII character set.

Semi-immutable parameters

Semi-immutable parameters don't require a complete database rebuild, but changes do require you to stop the database for brief periods of time. For example, changing your database from NOARCHIVELOG log mode to ARCHIVELOG log mode requires Oracle9i to start new background processes to archive the online redo logs into one archived redo log file system. To change your database log mode, you must stop your database, perform a startup mount, change to archive log mode, and open the database.

Dynamic system parameters

This class of Oracle parameters lets you change the size and configuration of the Oracle System Global Area (SGA) and background processes. Oracle9i has over 250 dynamic parameters, and virtually every aspect of Oracle9i can be changed immediately. These parameters fall into two areas, SGA parameters and process parameters.

SGA parameters

When you change these parameters in Oracle9i, the Oracle instance dynamically reconfigures RAM regions, making certain areas of RAM smaller while increasing the size of others. At startup time, Oracle reads the parameters from a file, configures the background processes, and allocates a large region of RAM memory called the SGA. This SGA region contains the database block buffers, RAM memory for the shared pool, and miscellaneous memory regions for specialized demands such as Java. For example, you can dynamically deallocate memory frames from the large pool and reallocate them to the database buffer cache, all with simple commands.

Process parameters

Process parameters affect the behavior of the background processes. For example, you can change the number of parallel query background processes anytime you like, and Oracle will automatically create or destroy these background processes without affecting availability.

Run-time behavior parameters

Run-time behavior parameters deal with changes to the run-time behavior of the Oracle database. For example, changing the hash area size for an Oracle database will dynamically cause hundreds of SQL statements to perform internal hash joins instead of nested loop joins. Another example would be changing the degree of parallelism for a table or index. The Oracle SQL optimizer will detect these changes in object parameters and dynamically generate different execution plans for all incoming SQL statements. Yet another example would be changing the cursor_sharing parameter, whereby SQL statements will be rewritten to replace literal values with host variables. The following examples demonstrate how easy it is to change the behavior of Oracle.

-- Enable the library cache to make SQL reentrant
alter system set cursor_sharing=force;
-- enable the automatic re-write of SQL for materialized views
alter system set query_rewrite_enabled = ‘TRUE';
-- Allocate RAM for hash tables
alter system set hash_area_size = 1048576;

That's it

As you can see, the dynamic reconfiguration abilities of Oracle9i are a boon for IT professionals who must maintain their database while keeping it available for users. Oracle9i has dynamic utilities for almost every database administration and tuning activity. Oracle is keeping its promise of providing true 24/7 availability.


Listing A
SQL> show parameters
NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE                         
active_instance_count                integer                                   
aq_tm_processes                      integer     0                             
archive_lag_target                   integer     0                             
audit_trail                          string      NONE                          
background_core_dump                 string      partial                       
background_dump_dest                 string      C:\oracle\admin\diogenes\bdump
backup_tape_io_slaves                boolean     FALSE                         
bitmap_merge_area_size               integer     1048576                       
blank_trimming                       boolean     FALSE                         
buffer_pool_keep                     string                                    
buffer_pool_recycle                  string                                    
circuits                             integer     170                           
cluster_database                     boolean     FALSE                         
cluster_database_instances           integer     1                             
cluster_interconnects                string                                    
commit_point_strength                integer     1                             
compatible                           string      9.0.0                         
control_file_record_keep_time        integer     7                             
control_files                        string      C:\oracle\oradata\diogenes\CON
                                                 TROL01.CTL, C:\oracle\oradata\
                                                 diogenes\CONTROL02.CTL, C:\ora
core_dump_dest                       string      C:\oracle\admin\diogenes\cdump
cpu_count                            integer     1                             
create_bitmap_area_size              integer     8388608                       
cursor_sharing                       string      EXACT                         
cursor_space_for_time                boolean     FALSE                         
db_16k_cache_size                    big integer 0                             
db_2k_cache_size                     big integer 0                             
db_32k_cache_size                    big integer 0                             
db_4k_cache_size                     big integer 0                             
db_8k_cache_size                    big integer 0                             
db_block_buffers                     integer     0                             
db_block_checking                    boolean     FALSE                         
db_block_checksum                   boolean     TRUE                          
db_block_size                        integer     4096                          
db_cache_advice                      string      OFF                           
db_cache_size                        big integer 33554432                      
db_create_file_dest                  string                                    
db_create_online_log_dest_1          string                                    
db_create_online_log_dest_2          string                                    
db_create_online_log_dest_3          string                                    
db_create_online_log_dest_4          string                                    
db_create_online_log_dest_5          string                                    
db_domain                            string                                    
db_file_multiblock_read_count        integer     8                             
db_file_name_convert                 string                                    
db_files                             integer     200                           
db_keep_cache_size                   big integer 0                             
db_name                              string      diogenes                      
db_recycle_cache_size                big integer 0                             
db_writer_processes                  integer     1                             
dblink_encrypt_login                 boolean     FALSE                         
dbwr_io_slaves                      integer     0                             
disk_asynch_io                       boolean     TRUE                          
dispatchers                          string      (PROTOCOL=TCP)(SER=MODOSE), (P
                                                 .server.GiopServer), (PROTOCOL
distributed_transactions             integer     46                            
dml_locks                            integer     748                           
drs_start                            boolean     FALSE                         
enqueue_resources                    integer     968                           
event                                string                                    
fal_client                           string                                    
fal_server                           string                                    
fast_start_io_target                 integer     0                             
fast_start_mttr_target               integer     300                           
fast_start_parallel_rollback         string      LOW                           
fixed_date                           string                                    
gc_files_to_locks                    string                                    
global_context_pool_size            string                                    
global_names                         boolean     FALSE                         
hash_area_size                       integer     1048576                       
hash_join_enabled                    boolean     TRUE                          
hi_shared_memory_address             integer     0                             
hs_autoregister                      boolean     TRUE                          
ifile                                file                                      
instance_groups                      string                                    
instance_name                        string      diogenes                      
instance_number                      integer     0                             
java_max_sessionspace_size           integer     0                             
java_pool_size                       string      33554432                      
java_soft_sessionspace_limit         integer     0                             
job_queue_processes                  integer     0                             
large_pool_size                      string      1048576                       
license_max_sessions                 integer     0                             
license_max_users                    integer     0                             
license_sessions_warning             integer     0                             
local_listener                       string                                    
lock_name_space                     string                                    
lock_sga                             boolean     FALSE                         
log_archive_dest                     string                                    
log_archive_dest_1                  string                                    
log_archive_dest_10                  string                                    
log_archive_dest_2                   string                                    
log_archive_dest_3                   string                                    
log_archive_dest_4                   string                                    
log_archive_dest_5                   string                                    
log_archive_dest_6                   string                                    
log_archive_dest_7                   string                                    
log_archive_dest_8                   string                                    
log_archive_dest_9                   string                                    
log_archive_dest_state_1             string      enable                        
log_archive_dest_state_10            string      enable                        
log_archive_dest_state_2             string      enable                        
log_archive_dest_state_3             string      enable                        
log_archive_dest_state_4             string      enable                        
log_archive_dest_state_5             string      enable                        
log_archive_dest_state_6             string      enable                        
log_archive_dest_state_7             string      enable                        
log_archive_dest_state_8             string      enable                        
log_archive_dest_state_9            string      enable                        
log_archive_duplex_dest              string                                    
log_archive_format                   string      ARC%S.%T                      
log_archive_max_processes            integer    1                             
log_archive_min_succeed_dest         integer     1                             
log_archive_start                    boolean     FALSE                         
log_archive_trace                    integer     0                             
log_buffer                           integer     524288                        
log_checkpoint_interval              integer     0                             
log_checkpoint_timeout               integer     1800                          
log_checkpoints_to_alert             boolean     FALSE                         
log_file_name_convert                string                                    
logmnr_max_persistent_sessions       integer     1                             
max_commit_propagation_delay         integer     700                           
max_dispatchers                      integer     5                             
max_dump_file_size                   string      UNLIMITED                     
max_enabled_roles                    integer     30                            
max_rollback_segments                integer     37                            
max_shared_servers                   integer     20                            
max_transaction_branches            integer     8                             
mts_circuits                         integer     170                           
mts_dispatchers                      string      (PROTOCOL=TCP)(SER=MODOSE), (P
                                                 .server.GiopServer), (PROTOCOL
mts_listener_address                 string                                    
mts_max_dispatchers                  integer     5                             
mts_max_servers                      integer     20                            
mts_multiple_listeners               boolean     FALSE                         
mts_servers                          integer     1                             
mts_service                          string      diogenes                      
mts_sessions                         integer     165                           
nls_calendar                         string                                    
nls_comp                             string                                    
nls_currency                        string                                    
nls_date_format                      string                                    
nls_date_language                    string                                    
nls_dual_currency                   string                                    
nls_iso_currency                     string                                    
nls_language                         string      AMERICAN                      
nls_length_semantics                 string      BYTE                          
nls_nchar_conv_excp                  string      FALSE                         
nls_numeric_characters               string                                    
nls_sort                             string                                    
nls_territory                        string      AMERICA                       
nls_time_format                      string                                    
nls_time_tz_format                   string                                    
nls_timestamp_format                 string                                    
nls_timestamp_tz_format              string                                    
object_cache_max_size_percent        integer     10                            
object_cache_optimal_size            integer     102400                        
open_cursors                         integer     300                           
open_links                           integer     4                             
open_links_per_instance              integer     4                             
optimizer_features_enable            string      9.0.1                         
optimizer_index_caching              integer     0                             
optimizer_index_cost_adj             integer     100                           
optimizer_max_permutations           integer     2000                          
optimizer_mode                       string      CHOOSE                        
oracle_trace_collection_name         string                                    
oracle_trace_collection_path         string      %ORACLE_HOME%\OTRACE\ADMIN\CDF
oracle_trace_collection_size         integer     5242880                       
oracle_trace_enable                  boolean     FALSE                         
oracle_trace_facility_name           string      oracled                       
oracle_trace_facility_path           string      %ORACLE_HOME%\OTRACE\ADMIN\FDF
os_authent_prefix                    string      OPS$                          
os_roles                             boolean     FALSE                         
parallel_adaptive_multi_user         boolean     FALSE                         
parallel_automatic_tuning            boolean     FALSE                         
parallel_broadcast_enabled           boolean     FALSE                         
parallel_execution_message_size      integer     2148                          
parallel_instance_group              string                                    
parallel_max_servers                 integer     5                             
parallel_min_percent                 integer     0                             
parallel_min_servers                 integer     0                             
parallel_server                      boolean     FALSE                         
parallel_server_instances            integer     1                             
parallel_threads_per_cpu             integer     2                             
partition_view_enabled               boolean     FALSE                         
pga_aggregate_target                big integer 0                             
plsql_compiler_flags                 string      INTERPRETED                   
plsql_native_c_compiler              string                                    
plsql_native_library_dir             string                                    
plsql_native_library_subdir_count    integer     0                             
plsql_native_linker                  string                                    
plsql_native_make_file_name          string                                    
plsql_native_make_utility            string                                    
plsql_v2_compatibility               boolean     FALSE                         
pre_page_sga                         boolean     FALSE                         
processes                            integer     150                           
query_rewrite_enabled                boolean     FALSE                         
query_rewrite_integrity              string      enforced                      
rdbms_server_dn                      string                                    
read_only_open_delayed               boolean     FALSE                         
recovery_parallelism                 integer     0                             
remote_archive_enable                boolean     TRUE                          
remote_dependencies_mode             string      TIMESTAMP                     
remote_listener                      string                                    
remote_login_passwordfile            string      EXCLUSIVE                     
remote_os_authent                    boolean     FALSE                         
remote_os_roles                      boolean     FALSE                         
replication_dependency_tracking      boolean     TRUE                          
resource_limit                       boolean     FALSE                         
resource_manager_plan                string                                    
rollback_segments                    string                                    
row_locking                          string      always                        
serial_reuse                         string      DISABLE                       
serializable                         boolean     FALSE                         
service_names                        string      diogenes                      
session_cached_cursors               integer     0                             
session_max_open_files               integer     10                            
sessions                             integer     170                           
sga_max_size                         big integer 118255568                     
shadow_core_dump                     string      partial                       
shared_memory_address                integer     0                             
shared_pool_reserved_size            big integer 1677721                       
shared_pool_size                     big integer 46137344                      
shared_server_sessions               integer     165                           
shared_servers                       integer     1                             
sort_area_retained_size              integer     0                             
sort_area_size                      integer     524288                        
spfile                               string                                    
sql92_security                       boolean     FALSE                         
sql_trace                            boolean     FALSE                         
sql_version                          string      NATIVE                        
standby_archive_dest                 string      %ORACLE_HOME%\RDBMS           
standby_file_management              string      MANUAL                        
standby_preserves_names              boolean     FALSE                         
star_transformation_enabled          string      FALSE                         
tape_asynch_io                       boolean     TRUE                          
thread                               integer     0                             
timed_os_statistics                  integer     0                             
timed_statistics                     boolean     TRUE                          
trace_enabled                        boolean     TRUE                          
tracefile_identifier                 string                                    
transaction_auditing                 boolean     TRUE                          
transactions                        integer     187                           
transactions_per_rollback_segment    integer     5                             
undo_management                      string      AUTO                          
undo_retention                      integer     900                           
undo_suppress_errors                 boolean     FALSE                         
undo_tablespace                      string      UNDOTBS                       
use_indirect_data_buffers            boolean    FALSE                         
user_dump_dest                       string      C:\oracle\admin\diogenes\udump
utl_file_dir                         string                                    
workarea_size_policy                 string      MANUAL       





Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational