The Data Warehouse Development Life Cycle
Parallelism And Oracle Data Warehousing
Oracle Parallel Database And Server
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
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
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