Database Reference
In-Depth Information
both scan and change are done by the PX server processes themselves. The keyword
UPDATE
is under the
PX SEND QC
operation, indicating that PX servers perform both scan and update operations.
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |...
-------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 4 |00:00:24.91 |
| 1 | PX COORDINATOR | | 1 | | 4 |00:00:24.91 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 32186 | 0 |00:00:00.01 | |
| 3 | UPDATE | HUGETABLE_HASH | 0 | | 0 |00:00:00.01 |
| 4 | PX BLOCK ITERATOR | | 0 | 32186 | 0 |00:00:00.01 |
|* 5 | TABLE ACCESS FULL| HUGETABLE_HASH | 0 | 32186 | 0 |00:00:00.01 |
-------------------------------------------------------------------------------------------
In the following execution plan, PX servers are scanning and identifying the rows to update. The QC process
performs the change. Notice that the UPDATE keyword precedes the PX SEND QC step, indicating that changes are
performed by the QC process.
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 1028 | 2600 (1)|
| 1 | UPDATE | HUGETABLE_HASH | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 1028 | 2600 (1)|
| 4 | NESTED LOOPS | | 1 | 1028 | 2600 (1)|
| 5 | BUFFER SORT | | | | |
| 6 | PX RECEIVE | | | | |
| 7 | PX SEND BROADCAST | :TQ10002 | | | |
| 8 | VIEW | VW_NSO_1 | 32186 | 408K| 2445 (1)|
| 9 | HASH UNIQUE | | 1 | 817K| |
| 10 | PX RECEIVE | | 1 | 817K| |
| 11 | PX SEND HASH | :TQ10001 | 1 | 817K| |
| 12 | HASH UNIQUE | | 1 | 817K| |
|* 13 | HASH JOIN | | 32186 | 817K| 2445 (1)|
| 14 | PX BLOCK ITERATOR | | 32186 | 408K| 1222 (0)|
| 15 | TABLE ACCESS FULL | HUGETABLE_HASH | 32186 | 408K| 1222 (0)| ...
...
In RAC, you should write parallel DML so that all PX server processes perform the changes. With this strategy,
true parallelism is achieved, as multiple PX servers are performing the DML operation. If only QC is carrying out
the changes and if the magnitude of change is much higher (for example, numerous rows to be updated), then
performance will suffer, as just one process is performing the change while PX servers are idle. Further, with
inter-instance parallelism, multiple LGWR processes can work on writing the redo buffer to the log file, concurrently
improving redo write throughput.
Concurrent Union Processing (12c)
Prior to 12c, union branches were executed one at a time. For example, if there were three
union
branches in a
SQL statement, then each of these branches would have been executed sequentially. Note that each individual
union branch can execute utilizing PX servers, but only one union branch will be active during SQL execution.
