Using Oracle's Parallel Execution Features
The full list of Oracle parallel execution features currently includes the following
Operations That Can Be Parallelized
Oracle can parallelize operations that involve processing an entire table or an entire partition. These operations include:
Oracle divides the task of executing a SQL statement into multiple smaller units, each of which is executed by a separate process. When parallel execution is used, the user's shadow process takes on the role of the parallel coordinator. The parallel coordinator is also referred to as parallel execution coordinator or query coordinator.
The parallel coordinator does the following:
The Pool of Parallel Slave Processes
Oracle maintains a pool of parallel slave processes for each instance. The parallel coordinator for a SQL statement assigns parallel tasks to slave processes from this pool. These parallel slave processes remain assigned to a task until its execution is complete. After that, these processes return to the pool and can be assigned tasks from some other parallel operation. A parallel slave process serves only one SQL statement at a time.
The following parameters control the number of parallel slave processes in the pool:
It takes time and resources to create parallel slave processes. Since parallel slave processes can serve only one statement at a time, you should set PARALLEL_MIN_SERVERS to a relatively high value if you need to run lots of parallel statements concurrently. That way, performance won't suffer from the need to constantly create slave processes.
You also need to consider how to set PARALLEL_MAX_SERVERS. Each parallel slave process consumes memory. Setting PARALLEL_MAX_SERVERS too high may lead to memory shortages during peak usage times. On the other hand, if PARALLEL_MAX_SERVERS is set too low, some operations may not get a sufficient number of parallel slave processes.
The Degree of Parallelism
The number of parallel slave processes associated with an operation is called its degree of parallelism . Don't confuse this term with the DEGREE keyword. They aren't exactly the same thing. In Oracle, the degree of parallelism consists of two components, the number of instances to use and the number of slave processes to use on each instance. In Oracle's SQL syntax, the keywords INSTANCES and DEGREE are always used to specify values for these two components as follows:
Level of parallel execution
The degree of parallelism used for a SQL statement can be specified at three different levels:
Oracle determines the degree of parallelism to use for a SQL statement by checking each item in this list in the order shown. Oracle first checks for a degree of parallelism specification at the statement level. If it can't find one, it then checks the table or index definition. If the table or index definition does not explicitly specify values for DEGREE and INSTANCES, Oracle uses the default values established for the instance.
Specifying the degree of parallelism at the statement level
You can specify the degree of parallelism at the statement level by using hints or by using a PARALLEL clause. PARALLEL and PARALLEL_INDEX hints are used to specify the degree of parallelism used for queries and DML statements. However, DDL statements that support parallel execution provide an explicit PARALLEL clause in their syntax.
SELECT /*+ PARALLEL(orders,4,1) */ COUNT(*)
Specifying the degree of parallelism at the object definition level
You can specify the degree of parallelism to use for a table or an index when you create it. You do that by using the PARALLEL clause of the CREATE TABLE and CREATE INDEX statements.
You also can specify a PARALLEL clause when you alter a table or an index.
ALTER TABLE order_items PARALLEL (DEGREE 4);
When you specify DEGREE and INSTANCES values at the table or index level, those values are used for all SQL statements involving the table or index unless overridden by a hint.
Specifying the degree of parallelism at the instance level
Each instance has associated with it a set of default values for DEGREE and INSTANCES. The default DEGREE value is either the number of CPUs available or the number of disks upon which a table or index is stored, whichever is less.
Oracle will use the instance-level defaults whenever the keyword DEFAULT is used in a hint or in a table or index definition. Oracle also will use the instance-level defaults when there are no hints and when no degree of parallelism has been specified at the table or index level.