Issue with remote joins (over db link)?
Hi All -
We are trying to understand why a report is taking a long time. In this case, it is taking about 15 minute sto complete. We were able to pull the execution plan (with estimated and actual details). We are trying to interpret the results, but are not completely sure about.
Our guess is that it is joining some remote tables that is taking all of the time. Possibly, some issues with the partitions? Please let us know if anyone can help identify here this one is "going off the rails" ?.
Thanks in advance!! Version = 12.2.0.1
SQL_ID b5wp1dgqqny3p, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ glb.ledger_id , glb.period_name , glb.currency_code , gcc.segment1 , gcc.segment2 , gcc.segment3 , gcc.segment4 , (nvl(glb.begin_balance_dr, 0) - nvl(glb.begin_balance_cr, 0) + nvl(glb.period_net_dr, 0) - nvl(glb.period_net_cr, 0)) ebs_ytd_amount, (nvl(glb.begin_balance_dr_beq,0)- nvl(glb.begin_balance_cr_beq,0)+ nvl(glb.period_net_dr_beq,0)-nvl(glb.period_net_cr_beq,0)) ebs_converted_amount ,(nvl(glb.period_net_dr,0)- nvl(glb.period_net_cr,0)) ebs_ptd_amount ,bf.ytd_balance ofdw_ytd_balance ,bf.ptd_activity ofdw_ptd_activity ,bf.converted_amount ofdw_converted_amount ,((nvl(glb.begin_balance_dr, 0) - nvl(glb.begin_balance_cr, 0) + nvl(glb.period_net_dr, 0) - nvl(glb.period_net_cr, 0)) - bf.ytd_balance) ytd_balance_variance ,( (nvl(glb.begin_balance_dr_beq,0)- nvl( Plan hash value: 2616286770 ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 146K(100)| 0 |00:02:34.46 | 44899 | | | | | 1 | CONCATENATION | | 1 | | | | | 0 |00:02:34.46 | 44899 | | | | |* 2 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | | | |* 3 | HASH JOIN | | 0 | 15172 | 3155K| 2848K| 72781 (4)| 0 |00:00:00.01 | 0 | 5077K| 2057K| | | 4 | REMOTE | | 0 | 13319 | 1079K| | 3119 (24)| 0 |00:00:00.01 | 0 | | | | | 5 | PARTITION LIST SINGLE| | 0 | 3603K| 113M| | 11426 (11)| 0 |00:00:00.01 | 0 | | | | | 6 | PARTITION LIST ALL | | 0
0