PARALLEL Hint
Note on Parallel Hints
Beginning with Oracle Database 11g Release 2 (11.2.0.1), the PARALLEL and NO_PARALLEL hints are statement-level hints and supersede the earlier object-level hints: PARALLEL_INDEX, NO_PARALLEL_INDEX, and previously specified PARALLEL and NO_PARALLEL hints. For PARALLEL, if you specify integer, then that degree of parallelism will be used for the statement. If you omit integer, then the database computes the degree of parallelism. All the access paths that can use parallelism will use the specified or computed degree of parallelism.
In the syntax diagrams below, parallel_hint_statement shows the syntax for statement-level hints, and parallel_hint_object shows the syntax for object-level hints. Object-level hints are supported for backward compatibility, and are superseded by statement-level hints.
parallel_hint_statement::=
Description of the illustration parallel_hint_statement.gif
parallel_hint_object::=
Description of the illustration parallel_hint_object.gif
(See "Specifying a Query Block in a Hint", tablespec::=)
The PARALLEL hint instructs the optimizer to use the specified number of concurrent servers for a parallel operation. This hint overrides the value of the PARALLEL_DEGREE_POLICY initialization parameter. The hint applies to the SELECT, INSERT, MERGE, UPDATE, and DELETE portions of a statement, as well as to the table scan portion. If any parallel restrictions are violated, then the hint is ignored.
Note:
The number of servers that can be used is twice the value in the PARALLEL hint, if sorting or grouping operations also take place.
For a statement-level PARALLEL hint:
-
PARALLEL: The statement always is run parallel, and the database computes the degree of parallelism, which can be 2 or greater. -
PARALLEL (DEFAULT): The optimizer calculates a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter. -
PARALLEL (AUTO): The database computes the degree of parallelism, which can be 1 or greater. If the computed degree of parallelism is 1, then the statement runs serially. -
PARALLEL (MANUAL): The optimizer is forced to use the parallel settings of the objects in the statement.
Note: when use parallel(manual) hint and parallel_degree_policy=auto, the sql statement would be queued when the available parallel slaves is equal to or greater than parallel_servers_target. The parallel slaves won't exceed paralle_max_servers. Besides parallel(manual) hint would override
parallel_min_time_thredshold parameter. And the statement DOP is determined by dictionary DOP. -
PARALLEL (integer): The optimizer uses the degree of parallelism specified by integer.
In the following example, the optimizer calculates the degree of parallelism. The statement always runs in parallel.
SELECT /*+ PARALLEL */ last_name FROM employees;
In the following example, the optimizer calculates the degree of parallelism, but that degree may be 1, in which case the statement will run serially.
SELECT /*+ PARALLEL (AUTO) */ last_name FROM employees;
In the following example, the PARALLEL hint advises the optimizer to use the degree of parallelism currently in effect for the table itself, which is 5:
CREATE TABLE parallel_table (col1 number, col2 VARCHAR2(10)) PARALLEL 5; SELECT /*+ PARALLEL (MANUAL) */ col2 FROM parallel_table;
For an object-level PARALLEL hint:
-
PARALLEL: The query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism. -
PARALLEL (integer): The optimizer uses the degree of parallelism specified by integer. -
PARALLEL (DEFAULT): The optimizer calculates a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
In the following example, the PARALLEL hint overrides the degree of parallelism specified in the employees table definition:
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name FROM employees hr_emp;
In the next example, the PARALLEL hint overrides the degree of parallelism specified in the employees table definition and instructs the optimizer to calculate a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name FROM employees hr_emp;
Oracle ignores parallel hints on temporary tables. Refer to CREATE TABLE and Oracle Database Concepts for more information on parallel execution.
See Also:
- CREATE TABLE and Oracle Database Concepts for more information on parallel execution.
- Oracle Database PL/SQL Packages and Types Reference for information on the
DBMS_PARALLEL_EXECUTE package, which provides methods to apply table changes in chunks of rows. Changes to each chunk are independently committed when there are no errors. - Oracle Database Reference for more information on the
PARALLEL_DEGREE_POLICY initialization parameter - NO_PARALLEL Hint
PARALLEL_INDEX Hint
Description of the illustration parallel_index_hint.gif
(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)
The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans, full scans, and fast full scans for partitioned indexes.
The integer value indicates the degree of parallelism for the specified index. Specifying DEFAULT or no value signifies that the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism. For example, the following hint indicates three parallel execution processes are to be used:
SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */
See Also:
"Note on Parallel Hints" for more information on the parallel hints
PQ_DISTRIBUTE Hint
The PQ_DISTRIBUTE hint instructs the optimizer how to distribute rows among producer and consumer query servers. You can control the distribution of rows for either joins or for load.
Control of Distribution for Load You can control the distribution of rows for parallel INSERT ... SELECT and parallel CREATE TABLE ... AS SELECT statements to direct how rows should be distributed between the producer (query) and the consumer (load) servers. Use the upper branch of the syntax by specifying a single distribution method. The values of the distribution methods and their semantics are described in Table 3-22.
Table 3-22 Distribution Values for Load
Distribution | Description |
| No distribution. That is the query and load operation are combined into each query server. All servers will load all partitions. This lack of distribution is useful to avoid the overhead of distributing rows where there is no skew. Skew can occur due to empty segments or to a predicate in the statement that filters out all rows evaluated by the query. If skew occurs due to using this method, then use either Note: Use this distribution with care. Each partition loaded requires a minimum of 512 KB per process of PGA memory. If you also use compression, then approximately 1.5 MB of PGA memory is consumer per server. |
| This method uses the partitioning information of |
| This method distributes the rows from the producers in a round-robin fashion to the consumers. Use this distribution method when the input data is highly skewed. |
| This method distributes the rows from the producers to a set of servers that are responsible for maintaining a given set of partitions. Two or more servers can be loading the same partition, but no servers are loading all partitions. Use this distribution method when the input data is skewed and combining query and load operations is not possible due to memory constraints. |
For example, in the following direct-load insert operation, the query and load portions of the operation are combined into each query server:
INSERT /*+ APPEND PARALLEL(target_table, 16) PQ_DISTRIBUTE(target_table, NONE) */ INTO target_table SELECT * FROM source_table;
In the following table creation example, the optimizer uses the partitioning of target_table to distribute the rows:
CREATE /*+ PQ_DISTRIBUTE(target_table, PARTITION) */ TABLE target_table NOLOGGING PARALLEL 16 PARTITION BY HASH (l_orderkey) PARTITIONS 512 AS SELECT * FROM source_table;
Control of Distribution for Joins You control the distribution method for joins by specifying two distribution methods, as shown in the lower branch of the syntax diagram, one distribution for the outer table and one distribution for the inner table.
-
outer_distribution is the distribution for the outer table. -
inner_distribution is the distribution for the inner table.
The values of the distributions are HASH, BROADCAST, PARTITION, and NONE. Only six combinations table distributions are valid, as described in Table 3-23:
Table 3-23 Distribution Values for Joins
Distribution | Description |
| The rows of each table are mapped to consumer query servers, using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This distribution is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort merge join. |
| All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This distribution is recommended when the outer table is very small compared with the inner table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is greater than the outer table size. |
| All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This distribution is recommended when the inner table is very small compared with the outer table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is less than the outer table size. |
| The rows of the outer table are mapped using the partitioning of the inner table. The inner table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers. Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key. |
| The rows of the inner table are mapped using the partitioning of the outer table. The outer table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers. Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key. |
| Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equipartitioned on the join keys. |
For example, given two tables r and s that are joined using a hash join, the following query contains a hint to use hash distribution:
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list
FROM r,s
WHERE r.c=s.c;
To broadcast the outer table r, the query is:
SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list
FROM r,s
WHERE r.c=s.c;