Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

The Downside of Mega Data Buffers

The 64-bit Oracle database now allows for far larger SGA regions.  Unfortunately, a 32-bit word size can only address 2 to the 32nd power, or about 4 gigabytes of RAM.  All 64-bit servers have a larger word size of 2 to the 64th 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:

 

§       avoid_high_invalidations.ksh

 

 

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


 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.