Deep analysis of dynamic reconfiguration in Oracle 9i database

The most important new feature of oracle9i is the ability to dynamically modify almost all Oracle performance parameters. This allows Oracle experts to dynamically reconfigure the Oracle instance during the run-whether it is addressing a current performance problem or anticipating an urgent performance requirement. Because you can dynamically modify everything in the system global Zone (the SGA is the RAM used by an instance of Oracle), it is important to know how to monitor the Oracle database (AREA,SGA). After the system access trend and access mode are summed up, the database can be reconfigured in advance because of the forecast to the regular resource requirements.

When it comes to dynamic database tuning operations, Oracle experts usually care about two things: a prearranged reconfiguration to support changes in the general processing requirements, and a dynamic reconfiguration based on trends to respond to information obtained from Statspack. The following is a look at how Oracle can provide support for both of these activities.

Scheduled reconfiguration

Assume that an Oracle database runs in online transaction processing (OLTP) mode during the day and in decision support mode at night. In order to obtain the best performance, these two kinds of services put forward completely different requirements. For this type of database, Oracle DBA can schedule a task in advance to reconfigure the Oracle instance to the most appropriate configuration for the current processing type.

You can usually choose one of two tools to schedule dynamic reconfiguration. The most common way is to use a UNIX cron job, which starts a shell script to schedule a regular reconfiguration. You can also use the Oracle Dbms_job utility. Both of these tools allow you to schedule configuration changes.

Listing A is as follows:

Listing a:script 
to Dss-mode #!/bin/ksh # i 
 , we must set the environment .... 
oracle_sid=$1 export oracle_sid 
oracle_home= ' cat/etc/oratab|grep ^ $ORACLE _sid:|cut-f2-d ': '
#ORACLE_HOME = ' Cat/var/opt/oracle/oratab|grep 
^ $ORACLE _sid:|cut-f2-d ': ' Export oracle_home 
path= $ORACLE _home/bin: $PATH Export 
PATH $ORACLE _home/bin/sqlplus–s/nologin<
alter system set db_cache_size=1500m;
alter system set shared_pool_size=500m;
alter system set pga_aggregate_target=4000m;
Exit!　　

Listing a provides a UNIX script that can be used to reconfigure Oracle for decision support processing. Note that the script modifies parameters such as Shared_pool, Db_cache_size, and Pga_aggregate_target to meet the needs of the data warehouse activity. The next morning you can run a similar script that changes the database configuration back to OLTP mode.