The Data Warehouse Development Life Cycle
Parallelism And Oracle Data Warehousing
Oracle Parallel Database And Server
Figure 7.7 A shared memory SMP configuration.
There is a great deal of confusion about the difference between
Oracle's parallel server and parallel query. While Oracle’s parallel
query can be used with any computer configuration, including
standalone processors, SMP, or MPP, Oracle’s parallel server can
only be used on MPP systems. As stated earlier, MPPs are systems
where a number of independent nodes, each with its own memory, share
a common disk resource. As such, SMP is sometimes called shared
memory multiprocessing and MPP is called shared-nothing
multiprocessing. Examples of SMP processors include IBM SP2 and the
IBM SP3, which contains eight processors. In parallel server
parlance, a processor is called a node.
Oracle parallel server only works with MPP because each node on the
MPP box requires its own memory area for the Oracle SGA. To
illustrate, consider the example shown in Figure 7.6. Here, we see
that a four processor system has been configured to share a common
data resource. As such, any user, on any node, will get a complete
view of the entire database. For example, user Scott could login to
node 1 and create a public access table called TIGER. Immediately
after creation, another user on node 4 could also access that table.
In parallel server, careful consideration must be given to the uses
of parallelism, because the resulting system could often perform
slower than a single node system. For example, in parallel server,
the DLM may force the Oracle database writer to write transactions
to the database more frequently than a standalone Oracle database.
When planning for parallel server tasks, it is a good idea to
segregate specific types of tasks to specific nodes (see Figure
7.8). For example, common update routines against tablespace A could
be segregated onto node 1, while queries against tablespace B could
be segregated onto node 2. Because each Oracle instance has its own
complete SGA, a full-table scan on one node will not flush any data
out of the buffer pool of another node.