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
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
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
- 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
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:
# First, we must set the environment . . . .
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
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
- 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
- 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
- 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
- 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
For more on 64-bit Oracle, see my