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: