It is easy to schedule tasks that
change the RAM memory configuration as the processing needs
change on a UNIX platform. For example, it is common for
Oracle databases to operate in OLTP mode during normal work
hours and to perform the database services memory-intensive
batch reports at night. We have noted that an OLTP database
needs a large db_cache_size value. Memory-intensive
batch tasks require additional RAM in the
pga_aggregate_target parameter.
The UNIX scripts given below can be used to reconfigure
the SGA between the OLTP and DSS without stopping the
instance. The example assumes an isolated Oracle server with
8 gigabytes of RAM, with 10 percent of RAM reserved for UNIX
overhead, leaving 7.2 gigabytes for Oracle and Oracle
connections. The scripts are intended either for HP-UX or
Solaris and accept the $ORACLE_SID as an argument.
This dss_config.ksh script
will be run at 6:00 p.m. each evening in order to
reconfigure Oracle for the memory-intensive batch tasks.
#!/bin/ksh
# First, 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<<!
connect
system/manager as sysdba;
alter system set db_cache_size
=1500m;
alter system set shared_pool_size =500m;
alter system set
pga_aggregate_target =4000m;
exit
!
The script below will be run at 6:00 a.m.
each morning to reconfigure Oracle for the OLTP usage during
the day.
#!/bin/ksh
# First, 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<<!
connect
system/manager as sysdba;
alter system set
db_cache_size
=4000m;
alter system set shared_pool_size =500m;
alter system set
pga_aggregate_target =1500m;
exit
!
Note that the dbms_job
package can also be used to schedule the reconfigurations.
It should now be clear that the
administrator can develop mechanisms to constantly monitor
the processing demands on the database and issue alter
system commands to dynamically respond to these conditions.
