Parallel Execution

Parallel execution divides the task of executing a SQL statement into multiple small units, each of which is executed by a separate process.
Also, the incoming data (tables, indexes, partitions) can be divided into parts called granules.
The user shadow process that wants to execute a query in parallel takes on the role as parallel execution coordinator or query coordinator.

The query coordinator does the following:

- Parses the query and determines the degree of parallelism
- Allocates one or two sets of slaves (threads or processes)
- Controls the query and sends instructions to the parallel query slaves
- Determines which tables or indexes need to be scanned by the parallel query slaves
- Produces the final output to the user.

If the running sessions on the database are using all parallel slaves assigned to the database using the parameter PARALLEL_MAX_SERVERS,
all new sessions using parallel execution will run in serial mode.


In Oracle 11g if you set the SGA_TARGET with MEMORY_TARGET parameter then all parallel execution sessions will use the Large Pool for its operations
and this will need to increase the LARGE_POOL_SIZE parameter to avoid ORA-4030 errors.
If the SGA_TARGET parameter is not set then all parallel execution sessions will be processed in the Shared Pool.


Using the Degree Of Parallelism (DOP) with objects:

- objects smaller than 200M should not use parallel
- objects between 200M and 5G should use a DOP of 4
- objects greater than 5G should use a DOP of 32