Database Reference
In-Depth Information
This sequential execution is not efficient for a few types of SQL statements. For example, in the following SQL
statement, most of the time is spent accessing the remote database over the database link; that is, time is spent in the
network transfer. Sequential execution of each branch adds up to a huge elapsed time interval for the SQL statement.
select /*+ PQ_CONCURRENt_UNION */ col1, col2, col3 from orders@retail_oltp
union all
select col1, col2, col3 from warehouse_orders@shipping
union all
select col1, col2, col3 from orders;
Version 12c introduces a concurrent union processing feature, and multiple union branches can be executed in
parallel. So, in this example, since most of the time is spent in the network, executing the union branches concurrently
will reduce the elapsed time of the SQL statement.
In RAC, this new feature has interesting uses. A complex parallel statement execution, with multiple union all
branches, can be designed to execute the utilization of inter-instance parallelism in multiple nodes.
12
Essentially,
each node will work on its own union all branch.
Partition-Wise Join
Traditional PX execution uses the Producer/Consumer model, and partition-wise join is an exception to that model.
In partition-wise join, each PX server will read a partition from joined tables and perform join processing. Finally, joined
result set is sent to the next process in the PX tree or QC. Since this technique does not use the Producer/Consumer
model, PX messaging between the processes is reduced. In RAC, a partition-wise join operation in an inter-instance
parallel execution does not induce excessive interconnect traffic.
An execution plan for a partition-wise join follows. In this plan, you can see that PX PARTITION HASH ALL
in step 5 precedes the HASH JOIN step. This execution sequence indicates that the partition-wise join is in play.
Essentially, every PX server process is performing its operation underneath the PX PARTITION step. Each PX server
performs HASH join between two partitions of the table, aggregates data, and then sends the aggregated results to QC.
As both aggregation and join operations are performed by a PX server process, interconnect traffic is minimal.
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:37.42 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:37.42 |
| 2 | PX COORDINATOR | | 1 | | 8 |00:00:37.42 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 |
| 4 | SORT AGGREGATE | | 0 | 1 | 0 |00:00:00.01 |
| 5 | PX PARTITION HASH ALL| | 0 | 32186 | 0 |00:00:00.01 |
|* 6 | HASH JOIN | | 0 | 32186 | 0 |00:00:00.01 |
| 7 | TABLE ACCESS FULL | HUGETABLE_HASH | 0 | 32186 | 0 |00:00:00.01 |
| 8 | TABLE ACCESS FULL | HUGETABLE_HASH | 0 | 32186 | 0 |00:00:00.01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T1"."N1"="T2"."N1")
12
In 12c, you may not be able to force this sort of inter-instance parallelism where instances are operating on disjoint branches of
union all execution. But, hopefully, future release might provide a mechanism to force a parallel concurrent execution.
