208 error on IDS 7.30.UC2

Hey Guy's, I have IDS 7.30.UC2 on Solaris 2.5.1 I got one user query which was giving error 208[Memory Allocation Failed] while it was running. However i was able to run the query successfully only by putting {+ordered} clause. Following one can see the explain.out for both ways i ran the query First.log[without ordered clause]: ---------------------------------- QUERY: ------ select c.cur_mth, a.usg_dy_key, a.prc_dy_key, a.bld_usg_qty, a.nml_usg_qty, a.raw_usg_qty, d.mtl_id, d.mtl_dsc, e.sld_to_id, f.sls_org_id, g.ivc_nr, a.blg_doc_itm_nr, h.pyr_id, i.bll_to_id, j.loc_id, l.cty_nm, m.po_nr,n.cdi_prj_nr, o.cns_id, p.cns_co_id, q.cns_org_id --select count(*) from dm_f_dtl_c a, dw_bld_dy b, dw_cur_mth c, dm_mtl_c d, dw_sld_to_v e, dw_sls_org f, dw_ivc g, dw_pyr_v h, dw_bll_to_v i, dw_shp_to_v j, dw_cty l, dw_cus_po m, dw_cdi_prj n, dw_cns o, dw_cns_co p, dw_apl q where e.sld_to_id = 'EC501478' and a.bld_dy_key = b.bld_dy_key and b.fsc_mth_id = c.fsc_mth_id and a.mtl_key = d.mtl_key and a.sld_to_key = e.sld_to_key and a.sls_org_key = f.sls_org_key and a.ivc_key = g.ivc_key and a.pyr_key = h.pyr_key and a.bll_to_key = i.bll_to_key and a.shp_to_key = j.shp_to_key and j.cty_key = l.cty_key and a.po_key = m.po_key and a.cdi_prj_key = n.cdi_prj_key and a.cns_key = o.cns_key and o.cns_co_key = p.cns_co_key and a.apl_key = q.apl_key Estimated Cost: 4473954 Estimated # of Rows Returned: 65051 Maximum Threads: 44 1) aa426495.c: SEQUENTIAL SCAN 2) aa426495.f: SEQUENTIAL SCAN NESTED LOOP JOIN 3) aa426495.b: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.c.fsc_mth_id = aa426495.b.fsc_mth_id 4) stsbop.dw_cus: SEQUENTIAL SCAN Filters: stsbop.dw_cus.cus_id = 'EC501478' NESTED LOOP JOIN 5) aa426495.a: SEQUENTIAL SCAN (Parallel, fragments: ALL) DYNAMIC HASH JOIN Dynamic Hash Filters: (stsbop.dw_cus.cus_key = aa426495.a.sld_to_key AND (aa426495.f.sls_org_key = aa426495.a.sls_org_key AND aa426495.b.bld_dy_key = aa426495.a.bld_dy_key ) ) 6) aa426495.q: AUTOINDEX PATH (1) Index Keys: apl_key Lower Index Filter: aa426495.q.apl_key = aa426495.a.apl_key NESTED LOOP JOIN 7) aa426495.d: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.mtl_key = aa426495.d.mtl_key 8) stsbop.dw_cus: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.bll_to_key = stsbop.dw_cus.cus_key 9) stsbop.dw_cus: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.pyr_key = stsbop.dw_cus.cus_key 10) stsbop.dw_loc: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.shp_to_key = stsbop.dw_loc.loc_key 11) aa426495.l: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: stsbop.dw_loc.cty_key = aa426495.l.cty_key 12) aa426495.g: INDEX PATH (1) Index Keys: ivc_key (Parallel, fragments: ALL) Lower Index Filter: aa426495.g.ivc_key = aa426495.a.ivc_key NESTED LOOP JOIN 13) aa426495.m: INDEX PATH (1) Index Keys: po_key (Parallel, fragments: ALL) Lower Index Filter: aa426495.m.po_key = aa426495.a.po_key NESTED LOOP JOIN 14) aa426495.n: INDEX PATH (1) Index Keys: cdi_prj_key (Parallel, fragments: ALL) Lower Index Filter: aa426495.n.cdi_prj_key = aa426495.a.cdi_prj_key NESTED LOOP JOIN 15) aa426495.o: INDEX PATH (1) Index Keys: cns_key (Parallel, fragments: ALL) Lower Index Filter: aa426495.o.cns_key = aa426495.a.cns_key NESTED LOOP JOIN 16) aa426495.p: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.o.cns_co_key = aa426495.p.cns_co_key second.log[with ordered clause]: -------------------------------- QUERY: ------ select {+ORDERED} c.cur_mth, a.usg_dy_key, a.prc_dy_key, a.bld_usg_qty, a.nml_usg_qty, a.raw_usg_qty, d.mtl_id, d.mtl_dsc, e.sld_to_id, f.sls_org_id, g.ivc_nr, a.blg_doc_itm_nr, h.pyr_id, i.bll_to_id, j.loc_id, l.cty_nm, m.po_nr, n.cdi_prj_nr, o.cns_id, p.cns_co_id, q.cns_org_id from dw_sld_to_v e, dm_f_dtl_c a, dw_bld_dy b, dw_cur_mth c, dm_mtl_c d, dw_sls_org f, dw_ivc g, dw_pyr_v h, dw_bll_to_v i, dw_shp_to_v j, dw_cty l, dw_cus_po m, dw_cdi_prj n, dw_cns o, dw_cns_co p, dw_apl q where e.sld_to_id = 'EC501478' and a.bld_dy_key = b.bld_dy_key and b.fsc_mth_id = c.fsc_mth_id and a.mtl_key = d.mtl_key and a.sld_to_key = e.sld_to_key and a.sls_org_key = f.sls_org_key and a.ivc_key = g.ivc_key and a.pyr_key = h.pyr_key and a.bll_to_key = i.bll_to_key and a.shp_to_key = j.shp_to_key and j.cty_key = l.cty_key and a.po_key = m.po_key and a.cdi_prj_key = n.cdi_prj_key and a.cns_key = o.cns_key and o.cns_co_key = p.cns_co_key and a.apl_key = q.apl_key DIRECTIVES FOLLOWED:ORDERED DIRECTIVES NOT FOLLOWED: Estimated Cost: 3838975 Estimated # of Rows Returned: 54974 Maximum Threads: 45 1) stsbop.dw_cus: SEQUENTIAL SCAN Filters: stsbop.dw_cus.cus_id = 'EC501478' 2) aa426495.a: SEQUENTIAL SCAN (Parallel, fragments: ALL) DYNAMIC HASH JOIN Dynamic Hash Filters: stsbop.dw_cus.cus_key = aa426495.a.sld_to_key 3) aa426495.b: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.bld_dy_key = aa426495.b.bld_dy_key 4) aa426495.c: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.b.fsc_mth_id = aa426495.c.fsc_mth_id 5) aa426495.d: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.mtl_key = aa426495.d.mtl_key 6) aa426495.f: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.sls_org_key = aa426495.f.sls_org_key 7) aa426495.g: INDEX PATH (1) Index Keys: ivc_key (Parallel, fragments: ALL) Lower Index Filter: aa426495.g.ivc_key = aa426495.a.ivc_key NESTED LOOP JOIN 8) stsbop.dw_cus: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.pyr_key = stsbop.dw_cus.cus_key 9) stsbop.dw_cus: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.bll_to_key = stsbop.dw_cus.cus_key 10) stsbop.dw_loc: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: aa426495.a.shp_to_key = stsbop.dw_loc.loc_key 11) aa426495.l: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: stsbop.dw_loc.cty_key = aa426495.l.cty_key 12) aa426495.m: INDEX PATH (1) Index Keys: po_key (Parallel, fragments: ALL) Lower Index Filter: aa426495.m.po_key = aa426495.a.po_key NESTED LOOP JOIN 13) aa426495.n: INDEX PATH (1) Index Keys: cdi_prj_key (Parallel, fragments: ALL) Lower Index Filter: aa426495.n.cdi_prj_key = aa426495.a.cdi_prj_key NESTED LOOP JOIN 14) aa426495.o: INDEX PATH (1) Index Keys: cns_k

sanjeev sagar wrote: > > Hey Guy's, I have IDS 7.30.UC2 on Solaris 2.5.1 > > I got one user query which was giving error 208[Memory Allocation > Failed] while it was running. > > However i was able to run the query successfully only by putting > {+ordered} clause. Following one can see the explain.out for both ways > i ran the query > [SNIP] > 3. If i change OPTCOMPIND=0, will it help? It may. SET OPTIMIZATION LOW may help also. With a 16 table join there are 16! possible query paths (trillions) that the engine has to calculate the cost of. SET OPTIMIZATION LOW reduces that number to SUM(2..16) or 135 possible query paths. > 4. Is there any parameter like esql-c, FET_BUFF_SIZE, for dbaccess > only. There is a corresponding environment variable, FET_BUF_SIZE which corresponds to the ESQL/C global variable FetBufSize, which works for any program, including dbaccess and 4gl executables, that use the ESQL/C libraries. FET_BUF_SIZE=32767 Be aware that what is changed it the size of the cursor buffer used for both FETCH and inserts so if you use INSERT CURSORS and do not manually flush the cursor, depending on the buffer filling to force a flush, you will have more unflushed rows at any given time. Art S. Kagel