*************** Original Query sql monitor*********************** Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 SQL ID : 0a4xhjbhdfjbk SQL Execution ID : 16777216 Execution Started : 12/24/2020 00:49:28 First Refresh Time : 12/24/2020 00:49:32 Last Refresh Time : 12/24/2020 01:03:37 Duration : 849s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 2 Global Stats ===================================================================================================== | Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read | Write | Write | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload | ===================================================================================================== | 926 | 196 | 730 | 0.22 | 2 | 81196 | 1M | 155GB | 7111 | 833MB | -1.01% | ===================================================================================================== SQL Plan Monitoring Details (Plan Hash Value=2475526405) ============================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | ============================================================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 846 | +4 | 1 | 1 | | | | | | | | | | 1 | VIEW | VW_FOJ_0 | 2M | 43492 | 846 | +4 | 1 | 1 | | | | | | | 0.12 | Cpu (1) | | 2 | HASH JOIN FULL OUTER | | 2M | 43492 | 849 | +1 | 1 | 2M | 1M | 147GB | 2943 | 345MB | 1M | 560M | 94.45 | Cpu (102) | | | | | | | | | | | | | | | | | | direct path read temp (698) | | 3 | TABLE ACCESS STORAGE FULL | BOS | 1M | 3274 | 1 | +4 | 1 | 1M | | | | | | | | | | 4 | VIEW | | 2M | 16676 | 45 | +4 | 1 | 2M | | | | | | | | | | 5 | VIEW | VW_FOJ_1 | 2M | 16676 | 45 | +4 | 1 | 2M | | | | | | | | | | 6 | HASH JOIN FULL OUTER | | 2M | 16676 | 47 | +2 | 1 | 2M | 66255 | 8GB | | | 2M | 373M | 5.43 | Cpu (6) | | | | | | | | | | | | | | | | | | direct path read temp (39) | | | | | | | | | | | | | | | | | | direct path write temp (1) | | 7 | TABLE ACCESS STORAGE FULL | CS | 948K | 2683 | 1 | +4 | 1 | 948K | | | | | | | | | | 8 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 | 1 | +4 | 1 | 2M | | | | | | | | | ============================================================================================================================================================================================================== *************** Original Query with PARALLEL(4) hints sql monitor*********************** Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 SQL Execution ID : 16777533 Execution Started : 12/23/2020 22:15:10 First Refresh Time : 12/23/2020 22:15:10 Last Refresh Time : 12/23/2020 22:15:23 Duration : 13s Fetch Calls : 2 Global Stats ====================================================================================================== | Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read | Write | Write | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload | ====================================================================================================== | 53 | 20 | 32 | 0.32 | 2 | 82861 | 54778 | 7GB | 7335 | 860MB | -21.95% | ====================================================================================================== Parallel Execution Details (DOP=4 , Servers Allocated=8) ==================================================================================================================================================================== | Name | Type | Server# | Elapsed | Cpu | IO | Cluster | Buffer | Read | Read | Write | Write | Cell | Wait Events | | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload | (sample #) | ==================================================================================================================================================================== | PX Coordinator | QC | | 0.02 | 0.02 | | | 15 | | . | | . | NaN% | | | p000 | Set 1 | 1 | 8.48 | 3.06 | 5.33 | 0.08 | 12945 | 9176 | 1GB | 1047 | 123MB | -19.05% | direct path read temp (6) | | p001 | Set 1 | 2 | 8.71 | 3.11 | 5.52 | 0.08 | 13975 | 9310 | 1GB | 1067 | 125MB | -20.48% | direct path read temp (5) | | p002 | Set 1 | 3 | 8.39 | 3.05 | 5.25 | 0.08 | 13302 | 9155 | 1GB | 1060 | 124MB | -20.48% | direct path read temp (5) | | p003 | Set 1 | 4 | 8.76 | 3.26 | 5.42 | 0.08 | 13107 | 9440 | 1GB | 1223 | 143MB | -21.95% | direct path read temp (7) | | p004 | Set 2 | 1 | 4.64 | 1.97 | 2.68 | | 7589 | 4422 | 567MB | 735 | 86MB | -26.58% | cell multiblock physical read (1) | | | | | | | | | | | | | | | direct path read temp (3) | | p005 | Set 2 | 2 | 4.69 | 1.95 | 2.74 | | 7206 | 4425 | 564MB | 734 | 86MB | -26.58% | direct path read temp (3) | | p006 | Set 2 | 3 | 4.60 | 1.95 | 2.65 | | 7177 | 4417 | 563MB | 734 | 86MB | -26.58% | cell multiblock physical read (1) | | | | | | | | | | | | | | | direct path read temp (2) | | p007 | Set 2 | 4 | 4.66 | 1.97 | 2.69 | | 7545 | 4433 | 566MB | 735 | 86MB | -26.58% | direct path read temp (2) | ==================================================================================================================================================================== SQL Plan Monitoring Details (Plan Hash Value=3711507738) ========================================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | ========================================================================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +13 | 9 | 1 | | | | | | | | | | 1 | PX COORDINATOR | | | | 1 | +13 | 9 | 1 | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10004 | 2M | 2547 | 3 | +11 | 4 | 1 | | | | | | | | | | 3 | VIEW | VW_FOJ_0 | 2M | 2547 | 8 | +6 | 4 | 1 | | | | | | | | | | 4 | HASH JOIN FULL OUTER BUFFERED | | 2M | 2547 | 13 | +1 | 4 | 2M | 36772 | 4GB | 4397 | 515MB | 9M | 578M | 63.27 | Cpu (8) | | | | | | | | | | | | | | | | | | direct path read temp (23) | | 5 | PX RECEIVE | | 1M | 908 | 2 | +1 | 4 | 1M | | | | | | | 2.04 | Cpu (1) | | 6 | PX SEND HASH | :TQ10002 | 1M | 908 | 2 | +1 | 4 | 1M | | | | | | | 2.04 | Cpu (1) | | 7 | PX BLOCK ITERATOR | | 1M | 908 | 1 | +2 | 4 | 1M | | | | | | | | | | 8 | TABLE ACCESS STORAGE FULL | BOS | 1M | 908 | 2 | +1 | 50 | 1M | 337 | 226MB | | | | | 6.12 | Cpu (1) | | | | | | | | | | | | | | | | | | cell multiblock physical read (2) | | 9 | PX RECEIVE | | 2M | 1637 | 5 | +2 | 4 | 2M | | | | | | | 2.04 | Cpu (1) | | 10 | PX SEND HASH | :TQ10003 | 2M | 1637 | 4 | +2 | 4 | 2M | | | | | | | | | | 11 | VIEW | | 2M | 1637 | 4 | +2 | 4 | 2M | | | | | | | | | | 12 | VIEW | VW_FOJ_1 | 2M | 1637 | 4 | +2 | 4 | 2M | | | | | | | | | | 13 | HASH JOIN FULL OUTER BUFFERED | | 2M | 1637 | 4 | +2 | 4 | 2M | 17360 | 2GB | 2938 | 344MB | 9M | 386M | 24.49 | Cpu (2) | | | | | | | | | | | | | | | | | | direct path read temp (10) | | 14 | PX RECEIVE | | 948K | 744 | 1 | +2 | 4 | 948K | | | | | | | | | | 15 | PX SEND HASH | :TQ10000 | 948K | 744 | 1 | +2 | 4 | 948K | | | | | | | | | | 16 | PX BLOCK ITERATOR | | 948K | 744 | 1 | +2 | 4 | 948K | | | | | | | | | | 17 | TABLE ACCESS STORAGE FULL | CS | 948K | 744 | 1 | +2 | 54 | 948K | | | | | | | | | | 18 | PX RECEIVE | | 2M | 891 | 1 | +2 | 4 | 2M | | | | | | | | | | 19 | PX SEND HASH | :TQ10001 | 2M | 891 | 1 | +2 | 4 | 2M | | | | | | | | | | 20 | PX BLOCK ITERATOR | | 2M | 891 | 1 | +2 | 4 | 2M | | | | | | | | | | 21 | TABLE ACCESS STORAGE FULL | COX | 2M | 891 | 1 | +2 | 49 | 2M | 309 | 222MB | | | | | | | ==========================================================================================================================================================================================================================