SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 214

GROUP BY n_name,
r_name,
p_name,
s_name;
The execution plan (not all columns shown, formatted for easy readability) for this
statement with the default mode of ALL_ROWS is the following:
-------------------------------------------------------------
Plan hash value: 3952064199
-------------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228M|
| 1 | PX COORDINATOR | | |
| 2 | PX SEND QC (RANDOM) | :TQ10007 | 228M|
| 3 | HASH GROUP BY | | 228M|
| 4 | PX RECEIVE | | 228M|
| 5 | PX SEND HASH | :TQ10006 | 228M|
|* 6 | HASH JOIN BUFFERED | | 228M|
| 7 | BUFFER SORT | | |
| 8 | PX RECEIVE | | 10 |
| 9 | PX SEND BROADCAST | :TQ10000 | 10 |
| 10 | TABLE ACCESS FULL | REGION | 10 |
|* 11 | HASH JOIN | | 114M|
| 12 | BUFFER SORT | | |
| 13 | PX RECEIVE | | 50 |
| 14 | PX SEND BROADCAST | :TQ10001 | 50 |
| 15 | TABLE ACCESS FULL | NATION | 50 |
|* 16 | HASH JOIN | | 57M|
| 17 | PX RECEIVE | | 2040K|
| 18 | PX SEND HASH | :TQ10004 | 2040K|
| 19 | PX BLOCK ITERATOR | | 2040K|
| 20 | TABLE ACCESS FULL | SUPPLIER | 2040K|
| 21 | PX RECEIVE | | 28M|
| 22 | PX SEND HASH | :TQ10005 | 28M|
|* 23 | HASH JOIN BUFFERED | | 28M|
| 24 | PX RECEIVE | | 7204K|
| 25 | PX SEND HASH | :TQ10002 | 7204K|
| 26 | PX BLOCK ITERATOR | | 7204K|
| 27 | TABLE ACCESS FULL| PART | 7204K|
| 28 | PX RECEIVE | | 28M|
| 29 | PX SEND HASH | :TQ10003 | 28M|
| 30 | PX BLOCK ITERATOR | | 28M|
| 31 | TABLE ACCESS FULL| PARTSUPP | 28M|
-------------------------------------------------------------
It should be noted that the optimizer executed the query in parallel, taking advantage of the
servers and the resources providing high throughput.