Page 656
Once the degree of parallelism for an SQL operation has been decided, query coordinator will try to enlist the available (already created but idle) servers. If available servers are not sufficient, it will create them. If it cannot create the required query slaves due to the MAX_PARALLEL_SERVERS limit, it will create as many as possible and use the available slaves for parallel execution. Prior to version 7.3, Oracle executes the parallel operation with as many query slaves as possible and, if no query slaves are available, the query will be executed in serial. In 7.3 you can specify the minimum number of slaves required to execute the query by using the init.ora parameter PARALLEL_MIN_PERCENT. The default value of this parameter is 0, which simulates behavior prior to 7.3. Any integer value between 0 and 100 can be specified for this parameter. When this parameter is set to a non-zero value, Oracle determines the minimum number of query slaves required for an operation as follows:
Minimum slaves required = Parallel_min_pecent * Degree of parallelism /100
An error is signaled to the user and the SQL operation is aborted when Oracle cannot get the requisite number of query slaves as determined by the previous formula.
When an Oracle instance is started, it also starts the number of parallel query servers, as
defined by the PARALLEL_MIN_SERVERS initialization
parameter. If you have multiple Oracle instances running on a system, each instance has its own set of independent parallel
query slaves. Similarly, in an Oracle parallel server environment, each instance has its own set
of parallel query slaves. If at any time the instance requires more query servers, it will
create them. However, the maximum parallel query slaves created at any time cannot exceed
the PARALLEL_MAX_SERVERS initialization parameter. Idle servers are shut down after they
have been idle for PARALLEL_SERVER_IDLE_TIME, which is specified in minutes. However, the
minimum number of servers active at any time does not go below PARALLEL_MIN_SERVERS.
In the Oracle parallel server environment, an SQL operation can execute on multiple instances. The maximum instances that can participate in a parallel SQL execution is determined by the PARALLEL_MAX_INSTANCE init.ora parameter at the instance where the execution is started. The query coordinator also resides on the same instance where the SQL statement is started.
Oracle uses a cost-based optimizer during parallel execution. If it finds the statistics associated with a table in the data dictionary, it uses them. If the statistics are not present, it collects them on-the-fly. If the statistic present with an object is outdated, Oracle might use the wrong execution plan. You should regularly analyze the objects to keep the latest data dictionary's statistics. Also, after any major data manipulation operation on a table, you should reanalyze the table.
Page 657
Oracle uses various algorithms for parallel execution. Prior to version 8, it mainly parallelized operations based on a full-table scan. The simple scheme of parallelizing a table scan involves the following:
Please note that 9,3,1 is one of the basic algorithms Oracle uses for parallel execution. Oracle parallel execution takes into consideration various other factors, as follows:
As noted earlier, Oracle8 can also parallelize insert, update, and delete operations. To execute an insert, update, or delete operation in parallel, you need to issue the following command:
alter session enable parallel dml ;
If you do not issue this command, the DML operation will be executed in serial, even if the SQL statement contains explicit hints or parallel clauses to parallelize it.
Update and delete can be parallelized only across partitions. However, an insert can be parallelized within a partition. Thus, for an update and delete operation, the maximum degree of parallelism can not be greater than the number of partitions. Thus, the following deletes all the sales transactions less than $100 from the table sales_97, which has three partitions.
delete /*+ parallel (s,3) */ from sales_97 s where sales_value < 100 ;
Page 658
The following are the hints related to the parallel query option:
PARALLEL (<table_name>, m,n)
where
m = number of parallel servers desired for an sql operation, and n = number of instances that should execute the SQL operation. It is useful only in an OPS environment.
Oracle allows multiple OPS instances to participate in parallel execution of an SQL operation.
If there are four Oracle instances, and you execute the parallel operation with the degree
of parallelism equal to 2, two parallel query slaves will be used at each instance.
You can control the number of participating instances by using:
Instance groups allow you to group instances in the desired manner. The PARALLEL_INSTANCE_ GROUP parameter defines the default group on which the instance's parallel operation is executed. PARALLEL_INSTANCE_GROUP is a dynamic parameter, and you can change the value of the PARALLEL_INSTANCE_GROUP parameter at the session or system level by using the ALTER SESSION/SYSTEM command. When a parallel operation is started, all the instances belonging to the Instance groups defined by the PARALLEL_INSTANCE_GROUP parameter are involved in the execution.
Consider a four-instance OPS configuration with the following initialization parameter:
Instance 1 -> INSTANCE_GROUPS = g_12, g_13, g_14, g_123 PARALLEL_INSTANCE_GROUP = g_12