Figure 7.8 Buffer pool segregation in Oracle parallel server.

Of course, it is not always possible to segregate all data into separate buffer pools, especially with a highly de-normalized data warehouse. The Oracle designer will be able to partition the Oracle instances such that similar data queries are launched from the same instance, thereby improving the probability that the data will be waiting in the buffer.

Note: It is possible to run Oracle parallel query on an Oracle parallel server system. In this case, the MPP system would allocate the sub-queries evenly across the nodes, and the concurrency manager would coordinate the receipt of data from each sub-query. Of course, this type of parallel query would run faster than a parallel query on an SMP box because the MPP box has isolated buffer pools. With SMP, the concurrent queries read their data into a common buffer pool.

Parallel processing is ideal for the Oracle data warehouse. Warehouse requests generally involve some type of aggregation (sum, average) and also require full-table scans. As such, parallelism can be used to dramatically reduce the execution time for these types of queries.

The parallel query option behaves the same regardless of whether the Oracle parallel server option has been installed. For more information, see the Oracle Parallel Query section later in this chapter.