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.