WITH stage AS (SELECT ..... FROM GTT), TAB1 AS ( SELECT /*+full(a)*/ stage.pnm, stage.anum, stage.did, SUM (CASE WHEN a.sr_cd = 'Y' AND a.t_amt = stage.p_amt THEN a.t_amt ELSE 0 END) AS s_amt, SUM ( CASE WHEN a.sr_cd = 'Y' AND a.t_amt = stage.p_amt THEN 1 ELSE 0 END) AS S_cnt, SUM ( CASE WHEN a.sr_cd = 'X' AND a.t_amt = stage.p_amt THEN a.t_amt ELSE 0 END) AS sr_amt, SUM (CASE WHEN a.sr_cd = 'X' AND a.t_amt = stage.p_amt THEN 1 ELSE 0 END) AS sr_cnt, .......... FROM TAB1 a, stage WHERE a.aanum = stage.anum AND a.did = stage.did AND a.d_ind = 'Y' AND a.o_id LIKE CASE WHEN stage.al = 'XXXX' THEN stage.o_id ELSE a.o_id END AND a.part_dt BETWEEN :1 AND :2 GROUP BY stage.pnm, stage.anum, stage.did), TAB2 AS ( SELECT /*+full(b)*/ stage.pnm, stage.anum, stage.did, SUM ( CASE WHEN b.t_typ = 'X' AND b.f_c_amt = stage.p_amt THEN b.f_c_amt ELSE 0 END) AS s_rc_amt, SUM ( CASE WHEN b.t_typ = 'X' AND b.f_c_amt = stage.p_amt THEN 1 ELSE 0 END) AS s_rc_cnt, SUM ( CASE WHEN b.t_typ = 'X' THEN b.f_c_amt ELSE 0 END) AS ar_amt, SUM (CASE WHEN b.t_typ = 'X' THEN 1 ELSE 0 END) AS a_r_c_amt FROM TAB2 b, stage WHERE b.anum = stage.anum AND b.did = stage.did AND b.m_num LIKE CASE WHEN stage.al = 'XXXX' THEN stage.o_id ELSE b.m_num END AND b.psa = 'YYYYY' AND b.t_typ = 'X' AND b.i_dt BETWEEN :3 AND :4 GROUP BY stage.pnm, stage.anum, stage.did) SELECT stage.pnm, stage.al, stage.o_id, stage.anum, stage.did, stage.p_amt,TAB1.s_amt,TAB1.S_cnt,....,TAB2.s_rc_amt, TAB2.s_rc_cnt,..... FROM TAB1 TAB1, TAB2 TAB2, stage WHERE TAB1.anum(+) = stage.anum AND TAB1.did(+) = stage.did AND TAB2.anum(+) = stage.anum AND TAB2.did(+) = stage.did AND TAB1.pnm(+) = stage.pnm AND TAB2.pnm(+) = stage.pnm ORDER BY stage.al, stage.pnm, stage.anum, stage.did Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 4 Session : USER1 (497:9825) SQL Execution ID : 67110187 Execution Started : 01/22/2021 09:02:07 First Refresh Time : 01/22/2021 09:02:11 Last Refresh Time : 01/22/2021 09:17:31 Duration : 924s Module/Action : JDBC Thin Client/- Program : JDBC Thin Client Fetch Calls : 4 Binds ======================================================================================================================== | Name | Position | Type | Value | ======================================================================================================================== | :1 | 1 | DATE | 07/25/2020 00:00:00 | | :2 | 2 | DATE | 01/21/2021 00:00:00 | | :3 | 3 | DATE | 07/25/2020 00:00:00 | | :4 | 4 | DATE | 01/21/2021 00:00:00 | ======================================================================================================================== Global Stats ======================================================================================== | Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Cell | | Time(Y) | Time(Y) | Waits(Y) | Waits(Y) | Calls | Gets | Reqs | Bytes | Offload | ======================================================================================== | 987 | 471 | 516 | 0.22 | 4 | 870M | 7M | 6TB | 99.67% | ======================================================================================== SQL Plan Monitoring Details (Plan Hash Value=2890488382) =========================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(Y) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) | =========================================================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +924 | 1 | 34 | | | | | | | | 1 | SORT ORDER BY | | 1000 | 162M | 1 | +924 | 1 | 34 | | | | 6144 | | | | 2 | HASH JOIN RIGHT OUTER | | 1000 | 162M | 1 | +924 | 1 | 34 | | | | 549K | | | | 3 | VIEW | | 1 | 1M | | | 1 | | | | | | | | | 4 | HASH GROUP BY | | 1 | 1M | | | 1 | | | | | | | | | 5 | FILTER | | | | | | 1 | | | | | | | | | 6 | HASH JOIN | | 1 | 1M | 1 | +4 | 1 | 0 | | | | 1M | | | | 7 | JOIN FILTER CREATE | :BF0000 | 1000 | 19 | 1 | +4 | 1 | 34 | | | | | | | | 8 | TABLE ACCESS STORAGE FULL | GTT | 1000 | 19 | 1 | +4 | 1 | 34 | | | | | | | | 9 | JOIN FILTER USE | :BF0000 | 11120 | 1M | 1 | +4 | 1 | 134 | | | | | | | | 10 | PARTITION RANGE ALL | | 11120 | 1M | 1 | +4 | 1 | 134 | | | | | | | | 11 | TABLE ACCESS STORAGE FULL | TAB2 | 11120 | 1M | 4 | +1 | 28 | 134 | | | | 7M | 0.43 | cell smart table scan (4) | | 12 | HASH JOIN RIGHT OUTER | | 1000 | 161M | 1 | +924 | 1 | 34 | | | | 1M | | | | 13 | VIEW | | 1 | 161M | 1 | +924 | 1 | 34 | | | | | | | | 14 | HASH GROUP BY | | 1 | 161M | 913 | +12 | 1 | 34 | | | | 1M | | | | 15 | FILTER | | | | 883 | +12 | 1 | 415 | | | | | | | | 16 | HASH JOIN | | 1 | 161M | 919 | +6 | 1 | 415 | | | | 1M | 0.22 | Cpu (2) | | 17 | JOIN FILTER CREATE | :BF0001 | 1000 | 19 | 1 | +6 | 1 | 34 | | | | | | | | 18 | TABLE ACCESS STORAGE FULL | GTT | 1000 | 19 | 3 | +4 | 1 | 34 | | | | | | | | 19 | JOIN FILTER USE | :BF0001 | 18G | 161M | 919 | +6 | 1 | 5M | | | | | | | | 20 | PARTITION RANGE ITERATOR | | 18G | 161M | 919 | +6 | 1 | 5M | | | | | | | | 21 | TABLE ACCESS STORAGE FULL | TAB1 | 18G | 161M | 921 | +5 | 181 | 5M | 7M | 450GB | 95.21% | 7M | 99.35 | Cpu (402) | | | | | | | | | | | | | | | | reliable message (4) | | | | | | | | | | | | | | | | cell smart table scan (512) | | 22 | TABLE ACCESS STORAGE FULL | GTT | 1000 | 19 | 1 | +924 | 1 | 34 | | | | | | | =========================================================================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TAB2"."anum"=TRIM("anum") AND "TAB2"."did"="did" AND "TAB2"."pnm"="pnm") 5 - filter(:4>=:3) 6 - access("B"."anum"=TRIM("anum") AND "B"."did"="did") filter("B"."m_num" LIKE CASE "al" WHEN 'XXXX' THEN "o_id" ELSE "B"."m_num" END ) 11 - storage(("B"."psa"='YYYYY' AND "B"."i_dt"<=:4 AND "B"."t_typ"='X' AND "B"."i_dt">=:3 AND SYS_OP_BLOOM_FILTER(:BF0000,"B"."anum","B"."did"))) filter(("B"."psa"='YYYYY' AND "B"."i_dt"<=:4 AND "B"."t_typ"='X' AND "B"."i_dt">=:3 AND SYS_OP_BLOOM_FILTER(:BF0000,"B"."anum","B"."did"))) 12 - access("TAB1"."anum"=TRIM("anum") AND "TAB1"."did"="did" AND "TAB1"."pnm"="pnm") 15 - filter(:2>=:1) 16 - access("A"."aanum"=TRIM("anum") AND "A"."did"="did") filter("A"."o_id" LIKE CASE "al" WHEN 'XXXX' THEN "o_id" ELSE "A"."o_id" END ) 21 - storage(("A"."d_ind"='Y' AND "A"."part_dt">=:1 AND "A"."part_dt"<=:2 AND SYS_OP_BLOOM_FILTER(:BF0000,"A"."aanum","A"."did"))) filter(("A"."d_ind"='Y' AND "A"."part_dt">=:1 AND "A"."part_dt"<=:2 AND SYS_OP_BLOOM_FILTER(:BF0000,"A"."aanum","A"."did")))