PARALLEL Related Hint

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

NONE	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 RANDOM or RANDOM_LOCAL distribution instead. 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.
PARTITION	This method uses the partitioning information of tablespec to distribute the rows from the query servers to the load servers. Use this distribution method when it is not possible or desirable to combine the query and load operations, when the number of partitions being loaded is greater than or equal to the number of load servers, and the input data will be evenly distributed across the partitions being loaded—that is, there is no skew.
RANDOM	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.
RANDOM_LOCAL	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

HASH, HASH	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.
BROADCAST, NONE	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.
NONE, BROADCAST	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.
PARTITION, NONE	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.
NONE, PARTITION	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.
NONE, NONE	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;

 

参考至：http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50907

如有错误，欢迎指正

邮箱:[e-mail]