Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Justifying a migration from 32-bit to 64-bit Oracle

Oracle Tips by Burleson Consulting
February 23, 2004

 

When deciding whether to upgrade your database servers to a 64-bit environment you should apply a very specific set of criteria to justify your decision. Remember, if your system does not experience any of the 32-bit limitations, then your 64-bit migration may not improve performance. On mission-critical databases where speed is a primary concern, adding additional 32-bit processors may not help.

Oracle provides a wealth of options for 32-bit versus 64-bit, and many shops using proprietary UNIX (Solaris, HP/UX and AIX) have the option of running a 32-bit version of Oracle on a 64-bit server.

When making recommendations for upgrades of entire servers, many Oracle tuning professionals use the analogy of the performance of a 16-bit PC compared to the performance of 32-bit PC. In general, moving to faster CPU architecture can greatly improve the speed of Oracle applications, and many vendors such as UNISYS will allow you to actually load your production system onto one of the new processors for speed benchmarks prior to purchasing the new servers.

Benefits of 64-bit processors include:

  • Improved RAM addressing: A 32-bit word size can only address 2 to the 32nd power, or about 4 gigabytes of RAM. All 64-bit servers allow SGA regions in excess of 20 gigabytes.
     
  • Faster CPU: Intel's 64-bit Itanium2 Architecture is faster than the older 32-bit chipsets. While the faster chips are not a direct result of the 64-bit architecture, they are an important consideration for shops with computationally-intensive databases.
     
  • High parallelism: Multiple CPU and SMP support allow large-scale parallel processing.
     
  • Faster file I/O: 64-bit architecture takes advantage of large data blocks. Big Oracle Blocks (BOB) allow for 32k block sizes, greatly reducing disk I/O for Oracle index access

While it is compelling to adopt 64-bit architectures solely to get the improved RAM addressing, you must remember that not all Oracle database will benefit from a super-large data buffer.

The issue of huge data buffers

The primary markets for 64-bit processors are shops that require the improved RAM addressing of a 64-bit machine. It is not uncommon for large Oracle OLTP shops to run two-gigabyte SGA regions, caching large volumes of critical data for super-fast data access. However, super-large RAM regions are not for everyone.

While a 30 gigabyte db_cache_size might be appropriate for an OLTP shop or a shop that uses a large working set, a super-large SGA will not benefit data warehouse and decision support systems (DSS) where most data access is performed via a full-table scan. Remember, when Oracle performs a full-table scan, the database blocks are read directly into the program global area (PGA), bypassing the data buffer RAM.

As we have noted, 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, you may be tempted to create a super-large RAM data buffer. However, 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 truncated 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: Oracle9i RAC does not like super-large data buffer RAM. You 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 your system has any of these characteristics and you still want to use a large SGA, then you will need to perform special operations to reduce the stress on the RAM during certain processing times. In systems with massive data purges and truncates, and where you can downsize the data buffer caches prior to these operations, you can flush the buffer (if you have Oracle10g), and then resize the data buffer region using this code:

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


In large Oracle applications such as Oracle Application Server 10g (formerly Oracle Application Server 10i), it is not uncommon to see a mix of 32-bit and 64-bit servers. For example, the back-end database and the infrastructure database can run 64-bit while the HTTP servers and Web cache servers are 32-bit machines.

When 32-bit Oracle is acceptable

You might want to stay on a 32-bit server if any of these conditions are true:

  • Linear processing: You do not need multiple-CPU SMP processing (Oracle parallel query) for large-table, full-table scans.
     
  • No need for large data buffers: If you do not have large working sets (e.g., an OLTP database) then 32-bit may be the right choice.
     
  • External bottleneck: A 32-bit architecture is fine if your bottleneck is not in the Oracle database. In an ERP system, the bottleneck may be in the app servers, Web caches, or network, and a faster database will not help.
     
  • High buffer invalidations: If your application performs frequent data purges, data truncations or makes high-volume use of temporary tables, then you may not find large RAM regions suitable.
     
  • Not computationally intensive: If your bottleneck is in the network or disk I/O, then the faster 64-bit CPUs will not improve your overall performance.

Conversely, if any of these conditions are not applicable, you may want to take a closer look at the 64-bit architecture.

While a 64-bit server is not a panacea, there are several well-documented reasons for moving to a 64-bit server. If any of the following conditions are true, then you may want to consider a 64-bit solution:

  • High transactions processing rates: Systems with more than 200 disk I/O's per second may see dramatic improvement in speed and scalability. By caching large amounts of data, disk I/O is reduced and performance skyrockets.
     
  • Declining performance: As systems grow, the 32-bit limitations prevent continued growth.
     
  • Anticipating rapid growth: For systems that require uninterrupted growth and scalability, the 64-bit architecture allows almost infinite scalability. Many large ERP systems have been able to scale successfully on Windows 64 platforms.
     
  • Computationally-intensive system: If your Oracle database is CPU-bound or if you perform lots of parallel full-table scans, then the faster processors in a 64-bit architecture are very appealing.

 

References:

 

For more on 64-bit Oracle, see my related notes:

 


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.