cancel
Showing results for 
Search instead for 
Did you mean: 

Simple Query with DBLink - Performance Question

Adventurer

Simple Query with DBLink - Performance Question

Hello: We have a query that includes a forign link to an Oracle database.  The query takes 30 seconds to run.  The explain plan shows that it's doing a sequential scan of the subquery for every record.

 

SELECT * FROM ECAPS.R_PROG@ECAPSPR.WORLD WHERE PROG_CD IN (SELECT PROG_CD FROM DPW.CCRFS_REQ);

But if we run it like so with the values hard coded, then it finishes in a split second.

 

SELECT * FROM ECAPS.R_PROG@ECAPSPR.WORLD WHERE PROG_CD IN ('J1000', 'J2000', 'J3000');

The problem then is the values are not dynamic.

One solution is to create a function that builds the query., like so... but it may not be the most elegant solution and it could theorically hit a value limit.

cachearray:= ARRAY(SELECT PROG_CD FROM DPW.CCRFS_REQ);
sql_statement := 'SELECT A.PROG_NM FROM ECAPS.R_PROG@ECAPSPR.WORLD A WHERE PROG_CD IN (''' ||array_to_string(cachearray, ''',''')||''')';
return query execute(sql_statement);

Does EDB have a way to impose HINTS or any other kind of optimization in order to improve the original query instead of using a function?

 

Thank you in advance!

 

8 REPLIES 8
EDB Team Member

Re: Simple Query with DBLink - Performance Question

Hi seandon4,

 

Thank you for raising the concerns with us.

 

To proceed further could you please confirm on below points.

 

1. Query execution is on Partition Table or Plain Table?

2. Could you please share the Explain Plan for the reported query. (i.e with and without hardcoded values)

Adventurer

Re: Simple Query with DBLink - Performance Question

Plain Table, not Partitioned

 

Photos Attached

 

8-7-2019 7-53-12 AM.pngDynamic

AND

 

8-7-2019 7-55-51 AM.pngHardcoded

EDB Team Member

Re: Simple Query with DBLink - Performance Question

Hi seandon4,

 

Could you please confirm the following things

 

1. Query execution is on Partition Table or Plain Table?

 

2. Could you please share the Explain Plan for the reported query. (i.e with and without hardcoded values)

Adventurer

Re: Simple Query with DBLink - Performance Question

Please see yesterday's reply.

EDB Team Member

Re: Simple Query with DBLink - Performance Question

Hi seandon4,

 

As you have provided the execution flow, but we are looking for the execution plan for this particular query. So we request you to please provide it.

Adventurer

Re: Simple Query with DBLink - Performance Question

How about...

 

EXPLAIN SELECT * FROM ECAPS.R_PROG@ECAPSPR.WORLD WHERE PROG_CD IN (SELECT PROG_CD FROM DPW.CCRFS_REQ);
"Hash Semi Join  (cost=16.07..26.89 rows=50 width=2626)"
"  Hash Cond: ((_dblink_r_prog_1.prog_cd)::text = (ccrfs_req.prog_cd)::text)"
"  ->  Foreign Scan on _dblink_r_prog_1  (cost=15.00..25.00 rows=100 width=2626)"
"        Remote Query: SELECT upload_date, dept_cd, prog_cd, mjr_prog_cd, prog_nm, prog_sh_nm, act_fl, efbgn_dt, efend_dt, alw_bud_fl, reim_elg_sta, recls_excl_fl, cnty_cd, mpst_strt, mpst_end, mpst_len, stn, ent, area, work_dsrc, secon_road_pln_cd, bldg_no, prcl_no, cordr, brd_ent, lgslv_ent, indn_rsrv, hwy_funcl_cls_cd, prog_dsrc, cntac_cd, pcls_cd, pcat_cd, pgrp_cd, ptyp_cd, ams_row_vers_no, susp_sta, proj_agmt_no, prog_nm_up, bill_agree_dt, fa_cnstrn_prog_lvl, sacct_cd, site_loc_cd, fed_aid_proj_num, tbl_last_dt, brd_aprv_dt, in_svc_dt, no_of_ext, ext_dt, aud_desc, last_aud_dt, draft_rpt_dt, rpt_issd_dt, plnd_strt_dt, plnd_end_dt, actu_strt_dt, actu_end_dt, cost_typ, bill_gp, bill_typ_cd, empl_ben_multi_typ, plan_strt_dt, plan_end_dt, prog_ind, drwdwn_grp, drwdwn_dept_cd, drwdwn_unit_cd, grant_id, grant_nm, prog_sta_cd FROM ecaps.r_prog"
"  ->  Hash  (cost=1.03..1.03 rows=3 width=38)"
"        ->  Seq Scan on ccrfs_req  (cost=0.00..1.03 rows=3 width=38)"

VS

EXPLAIN SELECT * FROM ECAPS.R_PROG@ECAPSPR.WORLD WHERE PROG_CD IN ('J53843B','J53843B','J53843B');
"Foreign Scan on _dblink_r_prog_1  (cost=15.00..25.00 rows=100 width=2626)"
"  Remote Query: SELECT upload_date, dept_cd, prog_cd, mjr_prog_cd, prog_nm, prog_sh_nm, act_fl, efbgn_dt, efend_dt, alw_bud_fl, reim_elg_sta, recls_excl_fl, cnty_cd, mpst_strt, mpst_end, mpst_len, stn, ent, area, work_dsrc, secon_road_pln_cd, bldg_no, prcl_no, cordr, brd_ent, lgslv_ent, indn_rsrv, hwy_funcl_cls_cd, prog_dsrc, cntac_cd, pcls_cd, pcat_cd, pgrp_cd, ptyp_cd, ams_row_vers_no, susp_sta, proj_agmt_no, prog_nm_up, bill_agree_dt, fa_cnstrn_prog_lvl, sacct_cd, site_loc_cd, fed_aid_proj_num, tbl_last_dt, brd_aprv_dt, in_svc_dt, no_of_ext, ext_dt, aud_desc, last_aud_dt, draft_rpt_dt, rpt_issd_dt, plnd_strt_dt, plnd_end_dt, actu_strt_dt, actu_end_dt, cost_typ, bill_gp, bill_typ_cd, empl_ben_multi_typ, plan_strt_dt, plan_end_dt, prog_ind, drwdwn_grp, drwdwn_dept_cd, drwdwn_unit_cd, grant_id, grant_nm, prog_sta_cd FROM ecaps.r_prog WHERE ((prog_cd) IN ('J53843B', 'J53843B', 'J53843B'))"

Thanks!

 

Adventurer

Re: Simple Query with DBLink - Performance Question

Posted additional details. Thank you very much.

EDB Team Member

Re: Simple Query with DBLink - Performance Question

Hi Sean,

 

You can refer the below document link for HINTS options available in EPAS.

 

https://www.enterprisedb.com/edb-docs/d/edbplus/user-guides/edbplus-users-guide/36/toc.html