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

 

   
  Oracle Tips by Burleson

Oracle super-large data buffer

The 64-bit Oracle database now allow for far larger SGA regions. Sadly 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 (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, most of it dedicated to the data buffer caches.

It is important to remember that there are downsides to having a super-large db_cache_size. While direct access to data is done with hashing, there are times when the database must examine all of the blocks in the RAM cache.

  • Systems with high Invalidations – Whenever a program issues a truncate table, uses temporary tables or runs a large data purge, Oracle must sweep all of the blocks in the db_cache_size to remove dirty blocks. This can cause excessive overhead for system with a db_cache_size greater than 10 gigabytes.

  • High Update Systems – The database writer (DBWR) process must sweep all of the blocks in db_cache_size when performing an asynchronous write. Having a huge db_cache_size can cause excessive work for the database writer.

  • RAC systems – Systems using Oracle9i RAC may experience high cross-instance call 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 your system has any of these characteristics, you will need to perform special operations to reduce the stress on the RAM. In these types of systems we can downsize the data buffer caches prior to these operations, flush the buffer (if we use Oracle10g), and then re-size the data buffer region:

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

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


# *********************************************************
#
# We can not re-set the data buffer region beck to
# its original size:
#
# *********************************************************


$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=1500m;
exit
!

In the next section, we will look more closely at the KEEP and RECYCLE data buffers and how objects are selected for inclusion.

 

The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.


Complete Oracle Script Collection Available

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 680 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.

http://www.rampant-books.com/download_adv_mon_tuning.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.