Fw: Query troubles
A DBA on IDS 11.70.FC3 (AIX 6.1) reported that a weekly 11-table reporting query against a 67M-row, week-fragmented fact table ran in ~4 minutes for fiscal weeks 1-37 but switched to a much worse plan and took ~20 minutes from week 38 on, repeating in earlier years too. Art Kagel noted the SET EXPLAIN row estimates were wildly off (66M estimated vs ~755K actual) and pointed to stale or too-coarse distributions, suggesting HIGH/better resolution stats and UPDATE STATISTICS with FORCE, plus SET OPTIMIZATION LOW for such a many-table join to cut optimizer time. The poster said various stats combinations made no difference; no resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.70.FC3XC Aix 6.1 Before I contact IBM to see if I'm running into some defect...thought I'd send this to the list. Couple of additional items, efin_wk_line_cls is a fragmented table. It has 40 fragments. Each fragment contains 4 weeks of data. Statistics haven't been updated with the new fragment level stuff... Weeks 37 - 40 are in the same fragment, and week 37 works as expected, and everything after week 37 is slow. If we do a different year, IE. last year the same behavior occurs...I am totally at a loss with this... Thanks ... Peter Logan Senior Database Administrator Phone: 616/878-8309 ----- Forwarded by Peter Logan/Corporate/Spartan on 03/13/2012 10:32 AM ----- From: Bruce Farwell/OTI/Spartan To: Peter Logan/Corporate/Spartan@SpartanStore Cc: Steve Baar/Corporate/Spartan@SpartanStore Date: 03/12/2012 11:02 AM Subject: Query troubles Peter, I have a SQL query that is causing me problems in EIS. The calculation is for year to date sales, which is done with a transformation table. I give a week_id to the query and it uses the transformation table (week_to_year_lkp) to determine sales for all the weeks from the start of the year. The problem is that from weeks 1 to 37 the query runs in about 4 minutes, but from week 38 on the query plan changes and it takes approx 20 minutes. This is slowing down report execution for the users. Can you take a look and see what can be done to avoid this problem? -Bruce The two query explains are below: Query 1 is week 37 and it run in about 4 minutes: QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:47:32) ------ select a18.mdse_grp_key mdse_grp_key, a12.fiscal_week_id fiscal_week_id, a13.catgy_manager_key catgy_manager_key, (sum(a11.total_sales_amt) * 0.001) WJXBFS1, (sum(a11.ext_profit_amt) * 0.001) WJXBFS2, sum(a11.total_sales_amt) WJXBFS3, sum(a11.ext_profit_amt) WJXBFS4 from efin_wk_line_cls a11, week_to_year_lkp a12, mdse_class_manager a13, line a14, chain a15, channel a16, mdse_class a17, mdse_category a18, mdse_group a19, department a110, business_dept a111 where a11.fiscal_week_id = a12.fiscal_wty_id and a11.mdse_class_key = a13.mdse_class_key and a11.sales_line_id = a14.sales_line_id and a14.sales_chain_id = a15.sales_chain_id and a15.sales_channel_id = a16.sales_channel_id and a11.mdse_class_key = a17.mdse_class_key and a17.mdse_catgy_key = a18.mdse_catgy_key and a18.mdse_grp_key = a19.mdse_grp_key and a19.dept_key = a110.dept_key and a110.bus_dept_key = a111.bus_dept_key and (a16.enterprise_id in (18) and a13.catgy_manager_key in (70) and a110.dept_grp_key not in (525, 9) and a14.format_type_id in ('SUPERMKT ') and a12.fiscal_week_id in (201237) and a111.dept_grp_type_key in (1)) group by a18.mdse_grp_key, a12.fiscal_week_id, a13.catgy_manager_key into temp ZZT6JQNPTNRMD003 with no log Estimated Cost: 220818 Estimated # of Rows Returned: 1499 Maximum Threads: 25 Temporary Files Required For: Group By 1) whmgr.a16: INDEX PATH (1) Index Name: whmgr.channel_i2 Index Keys: enterprise_id (Parallel, fragments: ALL) Lower Index Filter: whmgr.a16.enterprise_id = 18 2) whmgr.a15: INDEX PATH (1) Index Name: whmgr.chain_i2 Index Keys: sales_channel_id (Parallel, fragments: ALL) Lower Index Filter: whmgr.a15.sales_channel_id = whmgr.a16.sales_channel_id NESTED LOOP JOIN 3) whmgr.a14: INDEX PATH Filters: whmgr.a14.format_type_id = 'SUPERMKT ' (1) Index Name: whmgr.line_i2 Index Keys: sales_chain_id (Parallel, fragments: ALL) Lower Index Filter: whmgr.a14.sales_chain_id = whmgr.a15.sales_chain_id NESTED LOOP JOIN 4) whmgr.a111: SEQUENTIAL SCAN Filters: whmgr.a111.dept_grp_type_key = 1 NESTED LOOP JOIN 5) whmgr.a12: SEQUENTIAL SCAN Filters: whmgr.a12.fiscal_week_id = 201237 NESTED LOOP JOIN 6) whmgr.a110: SEQUENTIAL SCAN Filters: Table Scan Filters: whmgr.a110.dept_grp_key NOT IN (525 , 9 ) DYNAMIC HASH JOIN Dynamic Hash Filters: whmgr.a110.bus_dept_key = whmgr.a111.bus_dept_key 7) whmgr.a19: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: whmgr.a19.dept_key = whmgr.a110.dept_key 8) whmgr.a18: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: whmgr.a18.mdse_grp_key = whmgr.a19.mdse_grp_key 9) whmgr.a17: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: whmgr.a17.mdse_catgy_key = whmgr.a18.mdse_catgy_key 10) whmgr.a13: INDEX PATH (1) Index Name: whmgr.mdse_class_mgr_i1 Index Keys: catgy_manager_key (Parallel, fragments: ALL) Lower Index Filter: whmgr.a13.catgy_manager_key = 70 DYNAMIC HASH JOIN Dynamic Hash Filters: whmgr.a13.mdse_class_key = whmgr.a17.mdse_class_key 11) whmgr.a11: INDEX PATH (1) Index Name: whmgr.efin_wk_ln_cls_i Index Keys: fiscal_week_id sales_line_id mdse_class_key discount_type_cd (Parallel, fragments: ALL) Lower Index Filter: ((whmgr.a11.mdse_class_key = whmgr.a13.mdse_class_key AND whmgr.a11.fiscal_week_id = whmgr.a12.fiscal_wty_id ) AND whmgr.a11.sales_line_id = whmgr.a14.sales_line_id ) NESTED LOOP JOIN Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 a16 t2 a15 t3 a14 t4 a111 t5 a12 t6 a110 t7 a19 t8 a18 t9 a17 t10 a13 t11 a11 t12 zzt6jqnptnrmd003 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 7 7 7 00:00.00 2 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t2 9 25 9 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 9 6 00:00.01 5 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t3 137 23 173 00:00.00 1 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 137 6 00:00.02 15 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t4 1507 11 3014 00:00.00 3 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 1507 63 00:00.01 30 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t5 55759 28 18849556 00:04.88 426 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 55759 1771 00:04.90 26987 type table rows_prod est_rows rows_scan time est_cost ----------------------------------------
What level of stats (MEDIUM, HIGH) do you have on the fragmentation column (week#?) and at what resolution/confidence/sampling? How many total rows in the table? According to sysdistrib when were stats last calculated on the table? I would initially suggest forcing stats updates, but if last year behaves the same way for the weeks in the fragment after the first that would point to something other than the engine ignoring the update stats commands. Is it just the fragment with weeks 37-40 each year or the 2nd-4th weeks in many fragments? I'm assuming fiscal years and retail weeks here. What calendar dates do weeks 37-4 correspond to this year? Could there be a natural data skew in one of the four weeks in this fragment that is confusing the optimizer? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 13, 2012 at 10:37 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > IDS 11.70.FC3XC > Aix 6.1 > > Before I contact IBM to see if I'm running into some defect...thought I'd > send this to the list. Couple of additional items, efin_wk_line_cls is a > fragmented table. It has 40 fragments. Each fragment contains 4 weeks of > data. Statistics haven't been updated with the new fragment level > stuff... Weeks 37 - 40 are in the same fragment, and week 37 works as > expected, and everything after week 37 is slow. If we do a different > year, IE. last year the same behavior occurs...I am totally at a loss with > this... > > Thanks ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > ----- Forwarded by Peter Logan/Corporate/Spartan on 03/13/2012 10:32 AM > ----- > > From: Bruce Farwell/OTI/Spartan > To: Peter Logan/Corporate/Spartan@SpartanStore > Cc: Steve Baar/Corporate/Spartan@SpartanStore > Date: 03/12/2012 11:02 AM > Subject: Query troubles > > Peter, I have a SQL query that is causing me problems in EIS. The > calculation is for year to date sales, which is done with a transformation > table. I give a week_id to the query and it uses the transformation table > (week_to_year_lkp) to determine sales for all the weeks from the start of > the year. > > The problem is that from weeks 1 to 37 the query runs in about 4 minutes, > but from week 38 on the query plan changes and it takes approx 20 minutes. > This is slowing down report execution for the users. > > Can you take a look and see what can be done to avoid this problem? > > -Bruce > > The two query explains are below: > > Query 1 is week 37 and it run in about 4 minutes: > > QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:47:32) > ------ > select a18.mdse_grp_key mdse_grp_key, > > a12.fiscal_week_id fiscal_week_id, > > a13.catgy_manager_key catgy_manager_key, > > (sum(a11.total_sales_amt) * 0.001) WJXBFS1, > > (sum(a11.ext_profit_amt) * 0.001) WJXBFS2, > > sum(a11.total_sales_amt) WJXBFS3, > > sum(a11.ext_profit_amt) WJXBFS4 > from efin_wk_line_cls a11, > > week_to_year_lkp a12, > > mdse_class_manager a13, > > line a14, > > chain a15, > > channel a16, > > mdse_class a17, > > mdse_category a18, > > mdse_group a19, > > department a110, > > business_dept a111 > where a11.fiscal_week_id = a12.fiscal_wty_id and > > a11.mdse_class_key = a13.mdse_class_key and > > a11.sales_line_id = a14.sales_line_id and > > a14.sales_chain_id = a15.sales_chain_id and > > a15.sales_channel_id = a16.sales_channel_id and > > a11.mdse_class_key = a17.mdse_class_key and > > a17.mdse_catgy_key = a18.mdse_catgy_key and > > a18.mdse_grp_key = a19.mdse_grp_key and > > a19.dept_key = a110.dept_key and > > a110.bus_dept_key = a111.bus_dept_key > > and (a16.enterprise_id in (18) > > and a13.catgy_manager_key in (70) > > and a110.dept_grp_key not in (525, 9) > > and a14.format_type_id in ('SUPERMKT ') > > and a12.fiscal_week_id in (201237) > > and a111.dept_grp_type_key in (1)) > group by a18.mdse_grp_key, > > a12.fiscal_week_id, > > a13.catgy_manager_key > into temp ZZT6JQNPTNRMD003 with no log > > Estimated Cost: 220818 > Estimated # of Rows Returned: 1499 > Maximum Threads: 25 > Temporary Files Required For: Group By > > 1) whmgr.a16: INDEX PATH > > (1) Index Name: whmgr.channel_i2 > > Index Keys: enterprise_id (Parallel, fragments: ALL) > > Lower Index Filter: whmgr.a16.enterprise_id = 18 > > 2) whmgr.a15: INDEX PATH > > (1) Index Name: whmgr.chain_i2 > > Index Keys: sales_channel_id (Parallel, fragments: ALL) > > Lower Index Filter: whmgr.a15.sales_channel_id = > whmgr.a16.sales_channel_id > NESTED LOOP JOIN > > 3) whmgr.a14: INDEX PATH > > Filters: whmgr.a14.format_type_id = 'SUPERMKT ' > > (1) Index Name: whmgr.line_i2 > > Index Keys: sales_chain_id (Parallel, fragments: ALL) > > Lower Index Filter: whmgr.a14.sales_chain_id = > whmgr.a15.sales_chain_id > NESTED LOOP JOIN > > 4) whmgr.a111: SEQUENTIAL SCAN > > Filters: whmgr.a111.dept_grp_type_key = 1 > NESTED LOOP JOIN > > 5) whmgr.a12: SEQUENTIAL SCAN > > Filters: whmgr.a12.fiscal_week_id = 201237 > NESTED LOOP JOIN > > 6) whmgr.a110: SEQUENTIAL SCAN > > Filters: > > Table Scan Filters: whmgr.a110.dept_grp_key NOT IN (525 , 9 ) > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: whmgr.a110.bus_dept_key = > whmgr.a111.bus_dept_key > > 7) whmgr.a19: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: whmgr.a19.dept_key = whmgr.a110.dept_key > > 8) whmgr.a18: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: whmgr.a18.mdse_grp_key = whmgr.a19.mdse_grp_key > > 9) whmgr.a17: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: whmgr.a17.mdse_catgy_key = > whmgr.a18.mdse_catgy_key > > 10) whmgr.a13: INDEX PATH > > (1) Index Name: whmgr.mdse_class_mgr_i1 > > Index Keys: catgy_manager_key (Parallel, fragments: ALL) > > Lower Index Filter: whmgr.a13.catgy_manager_key = 70 > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: whmgr.a13.mdse_class_key = > whmgr.a17.mdse_class_key > > 11) whmgr.a11: INDEX PATH > > (1) Index Name: whmgr.efin_wk_ln_cls_i > > Index Keys: fiscal_week_id sales_line_id mdse_class_key > discount_type_cd (Parallel, fragments: ALL) > > Lower Index Filter: ((whmgr.a11.mdse_class_key = > whmgr.a13.mdse_class_key AND whmgr.a11.fiscal_week_id = > whmgr.a12.fiscal_wty_id ) AND whmgr.a11.sales_line_id = > whmgr.a14.sales_line_id ) > N
OK, I've taken the time to at least scan the SET EXPLAIN output below. Most of the row estimates for over half of the tables in the query are WAY OFF including the efin_wk_line_cls table. The estimates for both versions of the query guess that the engine will return 66.6million rows when actually only about 755thousand rows are ultimately returned from that table. The estimates on the joins are similarly off which affects the order of processing the tables. I'm back to saying it sounds like the stats are stale or insufficiently detailed (MEDIUM sampling is too small or HIGH should be used instead or the resolution is too low - ie not enough bins). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 13, 2012 at 10:50 AM, Art Kagel <art.kagel@gmail.com> wrote: > What level of stats (MEDIUM, HIGH) do you have on the fragmentation column > (week#?) and at what resolution/confidence/sampling? How many total rows > in the table? According to sysdistrib when were stats last calculated on > the table? > > I would initially suggest forcing stats updates, but if last year behaves > the same way for the weeks in the fragment after the first that would point > to something other than the engine ignoring the update stats commands. Is > it just the fragment with weeks 37-40 each year or the 2nd-4th weeks in > many fragments? I'm assuming fiscal years and retail weeks here. What > calendar dates do weeks 37-4 correspond to this year? Could there be a > natural data skew in one of the four weeks in this fragment that is > confusing the optimizer? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > > > > On Tue, Mar 13, 2012 at 10:37 AM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > >> IDS 11.70.FC3XC >> Aix 6.1 >> >> Before I contact IBM to see if I'm running into some defect...thought I'd >> send this to the list. Couple of additional items, efin_wk_line_cls is a >> fragmented table. It has 40 fragments. Each fragment contains 4 weeks of >> data. Statistics haven't been updated with the new fragment level >> stuff... Weeks 37 - 40 are in the same fragment, and week 37 works as >> expected, and everything after week 37 is slow. If we do a different >> year, IE. last year the same behavior occurs...I am totally at a loss with >> this... >> >> Thanks ... >> >> Peter Logan >> Senior Database Administrator >> Phone: 616/878-8309 >> ----- Forwarded by Peter Logan/Corporate/Spartan on 03/13/2012 10:32 AM >> ----- >> >> From: Bruce Farwell/OTI/Spartan >> To: Peter Logan/Corporate/Spartan@SpartanStore >> Cc: Steve Baar/Corporate/Spartan@SpartanStore >> Date: 03/12/2012 11:02 AM >> Subject: Query troubles >> >> Peter, I have a SQL query that is causing me problems in EIS. The >> calculation is for year to date sales, which is done with a transformation >> table. I give a week_id to the query and it uses the transformation table >> (week_to_year_lkp) to determine sales for all the weeks from the start of >> the year. >> >> The problem is that from weeks 1 to 37 the query runs in about 4 minutes, >> but from week 38 on the query plan changes and it takes approx 20 minutes. >> This is slowing down report execution for the users. >> >> Can you take a look and see what can be done to avoid this problem? >> >> -Bruce >> >> The two query explains are below: >> >> Query 1 is week 37 and it run in about 4 minutes: >> >> QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:47:32) >> ------ >> select a18.mdse_grp_key mdse_grp_key, >> >> a12.fiscal_week_id fiscal_week_id, >> >> a13.catgy_manager_key catgy_manager_key, >> >> (sum(a11.total_sales_amt) * 0.001) WJXBFS1, >> >> (sum(a11.ext_profit_amt) * 0.001) WJXBFS2, >> >> sum(a11.total_sales_amt) WJXBFS3, >> >> sum(a11.ext_profit_amt) WJXBFS4 >> from efin_wk_line_cls a11, >> >> week_to_year_lkp a12, >> >> mdse_class_manager a13, >> >> line a14, >> >> chain a15, >> >> channel a16, >> >> mdse_class a17, >> >> mdse_category a18, >> >> mdse_group a19, >> >> department a110, >> >> business_dept a111 >> where a11.fiscal_week_id = a12.fiscal_wty_id and >> >> a11.mdse_class_key = a13.mdse_class_key and >> >> a11.sales_line_id = a14.sales_line_id and >> >> a14.sales_chain_id = a15.sales_chain_id and >> >> a15.sales_channel_id = a16.sales_channel_id and >> >> a11.mdse_class_key = a17.mdse_class_key and >> >> a17.mdse_catgy_key = a18.mdse_catgy_key and >> >> a18.mdse_grp_key = a19.mdse_grp_key and >> >> a19.dept_key = a110.dept_key and >> >> a110.bus_dept_key = a111.bus_dept_key >> >> and (a16.enterprise_id in (18) >> >> and a13.catgy_manager_key in (70) >> >> and a110.dept_grp_key not in (525, 9) >> >> and a14.format_type_id in ('SUPERMKT ') >> >> and a12.fiscal_week_id in (201237) >> >> and a111.dept_grp_type_key in (1)) >> group by a18.mdse_grp_key, >> >> a12.fiscal_week_id, >> >> a13.catgy_manager_key >> into temp ZZT6JQNPTNRMD003 with no log >> >> Estimated Cost: 220818 >> Estimated # of Rows Returned: 1499 >> Maximum Threads: 25 >> Temporary Files Required For: Group By >> >> 1) whmgr.a16: INDEX PATH >> >> (1) Index Name: whmgr.channel_i2 >> >> Index Keys: enterprise_id (Parallel, fragments: ALL) >> >> Lower Index Filter: whmgr.a16.enterprise_id = 18 >> >> 2) whmgr.a15: INDEX PATH >> >> (1) Index Name: whmgr.chain_i2 >> >> Index Keys: sales_channel_id (Parallel, fragments: ALL) >> >> Lower Index Filter: whmgr.a15.sales_channel_id = >> whmgr.a16.sales_channel_id >> NESTED LOOP JOIN >> >> 3) whmgr.a14: INDEX PATH >> >> Filters: whmgr.a14.format_type_id = 'SUPERMKT ' >> >> (1) Index Name: whmgr.line_i2 >> >> Index Keys: sales_chain_id (Parallel, fragments: ALL) >> >> Lower Index Filter: whmgr.a14.sales_chain_id = >> whmgr.a15.sales_chain_id >> NESTED LOOP JOIN >> >> 4) whmgr.a111: SEQUENTIAL SCAN >> >> Filters: whmgr.a111.dept_grp_type_key = 1 >> NESTED LOOP JOIN >> >> 5) whmgr.a12: SEQUENTIAL SCAN >> >> Filters: whmgr.a12.fisca
I have played with the stats trying different combinations ... and not seeing any difference. This particular report is run weekly, and started performing poorly with week 38. You are correct that these are weeks of our fiscal year...the week corresponds to 12/11/2011 - 12/17/2011. If I execute the sql using the week of 201138, it performs bad, but 201137 performs as expected... so it appears that the 38th. week of each year is where it begins to go bad... Total rows are 67M Each week has approx. 385000 rows...and the data goes back to week 200933. Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 03/13/2012 10:52 AM Subject: Re: Fw: Query troubles [26509] Sent by: ids-bounces@iiug.org What level of stats (MEDIUM, HIGH) do you have on the fragmentation column (week#?) and at what resolution/confidence/sampling? How many total rows in the table? According to sysdistrib when were stats last calculated on the table? I would initially suggest forcing stats updates, but if last year behaves the same way for the weeks in the fragment after the first that would point to something other than the engine ignoring the update stats commands. Is it just the fragment with weeks 37-40 each year or the 2nd-4th weeks in many fragments? I'm assuming fiscal years and retail weeks here. What calendar dates do weeks 37-4 correspond to this year? Could there be a natural data skew in one of the four weeks in this fragment that is confusing the optimizer? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 13, 2012 at 10:37 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > IDS 11.70.FC3XC > Aix 6.1 > > Before I contact IBM to see if I'm running into some defect...thought I'd > send this to the list. Couple of additional items, efin_wk_line_cls is a > fragmented table. It has 40 fragments. Each fragment contains 4 weeks of > data. Statistics haven't been updated with the new fragment level > stuff... Weeks 37 - 40 are in the same fragment, and week 37 works as > expected, and everything after week 37 is slow. If we do a different > year, IE. last year the same behavior occurs...I am totally at a loss with > this... > > Thanks ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > ----- Forwarded by Peter Logan/Corporate/Spartan on 03/13/2012 10:32 AM > ----- > > From: Bruce Farwell/OTI/Spartan > To: Peter Logan/Corporate/Spartan@SpartanStore > Cc: Steve Baar/Corporate/Spartan@SpartanStore > Date: 03/12/2012 11:02 AM > Subject: Query troubles > > Peter, I have a SQL query that is causing me problems in EIS. The > calculation is for year to date sales, which is done with a transformation > table. I give a week_id to the query and it uses the transformation table > (week_to_year_lkp) to determine sales for all the weeks from the start of > the year. > > The problem is that from weeks 1 to 37 the query runs in about 4 minutes, > but from week 38 on the query plan changes and it takes approx 20 minutes. > This is slowing down report execution for the users. > > Can you take a look and see what can be done to avoid this problem? > > -Bruce > > The two query explains are below: > > Query 1 is week 37 and it run in about 4 minutes: > > QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:47:32) > ------ > select a18.mdse_grp_key mdse_grp_key, > > a12.fiscal_week_id fiscal_week_id, > > a13.catgy_manager_key catgy_manager_key, > > (sum(a11.total_sales_amt) * 0.001) WJXBFS1, > > (sum(a11.ext_profit_amt) * 0.001) WJXBFS2, > > sum(a11.total_sales_amt) WJXBFS3, > > sum(a11.ext_profit_amt) WJXBFS4 > from efin_wk_line_cls a11, > > week_to_year_lkp a12, > > mdse_class_manager a13, > > line a14, > > chain a15, > > channel a16, > > mdse_class a17, > > mdse_category a18, > > mdse_group a19, > > department a110, > > business_dept a111 > where a11.fiscal_week_id = a12.fiscal_wty_id and > > a11.mdse_class_key = a13.mdse_class_key and > > a11.sales_line_id = a14.sales_line_id and > > a14.sales_chain_id = a15.sales_chain_id and > > a15.sales_channel_id = a16.sales_channel_id and > > a11.mdse_class_key = a17.mdse_class_key and > > a17.mdse_catgy_key = a18.mdse_catgy_key and > > a18.mdse_grp_key = a19.mdse_grp_key and > > a19.dept_key = a110.dept_key and > > a110.bus_dept_key = a111.bus_dept_key > > and (a16.enterprise_id in (18) > > and a13.catgy_manager_key in (70) > > and a110.dept_grp_key not in (525, 9) > > and a14.format_type_id in ('SUPERMKT ') > > and a12.fiscal_week_id in (201237) > > and a111.dept_grp_type_key in (1)) > group by a18.mdse_grp_key, > > a12.fiscal_week_id, > > a13.catgy_manager_key > into temp ZZT6JQNPTNRMD003 with no log > > Estimated Cost: 220818 > Estimated # of Rows Returned: 1499 > Maximum Threads: 25 > Temporary Files Required For: Group By > > 1) whmgr.a16: INDEX PATH > > (1) Index Name: whmgr.channel_i2 > > Index Keys: enterprise_id (Parallel, fragments: ALL) > > Lower Index Filter: whmgr.a16.enterprise_id = 18 > > 2) whmgr.a15: INDEX PATH > > (1) Index Name: whmgr.chain_i2 > > Index Keys: sales_channel_id (Parallel, fragments: ALL) > > Lower Index Filter: whmgr.a15.sales_channel_id = > whmgr.a16.sales_channel_id > NESTED LOOP JOIN > > 3) whmgr.a14: INDEX PATH > > Filters: whmgr.a14.format_type_id = 'SUPERMKT ' > > (1) Index Name: whmgr.line_i2 > > Index Keys: sales_chain_id (Parallel, fragments: ALL) > > Lower Index Filter: whmgr.a14.sales_chain_id = > whmgr.a15.sales_chain_id > NESTED LOOP JOIN > > 4) whmgr.a111: SEQUENTIAL SCAN > > Filters: whmgr.a111.dept_grp_type_key = 1 > NESTED LOOP JOIN > > 5) whmgr.a12: SEQUENTIAL SCAN > > Filters: whmgr.a12.fiscal_week_id = 201237 > NESTED LOOP JOIN > > 6) whmgr.a110: SEQUENTIAL SCAN > > Filters: > > Table Scan Filters: whmgr.a110.dept_grp_key NOT IN (525 , 9 ) > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: whmgr.a110.bus_dept_key = > whmgr.a111.bus_dept_key > > 7) whmgr.a19: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: whmgr.a19.dept_key = whmgr.a110.dept_key > > 8) whmgr.a18: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: whmgr.a18.mdse_grp_key = whmgr.a19.mdse_grp_key >@@NL@
Hmm, mid December. For most retailers I would suspect holiday volume, but for you pre-christmas volume is probably not much different from pre-superbowl, pre-thanksgiving, pre-4th-of-july, etc. One thing I do notice, and I know it's a separate issue, this query has 11 tables in it. You should be running a query that complex (over 5 or 6 tables) using SET OPTIMIZATION LOW; since the optimizer has to cost out almost 40million query plans with default HIGH optimization set but only 54 query plans have to be examined with LOW optimization set. Often the optimization time is longer than the difference between the run times of the query plans developed using HIGH versus LOW. Are you certain that the engine is actually performing the various update stats commands you have tried and that it is not ignoring them because the existing stats are not 'old' or 'stale' enough? Did you run with the FORCE option included? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 13, 2012 at 11:10 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > I have played with the stats trying different combinations ... and not > seeing any difference. This particular report is run weekly, and started > performing poorly with week 38. You are correct that these are weeks of > our fiscal year...the week corresponds to 12/11/2011 - 12/17/2011. > > If I execute the sql using the week of 201138, it performs bad, but 201137 > performs as expected... so it appears that the 38th. week of each year is > where it begins to go bad... > > Total rows are 67M > Each week has approx. 385000 rows...and the data goes back to week > 200933. > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org > Date: 03/13/2012 10:52 AM > Subject: Re: Fw: Query troubles [26509] > Sent by: ids-bounces@iiug.org > > What level of stats (MEDIUM, HIGH) do you have on the fragmentation column > > (week#?) and at what resolution/confidence/sampling? How many total rows > in the table? According to sysdistrib when were stats last calculated on > the table? > > I would initially suggest forcing stats updates, but if last year behaves > the same way for the weeks in the fragment after the first that would > point > to something other than the engine ignoring the update stats commands. Is > it just the fragment with weeks 37-40 each year or the 2nd-4th weeks in > many fragments? I'm assuming fiscal years and retail weeks here. What > calendar dates do weeks 37-4 correspond to this year? Could there be a > natural data skew in one of the four weeks in this fragment that is > confusing the optimizer? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated > nor > those of the entities themselves. > > On Tue, Mar 13, 2012 at 10:37 AM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > IDS 11.70.FC3XC > > Aix 6.1 > > > > Before I contact IBM to see if I'm running into some defect...thought > I'd > > send this to the list. Couple of additional items, efin_wk_line_cls is a > > > fragmented table. It has 40 fragments. Each fragment contains 4 weeks of > > > data. Statistics haven't been updated with the new fragment level > > stuff... Weeks 37 - 40 are in the same fragment, and week 37 works as > > expected, and everything after week 37 is slow. If we do a different > > year, IE. last year the same behavior occurs...I am totally at a loss > with > > this... > > > > Thanks ... > > > > Peter Logan > > Senior Database Administrator > > Phone: 616/878-8309 > > ----- Forwarded by Peter Logan/Corporate/Spartan on 03/13/2012 10:32 AM > > ----- > > > > From: Bruce Farwell/OTI/Spartan > > To: Peter Logan/Corporate/Spartan@SpartanStore > > Cc: Steve Baar/Corporate/Spartan@SpartanStore > > Date: 03/12/2012 11:02 AM > > Subject: Query troubles > > > > Peter, I have a SQL query that is causing me problems in EIS. The > > calculation is for year to date sales, which is done with a > transformation > > table. I give a week_id to the query and it uses the transformation > table > > (week_to_year_lkp) to determine sales for all the weeks from the start > of > > the year. > > > > The problem is that from weeks 1 to 37 the query runs in about 4 > minutes, > > but from week 38 on the query plan changes and it takes approx 20 > minutes. > > This is slowing down report execution for the users. > > > > Can you take a look and see what can be done to avoid this problem? > > > > -Bruce > > > > The two query explains are below: > > > > Query 1 is week 37 and it run in about 4 minutes: > > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:47:32) > > ------ > > select a18.mdse_grp_key mdse_grp_key, > > > > a12.fiscal_week_id fiscal_week_id, > > > > a13.catgy_manager_key catgy_manager_key, > > > > (sum(a11.total_sales_amt) * 0.001) WJXBFS1, > > > > (sum(a11.ext_profit_amt) * 0.001) WJXBFS2, > > > > sum(a11.total_sales_amt) WJXBFS3, > > > > sum(a11.ext_profit_amt) WJXBFS4 > > from efin_wk_line_cls a11, > > > > week_to_year_lkp a12, > > > > mdse_class_manager a13, > > > > line a14, > > > > chain a15, > > > > channel a16, > > > > mdse_class a17, > > > > mdse_category a18, > > > > mdse_group a19, > > > > department a110, > > > > business_dept a111 > > where a11.fiscal_week_id = a12.fiscal_wty_id and > > > > a11.mdse_class_key = a13.mdse_class_key and > > > > a11.sales_line_id = a14.sales_line_id and > > > > a14.sales_chain_id = a15.sales_chain_id and > > > > a15.sales_channel_id = a16.sales_channel_id and > > > > a11.mdse_class_key = a17.mdse_class_key and > > > > a17.mdse_catgy_key = a18.mdse_catgy_key and > > > > a18.mdse_grp_key = a19.mdse_grp_key and > > > > a19.dept_key = a110.dept_key and > > > > a110.bus_dept_key = a111.bus_dept_key > > > > and (a16.enterprise_id in (18) > > > > and a13.catgy_manager_key in (70) > > > > and a110.dept_grp_key not in (525, 9) > > > > and a14.format_type_id in ('SUPERMKT ') > > > > and a12.fiscal_week_id
The really strange thing is that once I add any sort of distributions on this table, the query goes out to lunch. The week that generally runs in 2 or 3 mins goes to about 37 mins.... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 03/13/2012 11:03 AM Subject: Re: Fw: Query troubles [26510] Sent by: ids-bounces@iiug.org OK, I've taken the time to at least scan the SET EXPLAIN output below. Most of the row estimates for over half of the tables in the query are WAY OFF including the efin_wk_line_cls table. The estimates for both versions of the query guess that the engine will return 66.6million rows when actually only about 755thousand rows are ultimately returned from that table. The estimates on the joins are similarly off which affects the order of processing the tables. I'm back to saying it sounds like the stats are stale or insufficiently detailed (MEDIUM sampling is too small or HIGH should be used instead or the resolution is too low - ie not enough bins). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 13, 2012 at 10:50 AM, Art Kagel <art.kagel@gmail.com> wrote: > What level of stats (MEDIUM, HIGH) do you have on the fragmentation column > (week#?) and at what resolution/confidence/sampling? How many total rows > in the table? According to sysdistrib when were stats last calculated on > the table? > > I would initially suggest forcing stats updates, but if last year behaves > the same way for the weeks in the fragment after the first that would point > to something other than the engine ignoring the update stats commands. Is > it just the fragment with weeks 37-40 each year or the 2nd-4th weeks in > many fragments? I'm assuming fiscal years and retail weeks here. What > calendar dates do weeks 37-4 correspond to this year? Could there be a > natural data skew in one of the four weeks in this fragment that is > confusing the optimizer? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > > > > On Tue, Mar 13, 2012 at 10:37 AM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > >> IDS 11.70.FC3XC >> Aix 6.1 >> >> Before I contact IBM to see if I'm running into some defect...thought I'd >> send this to the list. Couple of additional items, efin_wk_line_cls is a >> fragmented table. It has 40 fragments. Each fragment contains 4 weeks of >> data. Statistics haven't been updated with the new fragment level >> stuff... Weeks 37 - 40 are in the same fragment, and week 37 works as >> expected, and everything after week 37 is slow. If we do a different >> year, IE. last year the same behavior occurs...I am totally at a loss with >> this... >> >> Thanks ... >> >> Peter Logan >> Senior Database Administrator >> Phone: 616/878-8309 >> ----- Forwarded by Peter Logan/Corporate/Spartan on 03/13/2012 10:32 AM >> ----- >> >> From: Bruce Farwell/OTI/Spartan >> To: Peter Logan/Corporate/Spartan@SpartanStore >> Cc: Steve Baar/Corporate/Spartan@SpartanStore >> Date: 03/12/2012 11:02 AM >> Subject: Query troubles >> >> Peter, I have a SQL query that is causing me problems in EIS. The >> calculation is for year to date sales, which is done with a transformation >> table. I give a week_id to the query and it uses the transformation table >> (week_to_year_lkp) to determine sales for all the weeks from the start of >> the year. >> >> The problem is that from weeks 1 to 37 the query runs in about 4 minutes, >> but from week 38 on the query plan changes and it takes approx 20 minutes. >> This is slowing down report execution for the users. >> >> Can you take a look and see what can be done to avoid this problem? >> >> -Bruce >> >> The two query explains are below: >> >> Query 1 is week 37 and it run in about 4 minutes: >> >> QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:47:32) >> ------ >> select a18.mdse_grp_key mdse_grp_key, >> >> a12.fiscal_week_id fiscal_week_id, >> >> a13.catgy_manager_key catgy_manager_key, >> >> (sum(a11.total_sales_amt) * 0.001) WJXBFS1, >> >> (sum(a11.ext_profit_amt) * 0.001) WJXBFS2, >> >> sum(a11.total_sales_amt) WJXBFS3, >> >> sum(a11.ext_profit_amt) WJXBFS4 >> from efin_wk_line_cls a11, >> >> week_to_year_lkp a12, >> >> mdse_class_manager a13, >> >> line a14, >> >> chain a15, >> >> channel a16, >> >> mdse_class a17, >> >> mdse_category a18, >> >> mdse_group a19, >> >> department a110, >> >> business_dept a111 >> where a11.fiscal_week_id = a12.fiscal_wty_id and >> >> a11.mdse_class_key = a13.mdse_class_key and >> >> a11.sales_line_id = a14.sales_line_id and >> >> a14.sales_chain_id = a15.sales_chain_id and >> >> a15.sales_channel_id = a16.sales_channel_id and >> >> a11.mdse_class_key = a17.mdse_class_key and >> >> a17.mdse_catgy_key = a18.mdse_catgy_key and >> >> a18.mdse_grp_key = a19.mdse_grp_key and >> >> a19.dept_key = a110.dept_key and >> >> a110.bus_dept_key = a111.bus_dept_key >> >> and (a16.enterprise_id in (18) >> >> and a13.catgy_manager_key in (70) >> >> and a110.dept_grp_key not in (525, 9) >> >> and a14.format_type_id in ('SUPERMKT ') >> >> and a12.fiscal_week_id in (201237) >> >> and a111.dept_grp_type_key in (1)) >> group by a18.mdse_grp_key, >> >> a12.fiscal_week_id, >> >> a13.catgy_manager_key >> into temp ZZT6JQNPTNRMD003 with no log >> >> Estimated Cost: 220818 >> Estimated # of Rows Returned: 1499 >> Maximum Threads: 25 >> Temporary Files Required For: Group By >> >> 1) whmgr.a16: INDEX PATH >> >> (1) Index Name: whmgr.channel_i2 >> >> Index Keys: enterprise_id (Parallel, fragments: ALL) >> >> Lower Index Filter: whmgr.a16.enterprise_id = 18 >> >> 2) whmgr.a15: INDEX PATH >> >> (1) Index Name: whmgr.chain_i2 >> >> Index Keys: sales_channel_id (Parallel, fragments: ALL) >> >> Lower Index Filter: whmgr.a15.sales_channel_id = >> whmgr.a16.sa
Art, setting the optimization to low makes the query run a little longer at week 37, but faster at week 38 ... So, it looks like it was the optimizer trying to figure out what to do .. thanks for the help ... Peter Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 03/13/2012 12:14 PM Subject: Re: Fw: Query troubles [26512] Sent by: ids-bounces@iiug.org Hmm, mid December. For most retailers I would suspect holiday volume, but for you pre-christmas volume is probably not much different from pre-superbowl, pre-thanksgiving, pre-4th-of-july, etc. One thing I do notice, and I know it's a separate issue, this query has 11 tables in it. You should be running a query that complex (over 5 or 6 tables) using SET OPTIMIZATION LOW; since the optimizer has to cost out almost 40million query plans with default HIGH optimization set but only 54 query plans have to be examined with LOW optimization set. Often the optimization time is longer than the difference between the run times of the query plans developed using HIGH versus LOW. Are you certain that the engine is actually performing the various update stats commands you have tried and that it is not ignoring them because the existing stats are not 'old' or 'stale' enough? Did you run with the FORCE option included? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 13, 2012 at 11:10 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > I have played with the stats trying different combinations ... and not > seeing any difference. This particular report is run weekly, and started > performing poorly with week 38. You are correct that these are weeks of > our fiscal year...the week corresponds to 12/11/2011 - 12/17/2011. > > If I execute the sql using the week of 201138, it performs bad, but 201137 > performs as expected... so it appears that the 38th. week of each year is > where it begins to go bad... > > Total rows are 67M > Each week has approx. 385000 rows...and the data goes back to week > 200933. > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org > Date: 03/13/2012 10:52 AM > Subject: Re: Fw: Query troubles [26509] > Sent by: ids-bounces@iiug.org > > What level of stats (MEDIUM, HIGH) do you have on the fragmentation column > > (week#?) and at what resolution/confidence/sampling? How many total rows > in the table? According to sysdistrib when were stats last calculated on > the table? > > I would initially suggest forcing stats updates, but if last year behaves > the same way for the weeks in the fragment after the first that would > point > to something other than the engine ignoring the update stats commands. Is > it just the fragment with weeks 37-40 each year or the 2nd-4th weeks in > many fragments? I'm assuming fiscal years and retail weeks here. What > calendar dates do weeks 37-4 correspond to this year? Could there be a > natural data skew in one of the four weeks in this fragment that is > confusing the optimizer? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated > nor > those of the entities themselves. > > On Tue, Mar 13, 2012 at 10:37 AM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > IDS 11.70.FC3XC > > Aix 6.1 > > > > Before I contact IBM to see if I'm running into some defect...thought > I'd > > send this to the list. Couple of additional items, efin_wk_line_cls is a > > > fragmented table. It has 40 fragments. Each fragment contains 4 weeks of > > > data. Statistics haven't been updated with the new fragment level > > stuff... Weeks 37 - 40 are in the same fragment, and week 37 works as > > expected, and everything after week 37 is slow. If we do a different > > year, IE. last year the same behavior occurs...I am totally at a loss > with > > this... > > > > Thanks ... > > > > Peter Logan > > Senior Database Administrator > > Phone: 616/878-8309 > > ----- Forwarded by Peter Logan/Corporate/Spartan on 03/13/2012 10:32 AM > > ----- > > > > From: Bruce Farwell/OTI/Spartan > > To: Peter Logan/Corporate/Spartan@SpartanStore > > Cc: Steve Baar/Corporate/Spartan@SpartanStore > > Date: 03/12/2012 11:02 AM > > Subject: Query troubles > > > > Peter, I have a SQL query that is causing me problems in EIS. The > > calculation is for year to date sales, which is done with a > transformation > > table. I give a week_id to the query and it uses the transformation > table > > (week_to_year_lkp) to determine sales for all the weeks from the start > of > > the year. > > > > The problem is that from weeks 1 to 37 the query runs in about 4 > minutes, > > but from week 38 on the query plan changes and it takes approx 20 > minutes. > > This is slowing down report execution for the users. > > > > Can you take a look and see what can be done to avoid this problem? > > > > -Bruce > > > > The two query explains are below: > > > > Query 1 is week 37 and it run in about 4 minutes: > > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:47:32) > > ------ > > select a18.mdse_grp_key mdse_grp_key, > > > > a12.fiscal_week_id fiscal_week_id, > > > > a13.catgy_manager_key catgy_manager_key, > > > > (sum(a11.total_sales_amt) * 0.001) WJXBFS1, > > > > (sum(a11.ext_profit_amt) * 0.001) WJXBFS2, > > > > sum(a11.total_sales_amt) WJXBFS3, > > > > sum(a11.ext_profit_amt) WJXBFS4 > > from efin_wk_line_cls a11, > > > > week_to_year_lkp a12, > > > > mdse_class_manager a13, > > > > line a14, > > > > chain a15, > > > > channel a16, > > > > mdse_class a17, > > > > mdse_category a18, > > > > mdse_group a19, > > > > department a110, > > > > business_dept a111 > > where a11.fiscal_week_id = a12.fiscal_wty_id and > > > > a11.mdse_class_key = a13.mdse_class_key and > > > > a11.sales_line_id = a14.sales_line_id and > > > > a14.
Odd indeed. I'd need significant hands-on there to figure this one out, and the solution may be a rewrite of the query. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 13, 2012 at 12:56 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > The really strange thing is that once I add any sort of distributions on > this table, the query goes out to lunch. The week that generally runs in > 2 or 3 mins goes to about 37 mins.... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org > Date: 03/13/2012 11:03 AM > Subject: Re: Fw: Query troubles [26510] > Sent by: ids-bounces@iiug.org > > OK, I've taken the time to at least scan the SET EXPLAIN output below. > Most of the row estimates for over half of the tables in the query are WAY > > OFF including the efin_wk_line_cls table. The estimates for both versions > of the query guess that the engine will return 66.6million rows when > actually only about 755thousand rows are ultimately returned from that > table. The estimates on the joins are similarly off which affects the > order of processing the tables. I'm back to saying it sounds like the > stats are stale or insufficiently detailed (MEDIUM sampling is too small > or > HIGH should be used instead or the resolution is too low - ie not enough > bins). > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated > nor > those of the entities themselves. > > On Tue, Mar 13, 2012 at 10:50 AM, Art Kagel <art.kagel@gmail.com> wrote: > > > What level of stats (MEDIUM, HIGH) do you have on the fragmentation > column > > (week#?) and at what resolution/confidence/sampling? How many total rows > > > in the table? According to sysdistrib when were stats last calculated on > > > the table? > > > > I would initially suggest forcing stats updates, but if last year > behaves > > the same way for the weeks in the fragment after the first that would > point > > to something other than the engine ignoring the update stats commands. > Is > > it just the fragment with weeks 37-40 each year or the 2nd-4th weeks in > > many fragments? I'm assuming fiscal years and retail weeks here. What > > calendar dates do weeks 37-4 correspond to this year? Could there be a > > natural data skew in one of the four weeks in this fragment that is > > confusing the optimizer? > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > > other organization with which I am associated either explicitly, > > implicitly, or by inference. Neither do those opinions reflect those of > > other individuals affiliated with any entity with which I am affiliated > nor > > those of the entities themselves. > > > > > > > > > > On Tue, Mar 13, 2012 at 10:37 AM, Peter_Logan@spartanstores.com < > > Peter_Logan@spartanstores.com> wrote: > > > >> IDS 11.70.FC3XC > >> Aix 6.1 > >> > >> Before I contact IBM to see if I'm running into some defect...thought > I'd > >> send this to the list. Couple of additional items, efin_wk_line_cls is > a > >> fragmented table. It has 40 fragments. Each fragment contains 4 weeks > of > >> data. Statistics haven't been updated with the new fragment level > >> stuff... Weeks 37 - 40 are in the same fragment, and week 37 works as > >> expected, and everything after week 37 is slow. If we do a different > >> year, IE. last year the same behavior occurs...I am totally at a loss > with > >> this... > >> > >> Thanks ... > >> > >> Peter Logan > >> Senior Database Administrator > >> Phone: 616/878-8309 > >> ----- Forwarded by Peter Logan/Corporate/Spartan on 03/13/2012 10:32 AM > > >> ----- > >> > >> From: Bruce Farwell/OTI/Spartan > >> To: Peter Logan/Corporate/Spartan@SpartanStore > >> Cc: Steve Baar/Corporate/Spartan@SpartanStore > >> Date: 03/12/2012 11:02 AM > >> Subject: Query troubles > >> > >> Peter, I have a SQL query that is causing me problems in EIS. The > >> calculation is for year to date sales, which is done with a > transformation > >> table. I give a week_id to the query and it uses the transformation > table > >> (week_to_year_lkp) to determine sales for all the weeks from the start > of > >> the year. > >> > >> The problem is that from weeks 1 to 37 the query runs in about 4 > minutes, > >> but from week 38 on the query plan changes and it takes approx 20 > minutes. > >> This is slowing down report execution for the users. > >> > >> Can you take a look and see what can be done to avoid this problem? > >> > >> -Bruce > >> > >> The two query explains are below: > >> > >> Query 1 is week 37 and it run in about 4 minutes: > >> > >> QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:47:32) > >> ------ > >> select a18.mdse_grp_key mdse_grp_key, > >> > >> a12.fiscal_week_id fiscal_week_id, > >> > >> a13.catgy_manager_key catgy_manager_key, > >> > >> (sum(a11.total_sales_amt) * 0.001) WJXBFS1, > >> > >> (sum(a11.ext_profit_amt) * 0.001) WJXBFS2, > >> > >> sum(a11.total_sales_amt) WJXBFS3, > >> > >> sum(a11.ext_profit_amt) WJXBFS4 > >> from efin_wk_line_cls a11, > >> > >> week_to_year_lkp a12, > >> > >> mdse_class_manager a13, > >> > >> line a14, > >> > >> chain a15, > >> > >> channel a16, > >> > >> mdse_class a17, > >> > >> mdse_category a18, > >> > >> mdse_group a19, > >> > >> department a110, > >> > >> business_dept a111 > >> where a11.fiscal_week_id = a12.fiscal_wty_id and > >> > >> a11.mdse_class_key = a13.mdse_class_key and > >> > >> a11.sales_line_id = a14.sales_line_id and > >> > >> a14.sales_chain_id = a15.sales_chain_id and > >> > >> a15.sales_channel_id = a16.sales_channel_id and > >> > >> a11.mdse_class_key = a17.mdse_class_key and > >> > >> a17.mdse_catgy_key = a18.mdse_catgy_key and > >> > >> a18.mdse_grp_key = a19.mdse_grp_key and