Previous | Table of Contents | Next

Page 656

When Enough Query Slaves Are Not Available

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.

Understanding the Query Server Processes

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.

Analyzing Objects to Update Statistics

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

Understanding the 9,3,1 Algorithm

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:

  1. Determining the degree of parallelism as described in the previous section.
  2. Finding out the high water mark for the table and thus detecting the total number of occupied data blocks in the table.
  3. Dividing the blocks to be scanned into equal parts by dividing the total blocks with available query servers. Now you are almost ready to assign equal work to each query slave. However, if the data is not evenly distributed or access to part of the data is slower, some slaves might finish their work earlier than other ones. To address this problem and optimize the performance, Oracle uses the 9,3,1 algorithm.
  4. Oracle further divides each work-partition into three chunks. The first part is 9/13 of the whole. The second and third are 3/13 and 1/13 of the whole, respectively. Thus the whole table is divided into the degree of parallelism * 3 partitions.
  5. The query coordinator now assigns all the 9/13 parts to each of the query slaves. The
    3/13 and 1/13 parts are assigned subsequently, as and when the query slaves finish their earlier assigned work. This ensures all the slaves are used almost equally.

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:

  1. In the Oracle parallel server environment, the Oracle work division algorithm also takes into account the disk affinity and so on.
  2. Oracle8 parallelizes by partitions while parallelizing partitioned indexes and tables.

Understanding Parallel DML

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 Execution in 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

Previous | Table of Contents | Next