Automatic SQL Tuning in Oracle Database(四)
tistics 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
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 949044725
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 33921 (1)|999:59:59 |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |
|* 2 | HASH JOIN | | 940M| 22G| 184M| 33921 (1)|999:59:59 |
| 3 | TABLE ACCESS FULL| T1 | 7751K| 96M| | 3716 (0)|210:10:45 |
| 4 | TABLE ACCESS FULL| T | 10M| 124M| | 9156 (0)|517:52:06 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
2- Using Parallel Execution
---------------------------
Plan hash value: 3647253920
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 4420 (0)|250:00:56 | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 26 | | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | | Q1,02 | PC
WP | |
|* 5 | HASH JOIN | | 940M| 22G| 23M| 4420 (0)|250:00:56 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 7751K| 96M| | 516 (0)| 29:11:30 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 7751K| 96M| | 516 (0)| 29:11:30 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 7751K| 96M| | 516 (0)| 29:11:30 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| T1 | 7751K| 96M| | 516 (0)| 29:11:30 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 10M| 124M| | 1272 (0)| 71:55:35 | Q1,02 | PCWP | |
| 11 |