Parallel execution (discussed in Chapter 3) works in both OPS and non-OPS environments. In OPS environments, you can exploit the processing power of all of the available nodes to parallelize SQL operations.
In a parallel execution environment, Oracle breaks down a SQL statement into several smaller tasks and assigns these tasks to multiple parallel slave processes. The slave processes then execute their tasks concurrently. Oracle Parallel Server extends this feature by executing parallel slave processes on multiple nodes of the parallel server.
Let’s look at an example of a four-node parallel server database, when a user issues the following query:
SELECT /*+ PARALLEL (orders, 2, 2) */ COUNT(*) FROM orders;
This statement includes a PARALLEL hint specifying DEGREE 2 and INSTANCES 2. Oracle will divide the work of executing this query between two instances and will use two slave processes on each instance to do the work, as illustrated in Figure 13.1.
We mentioned in Chapter 3 that the degree of parallelism could be specified either through a hint or in the table/index definition. The degree of parallelism specification consists of two components: DEGREE and INSTANCES. The INSTANCES component controls the number of instances that will be used to execute the statement in parallel. The DEGREE component specifies the number of parallel slave processes working for the statement on each instance. When the INSTANCES is specified as 1, the parallel slave processes are limited to one instance only.