Parallel Operations

Have you moved recently? If you moved yourself, you probably found that having more people working
together resulted in reducing the overall time that it took to complete the job. Of course, there was no
doubt some overhead involved in getting everyone to work together, and there may have been a point
where getting even more people involved actually increased the time to complete the job.
Oracle implements a similar type of work sharing called parallelism. Parallelism makes it possible for Oracle
to split up the work of a specific SQL statement among multiple worker tasks to reduce the elapsed time to
complete the SQL operation.

Oracle has had the capability since version 7, although the scope of this functionality has been continuously
improved. Oracle now supports parallel operations for queries, updates, and inserts, as well as for backup,
recovery, and other operations. Parallelism for batch-type jobs like loading data is also supported. Parallel
operations are only available with the Enterprise Edition of Oracle 10g Database.
Oracle implements parallelism on the server, without requiring any particular code to work. This final
section will look at how Oracle implements parallel operations to improve performance.

Managing Parallel Server Processes

The Oracle database has a pool of parallel server processes available for execution. Oracle automatically
manages the creation and termination of these processes. The minimum number of parallel processes is
specified in the initialization parameter PARALLEL_MIN_SERVERS. As more parallel execution processes
are requested, Oracle starts more parallel execution processes as they are needed, up to the value specified
in the PARALLEL_MAX_SERVERS initialization parameters. The default for this parameter is 5, unless the
PARALLEL_AUTOMATIC_TUNING parameter is set to TRUE, in which case the default is set to the value of
the CPU_COUNT parameter times 10.