I am using version 11.2.0.4.0 of oracle. Now we were seeing one sql consuming consider amount of CPU, now going through the real time monitoring plan, i see most of the CPU time was on plan line id- 8, 10, 11,9, so i am just putting the sql monitoring plan here, i am not able to understand how come the actual rows out of the HASH JOIN on step-11, comes around- 26G, whereas the input data volume to the HASH JOIN i.e plan line id- 44 was yielding 583 million and plan line id-12 was yielding 1 million rows during that point in time. i belive as its an equijoin, maximum number of record out of the hash join should be ~583 million. Correct me if wrong.
I see dynamic sampling used-7 in the plan ,but i believe that is due to the parallel operation rather due to missing stats.
Global Information
------------------------------
Status : DONE (ERROR)
Instance ID : 1
Duration : 3091s
Global Stats
====================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Offload |
====================================================================================================================
| 228805 | 227548 | 18 | 0.00 | 3.83 | 0.74 | 1234 | 7M | 156K | 54GB | 82.21% |
====================================================================================================================
Parallel Execution Details (DOP=6 , Servers Allocated=12)
======================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Offload | (sample #) |
======================================================================================================================================================================
| PX Coordinator | QC | | 9.06 | 3.34 | 4.13 | 0.00 | 1.22 | 0.36 | | 1M | 47591 | 9GB | 95.93% | |
| p012 | Set 1 | 1 | 20115 | 19906 | 3.72 | | 0.00 | 0.12 | 205 | 2M | 32781 | 13GB | 80.54% | |
| p013 | Set 1 | 2 | 20026 | 19819 | 1.76 | | 0.00 | 0.05 | 205 | 572K | 11629 | 4GB | 70.24% | |
| p014 | Set 1 | 3 | 20014 | 19810 | 2.32 | | | 0.08 | 202 | 505K | 14883 | 4GB | 72.22% | |
| p015 | Set 1 | 4 | 20070 | 19861 | 0.98 | | | 0.03 | 208 | 455K | 9174 | 3GB | 77.27% | |
| p016 | Set 1 | 5 | 20084 | 19875 | 2.89 | | 0.00 | 0.05 | 206 | 2M | 27806 | 17GB | 84.10% | |
| p017 | Set 1 | 6 | 20078 | 19868 | 2.35 | | 0.00 | 0.04 | 208 | 654K | 12065 | 5GB | 77.97% | |
| p018 | Set 2 | 1 | 17101 | 17101 | | | 0.60 | | | | | . | NaN% | |
| p019 | Set 2 | 2 | 16946 | 16946 | | | 0.20 | | | | | . | NaN% | |
| p020 | Set 2 | 3 | 20806 | 20805 | | | 0.30 | | | | | . | NaN% | |
| p021 | Set 2 | 4 | 17120 | 17120 | | | 0.40 | | | | | . | NaN% | |
| p022 | Set 2 | 5 | 19189 | 19188 | | | 0.60 | | | | | . | NaN% | |
| p023 | Set 2 | 6 | 17247 | 17246 | | | 0.50 | | | | | . | NaN% | |
======================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1817205139)
========================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
========================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 13 | | | | | | | |
| 1 | PX COORDINATOR | | | | | | 13 | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 199M | 5G | | | | | | | | | | |
| 3 | HASH GROUP BY | | 199M | 5G | | | | | | | | | | |
| 4 | PX RECEIVE | | 199M | 5G | | | | | | | | | | |
| 5 | PX SEND HASH | :TQ10002 | 199M | 5G | | | 6 | | | | | | | |
| 6 | HASH GROUP BY | | 199M | 5G | | | 6 | | | | | | | |
| 7 | VIEW | VW | 2T | 5G | | | 6 | | | | | | | |
| 8 | HASH GROUP BY | | 2T | 5G | 21638 | +11 | 6 | 0 | | | | 890M | | |
| 9 | PX RECEIVE | | 2T | 4M | 21638 | +11 | 6 | 26G | | | | | | |
| 10 | PX SEND HASH | :TQ10001 | 2T | 4M | 21622 | +9 | 6 | 26G | | | | | | |
| 11 | HASH JOIN | | 2T | 4M | 22388 | +9 | 6 | 26G | | | | 231M | | |
| 12 | BUFFER SORT | | | | 1 | +9 | 6 | 1M | | | | 171M | | |
| 13 | PX RECEIVE | | 255K | 322K | 1 | +9 | 6 | 1M | | | | | | |
| 14 | PX SEND BROADCAST | :TQ10000 | 255K | 322K | 1 | +9 | 1 | 1M | | | | | | |
| 15 | HASH JOIN | | 255K | 322K | 3 | +7 | 1 | 235K | | | | 1M | | |
| 16 | TABLE ACCESS BY INDEX ROWID | B | 14 | 8 | 1 | +7 | 1 | 14 | | | | | | |
| 17 | INDEX RANGE SCAN | B_IX1 | 14 | 1 | 1 | +7 | 1 | 14 | | | | | | |
| 18 | HASH JOIN | | 241K | 322K | 3 | +7 | 1 | 235K | | | | 2M | | |
| 19 | TABLE ACCESS STORAGE FULL | D | 188 | 18 | 1 | +7 | 1 | 188 | 4 | 248KB | | | | |
| 20 | HASH JOIN | | 241K | 322K | 3 | +7 | 1 | 235K | | | | 881K | | |
| 21 | INLIST ITERATOR | | | | 1 | +7 | 1 | 2 | | | | | | |
| 22 | INDEX UNIQUE SCAN | M_PK | 2 | 1 | 1 | +7 | 2 | 2 | | | | | | |
| 23 | HASH JOIN | | 241K | 322K | 3 | +7 | 1 | 235K | | | | 2M | | |
| 24 | VIEW | index$_join$_002 | 8694 | 51 | 1 | +7 | 1 | 8701 | | | | | | |
| 25 | HASH JOIN | | | | 1 | +7 | 1 | 8701 | | | | 2M | | |
| 26 | INDEX STORAGE FAST FULL SCAN | G_PK | 8694 | 25 | 1 | +7 | 1 | 8701 | | | | | | |
| 27 | INDEX STORAGE FAST FULL SCAN | G_IX2 | 8694 | 39 | 1 | +7 | 1 | 8701 | | | | | | |
| 28 | HASH JOIN | | 241K | 322K | 3 | +7 | 1 | 235K | | | | 26M | | |
| 29 | PARTITION RANGE SINGLE | | 241K | 1777 | 3 | +7 | 1 | 235K | | | | | | |
| 30 | TABLE ACCESS BY LOCAL INDEX ROWID | H | 241K | 1777 | 3 | +7 | 1 | 235K | 7208 | 56MB | | | | |
| 31 | BITMAP CONVERSION TO ROWIDS | | | | 3 | +7 | 1 | 235K | | | | | | |
| 32 | BITMAP AND | | | | 3 | +7 | 1 | 6 | | | | | | |
| 33 | BITMAP INDEX SINGLE VALUE | H_IX4 | | | 3 | +7 | 1 | 5135 | 63 | 504KB | | | | |
| 34 | BITMAP OR | | | | 3 | +7 | 1 | 6 | | | | | | |
| 35 | BITMAP INDEX SINGLE VALUE | H_IX5 | | | 3 | +7 | 1 | 4509 | 53 | 424KB | | | | |
| 36 | BITMAP INDEX SINGLE VALUE | H_IX5 | | | 3 | +7 | 1 | 4409 | 50 | 400KB | | | | |
| 37 | NESTED LOOPS | | 193K | 319K | 1 | +9 | 1 | 195K | | | | | | |
| 38 | NESTED LOOPS | | 1 | 2 | 1 | +9 | 1 | 1 | | | | | | |
| 39 | TABLE ACCESS BY INDEX ROWID | F | 1 | 2 | 1 | +9 | 1 | 1 | | | | | | |
| 40 | INDEX UNIQUE SCAN | F_PK | 1 | 1 | 1 | +9 | 1 | 1 | | | | | | |
| 41 | INDEX UNIQUE SCAN | EN_PK | 1 | | 1 | +9 | 1 | 1 | 1 | 8192 | | | | |
| 42 | TABLE ACCESS STORAGE FULL | E | 193K | 319K | 1 | +9 | 1 | 195K | 8518 | 8GB | 99.83% | 15M | | |
| 43 | PX BLOCK ITERATOR | | 6G | 2M | 22388 | +9 | 6 | 583M | | | | | | |
| 44 | TABLE ACCESS STORAGE FULL | A | 6G | 2M | 22388 | +9 | 492 | 583M | 108K | 46GB | 79.63% | 88M | | |
========================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("A"."C1_key"="H"."C1_key")
15 - access("B"."CL_KEY"="H"."CM_Key" AND "B"."SR_K"="H"."TR_K")
17 - access("B"."CL_KEY"=201701)
18 - access("D"."C_Key"="H"."TR_K")
20 - access("M"."M_K"="H"."M_K")
22 - access("M"."M_K"=111 OR "M"."M_K"=123)
23 - access("G"."C1_key"="H"."C1_key")
25 - access(ROWID=ROWID)
28 - access("E"."C_k"="H"."C_k" AND "F"."CL_KEY"="H"."CM_Key")
30 - filter(("H"."M_K"=111 OR "H"."M_K"=123) AND "H"."R_K"=1 AND "H"."CM_Key"=201701)
33 - access("H"."E_K"=224)
35 - access("H"."M_K"=111)
36 - access("H"."M_K"=123)
40 - access("F"."CL_KEY"=201701)
41 - access("EN"."E_K"=224)
42 - storage("E"."E_AI" IS NULL AND "E"."E_CE"<>'1234' AND "E"."E_CE"<>'2345' AND
"E"."E_CE"<>'1345' AND "E"."E_PI"<>'Null')
filter("E"."E_AI" IS NULL AND "E"."E_CE"<>'1234' AND "E"."E_CE"<>'2345' AND
"E"."E_CE"<>'1345' AND "E"."E_PI"<>'Null')