********** Sql monitor by replacing FULL OUTER JOIN with Just left JOIN Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 SQL ID : 03tz49p5nh6ad SQL Execution ID : 16777216 Execution Started : 12/24/2020 07:49:19 First Refresh Time : 12/24/2020 07:49:23 Last Refresh Time : 12/24/2020 07:50:42 Duration : 83s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 1 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 | ====================================================================================================== | 88 | 17 | 72 | 0.00 | 1 | 81196 | 80523 | 9GB | 6332 | 742MB | -14.94% | ====================================================================================================== SQL Plan Monitoring Details (Plan Hash Value=629335526) ============================================================================================================================================================================================================ | 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 | | | | | | | | | | | 1 | FILTER | | | | 48 | +36 | 1 | 0 | | | | | | | | | | 2 | HASH JOIN OUTER | | 3 | 38564 | 83 | +1 | 1 | 1M | 47173 | 5GB | 2094 | 245MB | 2M | 457M | 60.24 | Cpu (8) | | | | | | | | | | | | | | | | | | direct path read temp (42) | | 3 | TABLE ACCESS STORAGE FULL | BOS | 1M | 3274 | 1 | +4 | 1 | 1M | | | | | | | | | | 4 | VIEW | | 1M | 16677 | 33 | +4 | 1 | 1M | | | | | | | | | | 5 | HASH JOIN OUTER | | 1M | 16677 | 35 | +2 | 1 | 1M | 31751 | 4GB | | | 2M | 373M | 39.76 | Cpu (1) | | | | | | | | | | | | | | | | | | direct path read temp (32) | | 6 | TABLE ACCESS STORAGE FULL | CS | 948K | 2683 | 1 | +4 | 1 | 948K | | | | | | | | | | 7 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 | 1 | +4 | 1 | 2M | | | | | | | | | ============================================================================================================================================================================================================ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("COX"."BID1"=TO_NUMBER(:B1) OR "BOS"."BI"=TO_NUMBER(:B2) AND "BOS"."I_DT" IS NULL AND ("COX"."CT3"='XXX' AND "COX"."BID1" IS NOT NULL OR "COX"."BID1" IS NULL AND "COX"."CT3"='YYY')) 2 - access("BOS"."OID"=TO_NUMBER("COX"."OID2"(+)) AND "BOS"."BI"="COX"."BID1"(+)) 5 - access("COX"."EID"(+)="CS"."EID")