Oracle data buffers invalidations

The 64-bit Oracle database now allows for far larger SGA regions. Unfortunately, a 32-bit word size can only address 2 to the 32^nd power, or about 4 gigabytes of RAM. All 64-bit servers have a larger word size of 2 to the 64^th power that allows for up to 18 billion gigabytes. That’s 18 exabytes! Hence, many Oracle DBAs are running SGAs larger than 20 gigabytes with most of it dedicated to the data buffer caches.

There are downsides to having a large db_cache_size . While direct access to data is done with hashing, there are times when Oracle performance might slow down with a large cache. In these cases, objects may be segregated into a distinct, smaller buffer cache.

§ Objects with High Invalidations: Whenever a program issues a truncate table, uses non-Global temporary tables, or runs a large data purge, Oracle performance might suffer.

§ High Update Objects: Tables and indexes that experience high Data Manipulation Lock (DML) activity may perform better if mapped into a separate buffer cache.

§ RAC systems: Systems using Oracle10g RAC may experience high cross-instance calls when using a large db_cache_size in multiple RAC instances. This inter-instance pinging can cause excessive overhead, and that is why RAC DBA’s try to segregate RAC instances to access specific areas of the database.

If a DBA has a system that has any of these characteristics, special operations to reduce the stress on the RAM will need to be performed.

In these types of systems, the data buffer caches can be downsized prior to these operations, the buffer can be flushed if Oracle10g is in use, and then the data buffer region can be resized using a script like the following:

#!/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

# *********************************************************

# This will reduce the size of the data buffer

# immediately preceding a large truncate or data purge

# *********************************************************

$ORACLE_HOME/bin/sqlplus –s /nologin<<!

connect system/manager as sysdba;

alter system set db_cache_size=10m;

alter system flush buffer_cache;

exit

# *********************************************************

# Now we can invoke the specialty task.

# *********************************************************

nohup purge_job.ksh > /tmp/purge.lst 2>&1 &

$ORACLE_HOME/bin/sqlplus –s /nologin<<!

connect system/manager as sysdba;

alter system set db_cache_size=1500m;

exit

DBA’s must remember that the AMM does not yet analyze detailed workloads. Oracle has provided the KEEP and RECYCLE pools so that the DBA can add intelligence to the database and assign appropriate objects to the right buffer pool. The next section covers the allocation process.