Guidelines for tuning Oracle databases

Review the following guidelines to tune Oracle databases.

• Use Oracle Real Application Clusters (RAC).
• Use hardware-based RAID (Oracle-recommended RAID 1 for performance).
• The key initialization parameters in Oracle are SGA_MAX_SIZE, PGA_AGGREGATE_TARGET, DB_CACHE_SIZE, and SHARED_POOL_SIZE. If you use ASMM, SGA_TARGET is the key initialization parameter. Tune the following database parameters by using the recommended values:
  • Number of open cursors: at least 2000
  • Database block buffers: at least 19200. IBM® recommends that you set this value to 0 if SGA memory is equal to or greater than 0.
  • System Global Area (SGA) memory (10g/11g only): greater than 0
  • Shared pool size: at least 900000000
  • Large pool size: at least 614400
  • Number of Processes: at least 2000
  • Number of Sessions: at least 3500
  • Log Buffer: MAX (0.5, (128K * number of CPUs))
  • Sort Area Size: at least 65536
  • Redo Log Files: The behavior of the database writer and archive processes depends on the size of redo logs, thus also influencing performance. Generally, the larger the redo log files, the better the performance. Undersized log files increase checkpoint activity and reduce performance. In addition, to reduce redo log operations, consider the NOLOGGING option.
  • Keep the Hit Ratio for the Data cache greater than 95%.
  • Create enough dispatchers. For more information about creating dispatchers, see the Oracle website.
  • Make sure that the SGA is large enough to accommodate memory reads since physical memory is generally much faster than retrieving data from disk.
  • Measure hit ratios for the library cache of the shared pool with the V$LIBRARYCACHE view. A hit ratio of greater than 95% is optimal.
  • The general rule of thumb is to make the SHARED_POOL_SIZE parameter 50–150% of the size of your DB_CACHE_SIZE.
  • Adjust the PGA_AGGREGATE_TARGET parameter, which determines how efficiently sorting and hashing operations are performed in your database. Use the following formula to get the optimal value:

    SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb,    
    estd_pga_cache_hit_percentage cache_hit_perc, estd_overalloc_count 
    FROM V$PGA_TARGET_ADVICE