Hi All,
There is one Select Statement which is showing Performance issue and I have never seen it finish it just keep on running and after waiting for 2-3 hours I kill the session to try something new like until now I tried using INLINE Hint, no_merge hint, use_hash hint, on some tables tried creating indexes like below but they are not wokring with INLINE HINT on p_actvity_tab the cost reduce to 13M but query keep on running.
create index temp_activity_tab_IDX1 on temp_activity_tab (cmpny_cd, orgcrncy);
create index temp_activity_tab_IDX2 on temp_activity_tab (evt_cd);
I Apologies for Posting such a big query and explain plan.. I am getting hard time to evaluate it as the Plan is using Temp tables because of With clause and not getting Idea which Temp table is used for which With Clause table.
Some Information on data in tables
temp_activity_tab - 10M rows
evnt_map_ovr_tab - 1.3K rows
Please let me know if more information is required from my side to look into query.
Note: I have changed the original table and field names
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Problem Query --
with Evnt_tab as ( select distinct eo.cmpny_cd, coalesce(cm.crncy, gc.crncy,eo.crncygrp) orgcrncy, eo.ovr_rd_typ,
coalesce(pc.prd_cls, pc1.prd_cls,eo.prd_cls_evt) prd_cls_evt, eo.cpty_typ, eo.act_id, eo.ovr_act_id
from evnt_map_ovr_tab eo
left outer join crncy_map_tab cm
on eo.crncygrp != '?' and cm.crncy = eo.crncygrp
left outer join ( crncy_grp_map_tab cg inner join crncy_map_tab gc on gc.crncy = cg.crncy) on (cg.crncygrp = eo.crncygrp or eo.crncygrp = '?')
left outer join evnt_map_ovr_tab em
on em.cmpny_cd = eo.cmpny_cd and em.crncygrp = gc.crncy and em.ovr_rd_typ = eo.ovr_rd_typ
and em.prd_cls_evt = eo.prd_cls_evt and em.cpty_typ = eo.cpty_typ and em.act_id = eo.act_id
left outer join prd_cls_tab pc
on eo.ovr_rd_typ = 'P' and pc.prd_cls = eo.prd_cls_evt
left outer join prd_cls_tab pc1
on eo.ovr_rd_typ = 'P' and eo.prd_cls_evt = -1
left outer join evnt_map_ovr_tab em1
on em1.cmpny_cd = eo.cmpny_cd and em1.crncygrp = eo.crncygrp and em1.ovr_rd_typ = eo.ovr_rd_typ
and em1.prd_cls_evt = pc1.prd_cls and em1.cpty_typ = eo.cpty_typ
and em1.act_id = eo.act_id
where em.ovr_act_id is null and em1.ovr_act_id is null ),
p_actvity_tab as(
select ac.crncy_dt, ac.pstg_dt, ac.orgcrncy, ac.org_crncy_amt, ac.usd_amt, ac.usr_id, ac.jr_desc,
ac.evt_desc, ac.cmpny_act, ac.cmpny_ln, ac.stg_cd, ac.sub_cpty_cd,
ac.apprd_df, ac.jr_id, ac.rvrs_pstg_per, ac.rvrs_jr_id, ac.rvrs_dt, ac.dl_id, ac.tr_id,
ac.cr_id, ac.rgn_indcr, ac.file_no, ac.pstg_per, ac.adj_typ, ac.evt_cd, ac.act_id,
ac.bk_typ, ac.act_num, ac.dsk_cd, ac.cmpny_cd, ac.prd_ln, ac.sub_cpty_cmpny_cd, ac.sub_stg_cd,
ac.prd_cls, ac.evt_sgn, ac.act_bsc_cd , ac.dc_indcr, ac.DisplaySeqNum,
ac.dl_vw_id, ac.cmpny_fw_id, ac.pymt_no, ac.pymt_typ, ac.jr_ln_no, ac.local_amt,
ac.rvrs_typ, ac.rvrs_flg, ac.micro_stg_cd, ac.cpty_act, coalesce(ov.act_id,ac.map_act_id) map_act_id,
ac.last_chg_dt ,ac.last_chg_srt,ac.expns_cd ,ac.cnd_no, ac.cpty_cnd_no ,
ac.nr_flag,ac.signoff_flag,ac.nr_indcr,ac.rvrs_indcr,ac.offset_flag,
ac.shlf, ac.Mnemonic, ac.Extension, ac.aggremnt_id ,ac.cpty_nm, ac.cpty_act_no ,
ac.scsort
from temp_activity_tab ac
left join Evnt_tab ov
on ov.cmpny_cd = ac.cmpny_cd and ov.ovr_rd_typ = 'P'
and ac.orgcrncy = ov.orgcrncy and ov.prd_cls_evt = ac.prd_cls and ov.ovr_act_id = ac.act_id
left join evnt_map_tab dem
on dem.act_id = ov.act_id and dem.evt_cd=ac.evt_cd and dem.act_bsc_cd=ac.act_bsc_cd
),
ovr_activity_tab as(
select ac.crncy_dt, ac.pstg_dt, ac.orgcrncy, ac.org_crncy_amt, ac.usd_amt, ac.usr_id, ac.jr_desc,
ac.evt_desc, ac.cmpny_act, ac.cmpny_ln, ac.stg_cd, ac.sub_cpty_cd,
ac.apprd_df, ac.jr_id, ac.rvrs_pstg_per, ac.rvrs_jr_id, ac.rvrs_dt, ac.dl_id, ac.tr_id,
ac.cr_id, ac.rgn_indcr, ac.file_no, ac.pstg_per, ac.adj_typ, ac.evt_cd, ac.act_id,
ac.bk_typ, ac.act_num, ac.dsk_cd, ac.cmpny_cd, ac.prd_ln, ac.sub_cpty_cmpny_cd, ac.sub_stg_cd,
ac.prd_cls, ac.evt_sgn, ac.act_bsc_cd, ac.dc_indcr, ac.DisplaySeqNum,
ac.dl_vw_id, ac.cmpny_fw_id, ac.pymt_no, ac.pymt_typ, ac.jr_ln_no, ac.local_amt,
ac.rvrs_typ, ac.rvrs_flg, ac.micro_stg_cd, ac.cpty_act, coalesce(ov.act_id,ac.map_act_id) map_act_id,
ac.last_chg_dt ,ac.last_chg_srt,ac.expns_cd ,ac.cnd_no,ac.cpty_cnd_no ,
ac.nr_flag,ac.signoff_flag,ac.nr_indcr,ac.rvrs_indcr,ac.offset_flag,
ac.shlf, ac.Mnemonic, ac.Extension, ac.aggremnt_id ,ac.cpty_nm, ac.cpty_act_no ,
ac.scsort
from p_actvity_tab ac
left join Evnt_tab ov
on ov.cmpny_cd = ac.cmpny_cd and ov.ovr_rd_typ = 'E'
and ac.act_id = ac.map_act_id and ac.orgcrncy = ov.orgcrncy and ov.prd_cls_evt = ac.evt_cd and ov.ovr_act_id = ac.act_id
left join evnt_map_tab dem
on dem.act_id = ov.act_id and dem.evt_cd=ac.evt_cd and dem.act_bsc_cd=ac.act_bsc_cd),
nomap_tab as(
SELECT distinct ac.jr_id from ovr_activity_tab ac
WHERE NOT EXISTS (SELECT * from evnt_map_tab em
WHERE em.act_bsc_cd = ac.act_bsc_cd AND em.evt_cd = ac.evt_cd AND em.act_id = ac.map_act_id AND em.bk_typ = ac.bk_typ )),
ActivitySeq1_tab as(
select ac.crncy_dt, ac.pstg_dt, ac.orgcrncy, ac.org_crncy_amt, ac.usd_amt, ac.usr_id, ac.jr_desc,
ac.evt_desc, ac.cmpny_act, ac.cmpny_ln, ac.stg_cd, ac.sub_cpty_cd,
ac.apprd_df, ac.jr_id, ac.rvrs_pstg_per, ac.rvrs_jr_id, ac.rvrs_dt, ac.dl_id, ac.tr_id,
ac.cr_id, ac.rgn_indcr, ac.file_no, ac.pstg_per, ac.adj_typ, ac.evt_cd, ac.act_id,
ac.bk_typ, ac.act_num, ac.dsk_cd, ac.cmpny_cd, ac.prd_ln, ac.sub_cpty_cmpny_cd, ac.sub_stg_cd,
ac.prd_cls, ac.evt_sgn, ac.act_bsc_cd ,
case when nm.jr_id is null then em.dc_indcr else ac.dc_indcr end dc_indcr,
case when nm.jr_id is null then em.DisplaySeqNum else ac.DisplaySeqNum end DisplaySeqNum,
ac.dl_vw_id, ac.cmpny_fw_id, ac.pymt_no, ac.pymt_typ, ac.jr_ln_no, ac.local_amt,
ac.rvrs_typ, ac.rvrs_flg, ac.micro_stg_cd, ac.cpty_act, ac.map_act_id ,
ac.last_chg_dt ,ac.last_chg_srt,ac.expns_cd ,ac.cnd_no,ac.cpty_cnd_no,
ac.nr_flag,ac.signoff_flag,ac.nr_indcr,ac.rvrs_indcr,ac.offset_flag,
ac.shlf, ac.Mnemonic, ac.Extension, ac.aggremnt_id ,ac.cpty_nm, ac.cpty_act_no ,
ac.scsort
from ovr_activity_tab ac
left Join evnt_map_tab em
on ac.evt_cd != 53 /*CURRENCY_REVALUATION*/
and em.act_bsc_cd = ac.act_bsc_cd AND em.evt_cd = ac.evt_cd
AND em.act_id = ac.map_act_id AND em.bk_typ = ac.bk_typ
left outer join nomap_tab nm
on nm.jr_id = ac.jr_id ),
FirstSeqNum_Tab as(
select ac.jr_id, min(ac.DisplaySeqNum) DisplaySeqNum
from ActivitySeq1_tab ac
left outer join nomap_tab nm on nm.jr_id = ac.jr_id where nm.jr_id is NULL
group by ac.jr_id having min(ac.DisplaySeqNum) > 1 ) ,
ActivitySeq2_tab as(
select ac.crncy_dt, ac.pstg_dt, ac.orgcrncy, ac.org_crncy_amt, ac.usd_amt, ac.usr_id, ac.jr_desc,
ac.evt_desc, ac.cmpny_act, ac.cmpny_ln, ac.stg_cd, ac.sub_cpty_cd,
ac.apprd_df, ac.jr_id, ac.rvrs_pstg_per, ac.rvrs_jr_id, ac.rvrs_dt, ac.dl_id, ac.tr_id,
ac.cr_id, ac.rgn_indcr, ac.file_no, ac.pstg_per, ac.adj_typ, ac.evt_cd, ac.act_id,
ac.bk_typ, ac.act_num, ac.dsk_cd, ac.cmpny_cd, ac.prd_ln, ac.sub_cpty_cmpny_cd, ac.sub_stg_cd,
ac.prd_cls, ac.evt_sgn, ac.act_bsc_cd ,
ac.dc_indcr,
case when fs.jr_id is not null then ac.DisplaySeqNum - fs.DisplaySeqNum + 1 else ac.DisplaySeqNum end DisplaySeqNum,
ac.dl_vw_id, ac.cmpny_fw_id, ac.pymt_no, ac.pymt_typ, ac.jr_ln_no, ac.local_amt,
ac.rvrs_typ, ac.rvrs_flg, ac.micro_stg_cd, ac.cpty_act, ac.map_act_id ,
ac.last_chg_dt ,ac.last_chg_srt,ac.expns_cd ,ac.cnd_no,ac.cpty_cnd_no,
ac.nr_flag,ac.signoff_flag,ac.nr_indcr,ac.rvrs_indcr,ac.offset_flag,
ac.shlf, ac.Mnemonic, ac.Extension, ac.aggremnt_id ,ac.cpty_nm, ac.cpty_act_no ,
ac.scsort
from ActivitySeq1_tab ac
left join FirstSeqNum_Tab fs
on fs.jr_id = ac.jr_id),
ActivitySeq3_tab as(
select ac.crncy_dt, ac.pstg_dt, ac.orgcrncy, ac.org_crncy_amt, ac.usd_amt, ac.usr_id, ac.jr_desc,
ac.evt_desc, ac.cmpny_act, ac.cmpny_ln, ac.stg_cd, ac.sub_cpty_cd,
ac.apprd_df, ac.jr_id, ac.rvrs_pstg_per, ac.rvrs_jr_id, ac.rvrs_dt, ac.dl_id, ac.tr_id,
ac.cr_id, ac.rgn_indcr, ac.file_no, ac.pstg_per, ac.adj_typ, ac.evt_cd, ac.act_id,
ac.bk_typ, ac.act_num, ac.dsk_cd, ac.cmpny_cd, ac.prd_ln, ac.sub_cpty_cmpny_cd, ac.sub_stg_cd,
ac.prd_cls, ac.evt_sgn, ac.act_bsc_cd ,
case when em.dc_indcr is not null then em.dc_indcr else ac.dc_indcr end dc_indcr,
ac.DisplaySeqNum,
ac.dl_vw_id, ac.cmpny_fw_id, ac.pymt_no, ac.pymt_typ, ac.jr_ln_no, ac.local_amt,
ac.rvrs_typ, ac.rvrs_flg, ac.micro_stg_cd, ac.cpty_act, ac.map_act_id ,
ac.last_chg_dt ,ac.last_chg_srt,ac.expns_cd ,ac.cnd_no,ac.cpty_cnd_no,
ac.nr_flag,ac.signoff_flag,ac.nr_indcr,ac.rvrs_indcr,ac.offset_flag,
ac.shlf, ac.Mnemonic, ac.Extension, ac.aggremnt_id ,ac.cpty_nm, ac.cpty_act_no ,
ac.scsort
from ActivitySeq2_tab ac
left join evnt_map_tab em
on ac.evt_cd != 53 and em.act_bsc_cd = ac.act_bsc_cd
AND em.evt_cd = ac.evt_cd AND em.act_id = ac.map_act_id AND em.bk_typ = ac.bk_typ and ac.dc_indcr = ' '),
dc_tab as(
select ar.rgn_indcr, ar.pstg_per, ar.jr_id, max(ar.dc_indcr) dc_indcr
from ActivitySeq3_tab ar where ar.DisplaySeqNum = 1
group by ar.rgn_indcr, ar.pstg_per, ar.jr_id ),
ARSeqNum_Tab as(
SELECT case when nm.jr_id is NULL then ((a.DisplaySeqNum * (2-instr(a.dc_indcr,' ')) ) - instr(a.dc_indcr,'D')) else a.DisplaySeqNum end SeqNum, a.*
from ActivitySeq3_tab a
left outer join nomap_tab nm on nm.jr_id = a.jr_id ),
MinSeqNum as(
SELECT jr_id, min(SeqNum) SeqNum
from ARSeqNum_Tab GROUP by jr_id),
ActivityAmounts_tab as(
select to_char(ar.crncy_dt,'YYYY/MM/DD') crncy_dt, to_char(ar.pstg_dt,'YYYY/MM/DD') pstg_dt,ar.orgcrncy,
round( min(cast( substr('1 ' || cast(ar.evt_sgn as char(2))|| cast( - ar.evt_sgn as char(2))|| cast( ar.evt_sgn as char(2)), instr( 'DC?',ar.dc_indcr) * 2 + 1, 2) as int)) over (PARTITION BY ar.rgn_indcr, ar.pstg_per, ar.jr_id order by ar.jr_id) * ar.org_crncy_amt, 2) org_crncy_amt,
round( min(cast( substr('1 ' || cast(ar.evt_sgn as char(2))|| cast( - ar.evt_sgn as char(2))|| cast( ar.evt_sgn as char(2)), instr( 'DC?',ar.dc_indcr) * 2 + 1, 2) as int)) over (PARTITION BY ar.rgn_indcr, ar.pstg_per, ar.jr_id order by ar.jr_id) * ar.usd_amt, 2) usd_amt,
ar.usr_id, nvl(rtrim(ar.jr_desc), ' ') jr_desc,ar.adj_typ, nvl(rtrim(ar.evt_desc), ' ') evt_desc,
rtrim(cast(ar.dl_id as varchar2(10))) dl_id,rtrim(cast(ar.tr_id as varchar2(6))) tr_id, rtrim(cast(ar.cr_id as varchar2(4))) cr_id, ar.cmpny_act,
rtrim(ar.cmpny_ln) cmpny_ln, ar.stg_cd, rtrim(ar.sub_cpty_cd) sub_cpty_cd, ar.apprd_df, rtrim(cast(ar.jr_id as varchar2(10))) jr_id, ar.rvrs_pstg_per,
ar.rvrs_jr_id, ar.rgn_indcr, cast(ar.pstg_per as char(6)) pstg_per, ar.file_no, ar.micro_stg_cd, ar.pymt_no, ar.signoff_flag,
case when ar.nr_indcr='U' then ar.nr_flag else ar.nr_indcr end nr_indcr ,
ar.nr_flag, ar.rvrs_indcr, ar.offset_flag,
round( min(cast( substr('1 ' || cast(ar.evt_sgn as char(2))|| cast( - ar.evt_sgn as char(2))|| cast( ar.evt_sgn as char(2)), instr( 'DC?',ar.dc_indcr) * 2 + 1, 2) as int)) over (PARTITION BY ar.rgn_indcr, ar.pstg_per, ar.jr_id order by ar.jr_id) * ar.local_amt, 2) local_amt,
ar.last_chg_dt ,ar.last_chg_srt,
ar.expns_cd ,ar.cnd_no,ar.cpty_cnd_no ,ar.sub_stg_cd,ar.dsk_cd,ar.cpty_act,ar.rvrs_dt, ar.dl_vw_id, ar.evt_cd, ar.cmpny_fw_id, ar.pymt_typ, ar.rvrs_typ,
case when rvrs_flg!='N' and (adj_typ NOT IN ('S','M','R') or rvrs_jr_id!= 0) then 'N' else ar.rvrs_flg end rvrs_flg,
ar.cmpny_cd, ar.prd_cls,
ar.shlf, ar.Mnemonic, ar.Extension, ar.aggremnt_id ,ar.cpty_nm, ar.cpty_act_no ,
ar.scsort
from ARSeqNum_Tab ar, dc_tab dc, MinSeqNum m --, ARSeqNum_Tab ar2
where ar.SeqNum = m.SeqNum and ar.jr_id = m.jr_id
and dc.rgn_indcr = ar.rgn_indcr and dc.pstg_per = ar.pstg_per
and dc.jr_id = ar.jr_id and dc.dc_indcr = ar.dc_indcr),
ActivityWebAmount_Tab as(
select ar.dl_id, ar.tr_id, ar.cr_id,ar.pstg_dt,ar.rgn_indcr, ar.pstg_per, ar.jr_id,
ar.dc_indcr dc_indcr ,ar2.act_id,ar2.bk_typ,ar2.org_crncy_amt,ar2.usd_amt,
ar2.local_amt, ar2.jr_ln_no ,ar.scsort
from ARSeqNum_Tab ar, dc_tab dc, MinSeqNum m , ARSeqNum_Tab ar2
where ar.SeqNum = m.SeqNum and ar.jr_id = m.jr_id and dc.rgn_indcr = ar.rgn_indcr
and dc.pstg_per = ar.pstg_per and dc.jr_id = ar.jr_id and dc.dc_indcr = ar.dc_indcr
and ar2.rgn_indcr = ar.rgn_indcr and ar2.pstg_per = ar.pstg_per and ar2.jr_id=ar.jr_id
order by ar.scsort asc,ar2.jr_ln_no asc),
ActRPT_Tab as(
SELECT RowNum SeqNum, ar.dl_id, ar.tr_id, ar.cr_id, ar.file_no, cast(ar.crncy_dt as varchar2(10)) crncy_dt, cast(ar.pstg_dt as varchar2(10)) pstg_dt,
cast(ar.orgcrncy as varchar2(3)) orgcrncy, ar.org_crncy_amt, ar.local_amt,ar.usd_amt, ar.adj_typ, ar.expns_cd,
cast(ar.cmpny_act as varchar2(8)) cmpny_act, ar.cnd_no, ar.cmpny_ln, cast(ar.micro_stg_cd as varchar2(4)) micro_stg_cd,
cast(ar.stg_cd as varchar2(3)) stg_cd, ar.cpty_cnd_no, ar.sub_cpty_cd, ar.evt_desc,
cast(ar.usr_id as varchar2(8)) usr_id, ar.jr_desc, cast(ar.pstg_per as varchar2(6)) pstg_per,
cast(ar.jr_id as varchar2(8)) jr_id, ar.rvrs_pstg_per, ar.rvrs_jr_id, ar.rgn_indcr,ar.last_chg_dt ,
cast('' as varchar2(10)) dsb_jr_id, cast('' as char(1)) dsb_rgn_indcr, cast('' as varchar2(10)) dsb_pstg_dt,
substr ('YN', instr('N',rvrs_flg)+1, 1) rvrs_flg, ar.apprd_df, ar.signoff_flag, ar.nr_indcr,
ar.rvrs_indcr, ar.offset_flag,cast(ar.sub_stg_cd as varchar2(2)) sub_stg_cd,
cast(ar.dsk_cd as varchar2(2)) dsk_cd,cast(ar.cpty_act as varchar2(8)) cpty_act,
to_char(ar.rvrs_dt,'YYYY/MM/DD') rvrs_dt,ar.pymt_no,ar.dl_vw_id, ar.evt_cd, ar.cmpny_fw_id, ar.pymt_typ, ar.rvrs_typ,
ar.cmpny_cd,ar.prd_cls,cast('1278637384X' as varchar2(40)) SessionKey,
ar.shlf, ar.Mnemonic, ar.Extension, ar.aggremnt_id ,ar.cpty_nm, ar.cpty_act_no ,
cast('N' as char(1)) isFed_Z2 ,cast('N' as char(1)) isFed_KL , ar.scsort
from ActivityAmounts_tab ar
),
ActDtlRPT_Tab as(
select RowNum Dtl_Id ,rtrim(cast(ar.dl_id as varchar2(10))) dl_id,rtrim(cast(ar.tr_id as varchar2(6))) tr_id,
rtrim(cast(ar.cr_id as varchar2(4))) cr_id ,to_char(pstg_dt,'YYYY/MM/DD') pstg_dt,cast(ar.jr_id as varchar2(8)) jr_id,
ar.rgn_indcr, cast(ar.pstg_per as varchar2(6)) pstg_per ,cast('1278637384X' as varchar2(40)) SessionKey,
ar.act_id,ar.bk_typ,ar.org_crncy_amt,ar.usd_amt,ar.local_amt,ar.jr_ln_no , ar.scsort
from ActivityWebAmount_Tab ar ),
ActDtlRPT_Tab as( select RowNum Dtl_Id ,rtrim(cast(ar.dl_id as varchar2(10))) dl_id,rtrim(cast(ar.tr_id as varchar2(6))) tr_id,
rtrim(cast(ar.cr_id as varchar2(4))) cr_id ,to_char(pstg_dt,'YYYY/MM/DD') pstg_dt,cast(ar.jr_id as varchar2(8)) jr_id,
ar.rgn_indcr, cast(ar.pstg_per as varchar2(6)) pstg_per ,cast('1278637384X' as varchar2(40)) SessionKey,
ar.act_id,ar.bk_typ,ar.org_crncy_amt,ar.usd_amt,ar.local_amt,ar.jr_ln_no , ar.scsort
from ActivityWebAmount_Tab ar )
select /*+ no_parallel */ ac.SeqNum,ac.dl_id,ac.tr_id,ac.cr_id,ac.file_no,ac.crncy_dt,
ac.pstg_dt,ac.orgcrncy,ac.org_crncy_amt,ac.local_amt,ac.usd_amt,
ac.adj_typ,ac.expns_cd,ac.cmpny_act,ac.cnd_no,ac.cmpny_ln,
ac.micro_stg_cd,ac.stg_cd,ac.cpty_cnd_no,
ac.sub_cpty_cd,ac.evt_desc,ac.usr_id,ac.jr_desc,ac.pstg_per,
ac.jr_id,ac.rvrs_pstg_per,ac.rvrs_jr_id,ac.rgn_indcr,
ac.last_chg_dt,ac.dsb_jr_id,ac.dsb_rgn_indcr,ac.dsb_pstg_dt,
ac.rvrs_flg,ac.apprd_df,ac.signoff_flag,ac.nr_indcr,
ac.rvrs_indcr,ac.offset_flag,ac.sub_stg_cd,ac.dsk_cd,
ac.cpty_act,ac.rvrs_dt,ac.pymt_no,ac.dl_vw_id,ac.evt_cd,ac.cmpny_fw_id,
ac.pymt_typ,ac.rvrs_typ,ac.cmpny_cd,ac.prd_cls,ac.SessionKey,current_date RowCreateD,
ac.shlf,ac.Mnemonic,ac.Extension,ac.aggremnt_id,ac.cpty_nm,
ac.cpty_act_no,ac.isFed_Z2,ac.isFed_KL,
ad.Dtl_id,ad.act_id dtl_OasysActI ,ad.bk_typ ,ad.org_crncy_amt ,
ad.usd_amt ,ad.local_amt ,ad.jr_ln_no
from ActRPT_Tab ac
left join ActDtlRPT_Tab ad on ac.SessionKey = ad.SessionKey
and ac.dl_id = ad.dl_id and ac.tr_id = ad.tr_id and ac.cr_id = ad.cr_id
and ac.jr_id = ad.jr_id and ac.rgn_indcr = ad.rgn_indcr and ac.pstg_per = ad.pstg_per
order by ac.SeqNum asc;
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20M(100)| |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8301_3FB2270A | | | | | |
| 3 | HASH UNIQUE | | 16073 | 1428K| 1800K| 2076 (1)| 00:00:01 |
|* 4 | FILTER | | | | | | |
|* 5 | HASH JOIN RIGHT OUTER | | 16073 | 1428K| | 1736 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL | evnt_map_ovr_tab | 1335 | 33375 | | 8 (0)| 00:00:01 |
| 7 | MERGE JOIN OUTER | | 42239 | 2722K| | 1727 (1)| 00:00:01 |
|* 8 | FILTER | | | | | | |
|* 9 | HASH JOIN RIGHT OUTER | | 374 | 23188 | | 1353 (1)| 00:00:01 |
| 10 | TABLE ACCESS FULL | evnt_map_ovr_tab | 1335 | 33375 | | 8 (0)| 00:00:01 |
| 11 | MERGE JOIN OUTER | | 37380 | 1350K| | 1345 (1)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 1335 | 44055 | | 8 (0)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1335 | 38715 | | 8 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | evnt_map_ovr_tab | 1335 | 33375 | | 8 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | OASYSFINDB_PRDCLASTB_IDX1 | 1 | 4 | | 0 (0)| |
|* 16 | INDEX UNIQUE SCAN | OASYSFINDB_CRCYMAPTB_IDX2 | 1 | 4 | | 0 (0)| |
| 17 | BUFFER SORT | | 28 | 112 | | 1345 (1)| 00:00:01 |
| 18 | VIEW | VW_LAT_30F7E6E7 | 28 | 112 | | 1 (0)| 00:00:01 |
| 19 | NESTED LOOPS SEMI | | 28 | 336 | | 1 (0)| 00:00:01 |
|* 20 | INDEX FULL SCAN | OASYSFINDB_CRCYGRPMAPTB_IDX1 | 28 | 224 | | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | OASYSFINDB_CRCYMAPTB_IDX2 | 231 | 924 | | 0 (0)| |
| 22 | BUFFER SORT | | 113 | 452 | | 1719 (1)| 00:00:01 |
| 23 | VIEW | VW_LAT_30F7E6E7 | 113 | 452 | | 1 (0)| 00:00:01 |
|* 24 | FILTER | | | | | | |
| 25 | INDEX FULL SCAN | OASYSFINDB_PRDCLASTB_IDX1 | 113 | 1356 | | 1 (0)| 00:00:01 |
| 26 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8302_3FB2270A | | | | | |
|* 27 | FILTER | | | | | | |
|* 28 | HASH JOIN RIGHT OUTER | | 10M| 4378M| | 27183 (2)| 00:00:02 |
| 29 | INDEX FAST FULL SCAN | OASYSFINDB_EVENTMAPTB_IDX1 | 11215 | 131K| | 14 (0)| 00:00:01 |
|* 30 | HASH JOIN RIGHT OUTER | | 10M| 4263M| | 27150 (2)| 00:00:02 |
|* 31 | VIEW | | 16073 | 737K| | 56 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8301_3FB2270A | 16073 | 517K| | 56 (0)| 00:00:01 |
|* 33 | HASH JOIN RIGHT OUTER | | 10M| 3814M| | 27075 (2)| 00:00:02 |
| 34 | INDEX FAST FULL SCAN | OASYSFINDB_EVENTMAPTB_IDX1 | 11215 | 131K| | 14 (0)| 00:00:01 |
|* 35 | HASH JOIN RIGHT OUTER | | 10M| 3699M| | 27042 (2)| 00:00:02 |
|* 36 | VIEW | | 16073 | 737K| | 56 (0)| 00:00:01 |
| 37 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8301_3FB2270A | 16073 | 517K| | 56 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | temp_activity_tab | 10M| 3250M| | 26967 (2)| 00:00:02 |
| 39 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8303_3FB2270A | | | | | |
| 40 | HASH UNIQUE | | 4773K| 195M| 3058M| 1210K (1)| 00:00:48 |
|* 41 | HASH JOIN RIGHT ANTI | | 56M| 2328M| | 957K (1)| 00:00:38 |
| 42 | INDEX FAST FULL SCAN | OASYSFINDB_EVENTMAPTB_IDX1 | 11215 | 153K| | 14 (0)| 00:00:01 |
| 43 | VIEW | | 56M| 1570M| | 957K (1)| 00:00:38 |
| 44 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8302_3FB2270A | 56M| 19G| | 957K (1)| 00:00:38 |
| 45 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8304_3FB2270A | | | | | |
|* 46 | HASH JOIN RIGHT OUTER | | 56M| 39G| | 2905K (1)| 00:01:54 |
| 47 | TABLE ACCESS FULL | evnt_map_tab | 11215 | 208K| | 27 (0)| 00:00:01 |
|* 48 | HASH JOIN RIGHT OUTER | | 56M| 38G| 113M| 2905K (1)| 00:01:54 |
| 49 | VIEW | | 4773K| 59M| | 7558 (1)| 00:00:01 |
| 50 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8303_3FB2270A | 4773K| 27M| | 7558 (1)| 00:00:01 |
| 51 | VIEW | | 56M| 37G| | 957K (1)| 00:00:38 |
| 52 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8302_3FB2270A | 56M| 19G| | 957K (1)| 00:00:38 |
| 53 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8305_3FB2270A | | | | | |
|* 54 | HASH JOIN RIGHT OUTER | | 56M| 39G| | 3192K (1)| 00:02:05 |
| 55 | VIEW | | 28386 | 720K| | 1644K (1)| 00:01:05 |
|* 56 | FILTER | | | | | | |
| 57 | HASH GROUP BY | | 28386 | 693K| 17M| 1644K (1)| 00:01:05 |
|* 58 | FILTER | | | | | | |
|* 59 | HASH JOIN RIGHT OUTER | | 567K| 13M| 81M| 1642K (1)| 00:01:05 |
| 60 | VIEW | | 4773K| 27M| | 7558 (1)| 00:00:01 |
| 61 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8303_3FB2270A | 4773K| 27M| | 7558 (1)| 00:00:01 |
| 62 | VIEW | | 56M| 1028M| | 1547K (1)| 00:01:01 |
| 63 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8304_3FB2270A | 56M| 19G| | 1547K (1)| 00:01:01 |
|* 64 | HASH JOIN RIGHT OUTER | | 56M| 38G| | 1547K (1)| 00:01:01 |
| 65 | TABLE ACCESS FULL | evnt_map_tab | 11215 | 175K| | 27 (0)| 00:00:01 |
| 66 | VIEW | | 56M| 37G| | 1547K (1)| 00:01:01 |
| 67 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8304_3FB2270A | 56M| 19G| | 1547K (1)| 00:01:01 |
| 68 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8306_3FB2270A | | | | | |
| 69 | HASH GROUP BY | | 7160K| 191M| 2397M| 1758K (1)| 00:01:09 |
|* 70 | VIEW | | 56M| 1515M| | 1566K (1)| 00:01:02 |
| 71 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8305_3FB2270A | 56M| 19G| | 1566K (1)| 00:01:02 |
| 72 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8307_3FB2270A | | | | | |
|* 73 | HASH JOIN RIGHT OUTER | | 56M| 38G| 113M| 3511K (1)| 00:02:18 |
| 74 | VIEW | | 4773K| 59M| | 7558 (1)| 00:00:01 |
| 75 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8303_3FB2270A | 4773K| 27M| | 7558 (1)| 00:00:01 |
| 76 | VIEW | | 56M| 37G| | 1566K (1)| 00:01:02 |
| 77 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8305_3FB2270A | 56M| 19G| | 1566K (1)| 00:01:02 |
| 78 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8308_3FB2270A | | | | | |
| 79 | HASH GROUP BY | | 4773K| 86M| 1524M| 1642K (1)| 00:01:05 |
| 80 | VIEW | | 56M| 1028M| | 1505K (1)| 00:00:59 |
| 81 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8307_3FB2270A | 56M| 20G| | 1505K (1)| 00:00:59 |
| 82 | SORT ORDER BY | | 1 | 687 | | 6480K (1)| 00:04:14 |
|* 83 | HASH JOIN OUTER | | 1 | 687 | | 6480K (1)| 00:04:14 |
| 84 | VIEW | | 1 | 556 | | 3050K (1)| 00:02:00 |
| 85 | COUNT | | | | | | |
| 86 | VIEW | | 1 | 504 | | 3050K (1)| 00:02:00 |
| 87 | WINDOW SORT | | 1 | 591 | | 3050K (1)| 00:02:00 |
|* 88 | HASH JOIN | | 1 | 591 | | 3050K (1)| 00:02:00 |
|* 89 | HASH JOIN | | 1 | 576 | 141M| 3043K (1)| 00:01:59 |
| 90 | VIEW | | 4773K| 86M| | 3351 (1)| 00:00:01 |
| 91 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8308_3FB2270A | 4773K| 86M| | 3351 (1)| 00:00:01 |
| 92 | VIEW | | 56M| 29G| | 1505K (1)| 00:00:59 |
| 93 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8307_3FB2270A | 56M| 20G| | 1505K (1)| 00:00:59 |
| 94 | VIEW | | 7160K| 102M| | 7399 (1)| 00:00:01 |
| 95 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8306_3FB2270A | 7160K| 102M| | 7399 (1)| 00:00:01 |
| 96 | VIEW | | 1 | 131 | | 3429K (1)| 00:02:14 |
| 97 | COUNT | | | | | | |
| 98 | VIEW | | 1 | 135 | | 3429K (1)| 00:02:14 |
| 99 | SORT ORDER BY | | 1 | 251 | | 3429K (1)| 00:02:14 |
|*100 | HASH JOIN | | 1 | 251 | | 3429K (1)| 00:02:14 |
|*101 | HASH JOIN | | 1 | 171 | | 1923K (1)| 00:01:16 |
|*102 | HASH JOIN | | 1 | 156 | 141M| 1916K (1)| 00:01:15 |
| 103 | VIEW | | 4773K| 86M| | 3351 (1)| 00:00:01 |
| 104 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8308_3FB2270A | 4773K| 86M| | 3351 (1)| 00:00:01 |
| 105 | VIEW | | 56M| 7417M| | 1505K (1)| 00:00:59 |
| 106 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8307_3FB2270A | 56M| 20G| | 1505K (1)| 00:00:59 |
| 107 | VIEW | | 7160K| 102M| | 7399 (1)| 00:00:01 |
| 108 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8306_3FB2270A | 7160K| 102M| | 7399 (1)| 00:00:01 |
| 109 | VIEW | | 56M| 4331M| | 1505K (1)| 00:00:59 |
| 110 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8307_3FB2270A | 56M| 20G| | 1505K (1)| 00:00:59 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(EM1.ovr_act_id IS NULL)
5 - access(EM1.cmpny_cd=EO.cmpny_cd AND EM1.crncygrp=EO.crncygrp AND EM1.ovr_rd_typ=EO.ovr_rd_typ AND
EM1.prd_cls_evt=ITEM_1 AND EM1.cpty_typ=EO.cpty_typ AND EM1.act_id=EO.act_id)
8 - filter(EM.ovr_act_id IS NULL)
9 - access(EM.cmpny_cd=EO.cmpny_cd AND EM.crncygrp=ITEM_4 AND EM.ovr_rd_typ=EO.ovr_rd_typ AND
EM.prd_cls_evt=EO.prd_cls_evt AND EM.cpty_typ=EO.cpty_typ AND EM.act_id=EO.act_id)
15 - access(PC.prd_cls=EO.prd_cls_evt)
filter(EO.ovr_rd_typ=CASE WHEN (PC.prd_cls IS NOT NULL) THEN 'P' ELSE 'P' END )
16 - access(CM.crncy=EO.crncygrp)
filter(EO.crncygrp<>CASE WHEN (CM.crncy IS NOT NULL) THEN '?' ELSE '?' END )
20 - filter((EO.crncygrp='?' OR CG.crncygrp=EO.crncygrp))
21 - access(GC.crncy=CG.crncy)
24 - filter((EO.prd_cls_evt=(-1) AND EO.ovr_rd_typ='P'))
27 - filter(((OV.act_id IS NOT NULL AND DEM.act_id IS NOT NULL) OR (OV.act_id IS NULL AND
DEM.act_id IS NULL)))
28 - access(DEM.act_id=OV.act_id AND DEM.evt_cd=AC.evt_cd AND DEM.act_bsc_cd=AC.act_bsc_cd)
30 - access(OV.cmpny_cd=AC.cmpny_cd AND AC.orgcrncy=OV.orgcrncy AND OV.prd_cls_evt=AC.evt_cd
AND OV.ovr_act_id=AC.act_id)
filter(CASE WHEN OV.ovr_act_id IS NOT NULL THEN AC.act_id ELSE AC.act_id END
=COALESCE(OV.act_id,AC.map_act_id))
31 - filter(OV.ovr_rd_typ='E')
33 - access(DEM.act_id=OV.act_id AND DEM.evt_cd=AC.evt_cd AND DEM.act_bsc_cd=AC.act_bsc_cd)
35 - access(OV.cmpny_cd=AC.cmpny_cd AND AC.orgcrncy=OV.orgcrncy AND
OV.prd_cls_evt=AC.prd_cls AND OV.ovr_act_id=AC.act_id)
36 - filter(OV.ovr_rd_typ='P')
41 - access(EM.act_bsc_cd=AC.act_bsc_cd AND EM.evt_cd=AC.evt_cd AND EM.act_id=AC.map_act_id
AND EM.bk_typ=AC.bk_typ)
46 - access(EM.act_bsc_cd=AC.act_bsc_cd AND EM.evt_cd=AC.evt_cd AND EM.act_id=AC.map_act_id
AND EM.bk_typ=AC.bk_typ)
filter(AC.evt_cd<>CASE WHEN (EM.bk_typ IS NOT NULL) THEN 53 ELSE 53 END )
48 - access(NM.jr_id=AC.jr_id)
54 - access(FS.jr_id=AC.jr_id)
56 - filter(MIN(AC.DISPLAYSEQNUM)>1)
58 - filter(NM.jr_id IS NULL)
59 - access(NM.jr_id=AC.jr_id)
64 - access(EM.act_bsc_cd=AC.act_bsc_cd AND EM.evt_cd=AC.evt_cd AND EM.act_id=AC.map_act_id
AND EM.bk_typ=AC.bk_typ AND AC.dc_indcr=CASE WHEN (EM.bk_typ IS NOT NULL) THEN ' ' ELSE ' ' END )
filter(AC.evt_cd<>CASE WHEN (EM.bk_typ IS NOT NULL) THEN 53 ELSE 53 END )
70 - filter(AR.DISPLAYSEQNUM=1)
73 - access(NM.jr_id=A.jr_id)
83 - access(AC.pstg_per=AD.pstg_per AND AC.rgn_indcr=AD.rgn_indcr AND AC.jr_id=AD.jr_id AND
AC.cr_id=AD.cr_id AND AC.tr_id=AD.tr_id AND AC.dl_id=AD.dl_id AND
AC.SESSIONKEY=AD.SESSIONKEY)
88 - access(DC.rgn_indcr=AR.rgn_indcr AND DC.pstg_per=AR.pstg_per AND DC.jr_id=AR.jr_id AND
DC.dc_indcr=AR.dc_indcr)
89 - access(AR.SEQNUM=M.SEQNUM AND AR.jr_id=M.jr_id)
100 - access(AR2.rgn_indcr=AR.rgn_indcr AND AR2.pstg_per=AR.pstg_per AND AR2.jr_id=AR.jr_id)
101 - access(DC.rgn_indcr=AR.rgn_indcr AND DC.pstg_per=AR.pstg_per AND DC.jr_id=AR.jr_id AND
DC.dc_indcr=AR.dc_indcr)
102 - access(AR.SEQNUM=M.SEQNUM AND AR.jr_id=M.jr_id)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - no_parallel
Note
-----
- Degree of Parallelism is 1 because of hint