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

The following data shows some sampled statistics for this SQL from the past week and projected weekly values
when parallel execution is enabled:
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
The plan generated by the tuning optimizer using parallel threads follows. As noted in the preceding analysis, the
execution time using the parallel query is just 2 minutes compared to 24 minutes when the NOPARALLEL hint was used:
Plan hash value: 2152260066
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost(%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228M| 29G| 722K (1)| 02:24:25 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10006 | 228M| 29G| 722K (1)| 02:24:25 |
| 3 | HASH GROUP BY | | 228M| 29G| 722K (1)| 02:24:25 |
| 4 | PX RECEIVE | | 228M| 29G| 23574 (1)| 00:04:43 |
| 5 | PX SEND HASH | :TQ10005 | 228M| 29G| 23574 (1)| 00:04:43 |
|* 6 | HASH JOIN BUFFERED | | 228M| 29G| 23574 (1)| 00:04:43 |
| 7 | PX RECEIVE | | 10 | 270 | 2 (0)| 00:00:01 |
| 8 | PX SEND BROADCAST | :TQ10000 | 10 | 270 | 2 (0)| 00:00:01 |
| 9 | PX BLOCK ITERATOR | | 10 | 270 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | REGION | 10 | 270 | 2 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 114M| 11G| 23496 (1)| 00:04:42 |
| 12 | PX RECEIVE | | 50 | 1450 | 2 (0)| 00:00:01 |
| 13 | PX SEND BROADCAST | :TQ10001 | 50 | 1450 | 2 (0)| 00:00:01 |
| 14 | PX BLOCK ITERATOR | | 50 | 1450 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | NATION | 50 | 1450 | 2 (0)| 00:00:01 |
|* 16 | HASH JOIN | | 57M| 4458M| 23455 (1)| 00:04:42 |
| 17 | PX RECEIVE | | 2040K| 60M| 1165 (1)| 00:00:14 |
| 18 | PX SEND BROADCAST | :TQ10002 | 2040K| 60M| 1165 (1)| 00:00:14 |
| 19 | PX BLOCK ITERATOR | | 2040K| 60M| 1165 (1)| 00:00:14 |
| 20 | TABLE ACCESS FULL | SUPPLIER | 2040K| 60M| 1165 (1)| 00:00:14 |
|* 21 | HASH JOIN | | 28M| 1372M| 22270 (1)| 00:04:28 |
| 22 | PX RECEIVE | | 7204K| 261M| 4182 (1)| 00:00:51 |
| 23 | PX SEND HASH | :TQ10003 | 7204K| 261M| 4182 (1)| 00:00:51 |
| 24 | PX BLOCK ITERATOR | | 7204K| 261M| 4182 (1)| 00:00:51 |
| 25 | TABLE ACCESS FULL| PART | 7204K| 261M| 4182 (1)| 00:00:51 |
| 26 | PX RECEIVE | | 28M| 354M| 18075 (1)| 00:03:37 |
| 27 | PX SEND HASH | :TQ10004 | 28M| 354M| 18075 (1)| 00:03:37 |
| 28 | PX BLOCK ITERATOR | | 28M| 354M| 18075 (1)| 00:03:37 |
| 29 | TABLE ACCESS FULL| PARTSUPP | 28M| 354M| 18075 (1)| 00:03:37 |
----------------------------------------------------------------------------