Oracle RAC:

Oracle RAC is used extensively in many applications like banking applications and massive online transaction systems. However, there is a debate over the use of Oracle RAC in data warehouses. Oracle RAC is not always the best architecture for data warehouse that require large, cohesive RAM regions and tightly-coupled CPU's for fast parallel query. 

There is a debate whether the RAC interconnect will be slower than a monolithic server for large-scale parallel queries with large RAM demands, the type of queries that are common for aggregation, rollups and materialized view refreshing. Some experts say that Oracle RAC is not the best solution to data warehouse applications because they require large banks of CPU's to perform parallel table-scan operations. In this article I will discuss some concerns related to the use of Oracle RAC with data warehouses.

Where does Oracle Parallel execution work?

Parallel execution benefits systems that have all of the below characteristics. If your system lacks any of these characteristics, parallel execution might not significantly improve performance.

• Symmetric multi-processors (SMP), clusters, or massively parallel systems
• Sufficient I/O bandwidth
• Underutilized or intermittently used CPUs
• Sufficient memory to support additional memory-intensive processes

High-parallelism required in Oracle Data Warehouses:

Oracle data warehouse applications require high-parallelism to ensure fast reads of multi-gigabyte tables using Oracle parallel query (OPQ).  In Oracle 10g, automatic parallelism can be enabled and the degree of parallelism is controlled internally by interrogating the number of CPU's on the server (the cpu_count parameter) and setting the automatic degree of parallelism based on the CPU count. Problem occurs in query parallelism when the CPU's are distributed across many servers. 

Apart from the problem of parallel query on RAC we also encounter massive performance issues when attempting to sort a result set from a RAC-based parallel query.

Overhead in sorting oracle parallel results:

There is a huge overhead in sorting the result set because the RAC nodes must transfer the data back to the parallel query coordinator, which will reassemble the data, perform a sort if required, and return the results back to the end user.

While this operation is transparent within a single server, in a RAC environment, billions of bytes of table data must be passed to the coordinator, often with disastrous performance.

RAC Cluster Nodes:

Each node in a RAC cluster has only two or four processors and therefore Oracle parallel query cannot perform as fast as a single monolithic server.  For example, a 8-node RAC configuration with 2 processors each (16 CPUs) will be far slower for large-table scans than a single server with 16 processors.

sort_area_size pga_aggregate_target parameter:

Appropriate setting of sort_area_size pga_aggregate_target may allow one-pass sorts to happen nearly as fast as optimal sorts. 

RAM-SAM:

Using high-speed solid-state disk RAM-SAM for the RAC TEMP tablespace might alleviate the sorting issue on RAC systems.

Oracle Large Table Scans:

Oracle data warehouse applications perform large-table scans quickly, especially during the critical ETL (Extract, transformation, and Load) processing and during online analytical processing, where Oracle must scan large volumes of data very quickly.  On RAC systems with small numbers of processors on each node, there parameters are set based upon the cpu_count of each node.

• fast_start_parallel_rollback
• parallel_max_servers
• log_buffer
• db_block_lru_latches

Conversely, the same number of processors would result in different settings for these parameters, often resulting in faster performance.

Oracle Parallel Query for Oracle Data Warehouse:

The degree of parallelism for Oracle parallel query is dependent upon the number of processors and the disk configuration, but the advent of the SAME (Stripe And Mirror Everywhere) has made the number of CPU's the primary consideration in large-table scan performance.

Oracle High Speed Table Scans:

Oracle achieves high-speed table scans by dividing the table into equal pieces and dedicates a separate CPU process to handle each table section. For example, the speed of large-table full-table scans is far slower on RAC systems with small numbers of processors on each node.  The inter-node parallel query on a RAC cluster system has far higher overhead because the parallel query coordinator must communicate with the slave processes over the network.

Conclusion:

Some hardware vendors may claim that RAC is appropriate for every types of application.  DM Review magazine noted several issues with Oracle RAC and suggests that the primary motive for using RAC should be to achieve continuous availability and transparent failover:

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of crashed Oracle Instance Semaphores and Memory!!
   What if your database lock gets blocked??
   Oracle Recovery from import errors!!
   DBA Tips for Changing Column Order in Oracle!!