PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2029998977
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8589 | 863K| | 346K (1)| 00:00:14 |
| 1 | SORT GROUP BY | | 8589 | 863K| 992K| 346K (1)| 00:00:14 |
|* 2 | HASH JOIN | | 8589 | 863K| | 346K (1)| 00:00:14 |
|* 3 | HASH JOIN | | 8589 | 771K| | 343K (1)| 00:00:14 |
|* 4 | TABLE ACCESS FULL | SITE | 6 | 234 | | 148 (0)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN | | 32M| 1653M| | 343K (1)| 00:00:14 |
| 6 | TABLE ACCESS BY INDEX ROWID | PARTNER | 1 | 25 | | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PARTNER_ETL_PK | 1 | | | 1 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 32M| 873M| | 343K (1)| 00:00:14 |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| DAILY_INVENTORY | 32M| 873M| | 343K (1)| 00:00:14 |
|* 10 | INDEX RANGE SCAN | DAILY_INVENTORY_2_PK | 32M| | | 145K (1)| 00:00:06 |
|* 11 | TABLE ACCESS FULL | TAXONOMY | 453K| 4873K| | 3237 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Here is the output of SQL*Plus AUTOTRACE including the TIMING information:
###############################################################
260327 rows selected.
Elapsed: 00:00:32.31
Execution Plan
----------------------------------------------------------
Plan hash value: 2029998977
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14437 | 1452K| | 481K (1)| 00:00:19 |
| 1 | SORT GROUP BY | | 14437 | 1452K| 1664K| 481K (1)| 00:00:19 |
|* 2 | HASH JOIN | | 14437 | 1452K| | 480K (1)| 00:00:19 |
|* 3 | HASH JOIN | | 14437 | 1297K| | 477K (1)| 00:00:19 |
|* 4 | TABLE ACCESS FULL | SITE | 6 | 234 | | 148 (0)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN | | 45M| 2308M| | 477K (1)| 00:00:19 |
| 6 | TABLE ACCESS BY INDEX ROWID | PARTNER | 1 | 25 | | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PARTNER_ETL_PK | 1 | | | 1 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 45M| 1219M| | 477K (1)| 00:00:19 |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| DAILY_INVENTORY | 45M| 1219M| | 477K (1)| 00:00:19 |
|* 10 | INDEX RANGE SCAN | DAILY_INVENTORY_2_PK | 45M| | | 202K (1)| 00:00:08 |
|* 11 | TABLE ACCESS FULL | TAXONOMY | 453K| 4873K| | 3237 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
180 recursive calls
12 db block gets
503131 consistent gets
0 physical reads
0 redo size
3902706 bytes sent via SQL*Net to client
29185 bytes received via SQL*Net from client
2605 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
260327 rows processed
###############################################################
\
The DBMS_XPLAN.DISPLAY_CURSOR output:
###############################################################
\
/*+ gather_plan_statistics */
Execution Plan
----------------------------------------------------------
Plan hash value: 2370503576
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14437 | 1452K| | 107K (2)| 00:00:05 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 14437 | 1452K| | 107K (2)| 00:00:05 | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 14437 | 1452K| 1664K| 107K (2)| 00:00:05 | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 14437 | 1452K| | 107K (2)| 00:00:05 | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 14437 | 1452K| | 107K (2)| 00:00:05 | Q1,03 | P->P | RANGE |
| 6 | HASH GROUP BY | | 14437 | 1452K| 1664K| 107K (2)| 00:00:05 | Q1,03 | PCWP | |
|* 7 | HASH JOIN | | 14437 | 1452K| | 107K (2)| 00:00:05 | Q1,03 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 453K| 4873K| | 359 (0)| 00:00:01 | Q1,03 | PCWC | |
|* 9 | TABLE ACCESS FULL | TAXONOMY | 453K| 4873K| | 359 (0)| 00:00:01 | Q1,03 | PCWP | |
| 10 | PX RECEIVE | | 14437 | 1297K| | 107K (2)| 00:00:05 | Q1,03 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10002 | 14437 | 1297K| | 107K (2)| 00:00:05 | Q1,02 | P->P | BROADCAST |
|* 12 | HASH JOIN | | 14437 | 1297K| | 107K (2)| 00:00:05 | Q1,02 | PCWP | |
| 13 | JOIN FILTER CREATE | :BF0000 | 6 | 384 | | 18 (0)| 00:00:01 | Q1,02 | PCWP | |
| 14 | PX RECEIVE | | 6 | 384 | | 18 (0)| 00:00:01 | Q1,02 | PCWP | |
| 15 | PX SEND BROADCAST | :TQ10001 | 6 | 384 | | 18 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 16 | MERGE JOIN CARTESIAN | | 6 | 384 | | 18 (0)| 00:00:01 | Q1,01 | PCWP | |
| 17 | PX BLOCK ITERATOR | | 6 | 234 | | 16 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 18 | TABLE ACCESS FULL | SITE | 6 | 234 | | 16 (0)| 00:00:01 | Q1,01 | PCWP | |
| 19 | BUFFER SORT | | 1 | 25 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 20 | PX RECEIVE | | 1 | 25 | | 0 (0)| 00:00:01 | Q1,01 | PCWP | |
| 21 | PX SEND BROADCAST | :TQ10000 | 1 | 25 | | 0 (0)| 00:00:01 | Q1,00 | S->P | BROADCAST |
| 22 | PX SELECTOR | | | | | | | Q1,00 | SCWC | |
| 23 | TABLE ACCESS BY INDEX ROWID BATCHED| PARTNER | 1 | 25 | | 0 (0)| 00:00:01 | Q1,00 | SCWC | |
|* 24 | INDEX RANGE SCAN | PARTNER_ETL_PK | 1 | | | 0 (0)| 00:00:01 | Q1,00 | SCWP | |
| 25 | JOIN FILTER USE | :BF0000 | 45M| 1219M| | 107K (2)| 00:00:05 | Q1,02 | PCWP | |
| 26 | PX BLOCK ITERATOR | | 45M| 1219M| | 107K (2)| 00:00:05 | Q1,02 | PCWC | |
|* 27 | TABLE ACCESS FULL | DAILY_INVENTORY | 45M| 1219M| | 107K (2)| 00:00:05 | Q1,02 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 10
- parallel scans affinitized for buffer cache
Statistics
----------------------------------------------------------
580 recursive calls
22 db block gets
3547473 consistent gets
178861 physical reads
6468392 redo size
3902706 bytes sent via SQL*Net to client
29185 bytes received via SQL*Net from client
2605 SQL*Net roundtrips to/from client
60 sorts (memory)
0 sorts (disk)
260327 rows processed
###############################################################
\
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
###############################################################
\
26 rows selected.
Elapsed: 00:00:00.22
Execution Plan
----------------------------------------------------------
Plan hash value: 3713220770
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 8168 | 16336 | 29 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
262 recursive calls
0 db block gets
759 consistent gets
9 physical reads
584 redo size
1710 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
26 rows processed
###############################################################
\
I'm looking forward for suggestions how to improve the performance of this statement.