Oracle tuning buleson performance

Home
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
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

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

                                                 cle\oradata\diogenes\CONTROL03

                                                 .CTL

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

                                                ROTOCOL=TCP)(PRE=oracle.aurora

                                                 .server.GiopServer), (PROTOCOL

                                                 =TCP)(PRE=oracle.aurora.server

                                                 .SGiopServer)

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

                                                ROTOCOL=TCP)(PRE=oracle.aurora

                                                 .server.GiopServer), (PROTOCOL

                                                 =TCP)(PRE=oracle.aurora.server

                                                 .SGiopServer)

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