Bad slow performance after upgrade to 64-bit server

Question:  I just upgraded my server from 32-bit to 64-bit and my performance has gotten significantly worse.  The 64-bit server has 16 CPU's and 32 gig of RAM.  All SQL was optimized on the 32-bit server and there are no changes to SQL execution plans on the 64-bit server.  This is my SGA data:

How do I adjust poor performance when migrating from 32-bit to 64-bit Oracle?
 

Update - The problem we eventually found out wasn't an Oracle problem at all. We had a hardware problem that wasn't NUMA aware. Oracle had made changes to its code to best manage memory on NUMA aware systems (i.e. Sun Solaris, HP-UX etc).

 

Apparently we had an IBM machine with Redhat Linux that wasn't and hence made the machine do much work in trying to allocate memory from the slave machines. In any case, we have the wrong machine and we are making alternative plans towards addressing that issue.   I understand SLES-9 is NUMA aware, but there may be additional patches required to fully enable it:

www.novell.com/brainshare/europe/05_presentations/tut303.pdf 

www.open-mag.com/00133844925.shtml 

www.hp.com/techservers/hpccn/downloads/bigtux_external_january_2005.pdf 

www.hp.com/techservers/hpccn/downloads/HPCAST_Bigtux.pdf 

www.itjungle.com/tlb/tlb082305-story01.html
 

Answer:  I have seen vast differences in performance after an upgrade to a 64-bit server with performance ranging from a 20% decease to a 300% increase, depending on the type of database load.  Some applications become more CPU-intensive under 64-bit architectures, that's just how they work.

In your case, your server is clearly CPU constrained (high vmstat runqueue values), and if your SQL has not changed execution plans then:

• Try MOSC notes on 64-bit migrations (see below)
   

• If SQL and library cache are optimized, add 8 more processors.

Also, note that Oracle has changed the optimizer costing model from "IO" to CPU" in 10g, and shops that combine an upgrade to 64-bit servers with a 10g migration may want to look at changing the new default for _optimizer_cost_model.

MOSC recommends trying adjustment of some hidden parms, but only at the direct request of Oracle technical support:

_b_tree_bitmap_plans = FALSE
_index_join_enabled = false
optimizer_secure_view_merging = FALSE
_complex_view_merging = FALSE

Going 64-bit means that you can now allocate very large RAM data buffers and increase your shared_pool_size above two gigabytes.  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.  These types of database may not always benefit from an upgrade to a 64 bit server:

• 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.  Some shops dedicate a separate, smaller data buffer (of a different blocksize) for high-update objects.
   

• RAC systems: Oracle RAC and Grid does not perform optimally with super-large data buffer RAM, as available in 64-bit systems. 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. This is why Oracle 10g grid server blades generally contain only 4-gig RAM.
   

MOSC notes on 64-bit upgrades