Oracle parallel option:
Parallel option parameter in int.ora:
parallel_degree_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_execution_message_size integer 15343
parallel_max_servers integer 743
parallel_min_servers integer 76
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 4
parallel_servers_target integer 374
parallel_threads_per_cpu integer 2
In an explain plan PX stands for Parallel processing.
PX Send are producer
PX Receiver receiver
PX Coordinator
PX COORDINATOR FORCED SERIAL operation means that, although the plan looks like a parallel execution, Oracle effectively will run this serially at execution time.
IN-OUT column is either P->P or P->S (line 2)
P->P means that data is being sent from one parallel operation to another.
P->S means that data is being sent from a parallel operation to serial operation
P->S QC, which is a single process
PCWC Parallel Combine with Child
PCWP Parallel Combine with Parent
HASH: Hash redistribution is very common in parallel execution in order to achieve an equal distribution among the parallel server processes.
A hash function is applied to the join column and the result dictates which consumer parallel server process should receive the row.
BROADCAST: Broadcast redistribution happens when one of the two result sets in a join operation is much smaller than the other result set. Instead of redistributing rows from both result sets the database sends the smaller result set to all of the consumer parallel server processes in order to guarantee the individual servers are able to complete their join operation.
RANGE: Range redistribution is generally used for parallel sort operations. Individual parallel server processes work on data ranges so that the QC does not have to do any additional sorting but only present the individual parallel server processes results in the correct order.
KEY: Key redistribution ensures result sets for individual key values are clumped together. This is an optimization that is primarily used for partial partition-wise joins to ensure only one side in the join has to be redistributed.
ROUND ROBIN: Round-robin data redistribution can be the final redistribution operation before sending data to the requesting process.
It can also be used in an early stage of a query when no redistribution constraints are required.
Ref:
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf
http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-1-1872400.html
http://www.oracle.com/technetwork/database/manageability/con9579-sql-tune-cookbook-2031472.pdf
Parallel option parameter in int.ora:
parallel_degree_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_execution_message_size integer 15343
parallel_max_servers integer 743
parallel_min_servers integer 76
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 4
parallel_servers_target integer 374
parallel_threads_per_cpu integer 2
In an explain plan PX stands for Parallel processing.
PX Send are producer
PX Receiver receiver
PX Coordinator
PX COORDINATOR FORCED SERIAL operation means that, although the plan looks like a parallel execution, Oracle effectively will run this serially at execution time.
IN-OUT column is either P->P or P->S (line 2)
P->P means that data is being sent from one parallel operation to another.
P->S means that data is being sent from a parallel operation to serial operation
P->S QC, which is a single process
PCWC Parallel Combine with Child
PCWP Parallel Combine with Parent
HASH: Hash redistribution is very common in parallel execution in order to achieve an equal distribution among the parallel server processes.
A hash function is applied to the join column and the result dictates which consumer parallel server process should receive the row.
BROADCAST: Broadcast redistribution happens when one of the two result sets in a join operation is much smaller than the other result set. Instead of redistributing rows from both result sets the database sends the smaller result set to all of the consumer parallel server processes in order to guarantee the individual servers are able to complete their join operation.
RANGE: Range redistribution is generally used for parallel sort operations. Individual parallel server processes work on data ranges so that the QC does not have to do any additional sorting but only present the individual parallel server processes results in the correct order.
KEY: Key redistribution ensures result sets for individual key values are clumped together. This is an optimization that is primarily used for partial partition-wise joins to ensure only one side in the join has to be redistributed.
ROUND ROBIN: Round-robin data redistribution can be the final redistribution operation before sending data to the requesting process.
It can also be used in an early stage of a query when no redistribution constraints are required.
Ref:
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf
http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-1-1872400.html
http://www.oracle.com/technetwork/database/manageability/con9579-sql-tune-cookbook-2031472.pdf
Comments
Post a Comment