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
-------------------------------------------------------------------
scan t6 25 25 27 00:00.00 3
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 70966 2013 25 55759 0 00:01.16
27319
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t7 722 722 722 00:00.00 34
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 2336809 53840 722 70966 0 00:01.92
27854
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t8 3807 3807 3807 00:00.00 173
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 13209814 283888 3807 2336809 0 00:10.94
38624
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t9 8102 8102 8102 00:00.00 410
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 24848238 604167 8102 13209814 0 00:37.15
92809
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t10 270 270 270 00:00.01 13
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 1358492 20092 270 24848238 0 01:11.38
204318
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t11 755416 66768516 755416 06:00.20 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 755416 1347 08:06.32 218612
type rows_prod est_rows rows_cons time est_cost
------------------------------------------------------------
group 19 1499 755416 04:03.93 2207
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 19 1499 19 04:03.93
type table rows_ins time
-----------------------------------
insert t12 19 16:15.75
Query 2 is week 38 and it ran in approx 40 minutes:
QUERY: (OPTIMIZATION TIMESTAMP: 03-12-2012 09:55:55)
------
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 (201238)
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: 297043
Estimated # of Rows Returned: 925
Maximum Threads: 26
Temporary Files Required For: Group By
1) 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
2) whmgr.a17: INDEX PATH
(1) Index Name: whmgr. 314_609
Index Keys: mdse_class_key (Parallel, fragments: ALL)
Lower Index Filter: whmgr.a13.mdse_class_key =
whmgr.a17.mdse_class_key
NESTED LOOP JOIN
3) whmgr.a18: INDEX PATH
(1) Index Name: whmgr. 292_608
Index Keys: mdse_catgy_key (Parallel, fragments: ALL)
Lower Index Filter: whmgr.a17.mdse_catgy_key =
whmgr.a18.mdse_catgy_key
NESTED LOOP JOIN
4) whmgr.a19: INDEX PATH
(1) Index Name: whmgr. 291_611
Index Keys: mdse_grp_key (Parallel, fragments: ALL)
Lower Index Filter: whmgr.a18.mdse_grp_key =
whmgr.a19.mdse_grp_key
NESTED LOOP JOIN
5) whmgr.a110: INDEX PATH
Filters: whmgr.a110.dept_grp_key NOT IN (525 , 9 )
(1) Index Name: whmgr. 300_606
Index Keys: dept_key (Parallel, fragments: ALL)
Lower Index Filter: whmgr.a19.dept_key = whmgr.a110.dept_key
NESTED LOOP JOIN
6) whmgr.a111: SEQUENTIAL SCAN
Filters:
Table Scan Filters: whmgr.a111.dept_grp_type_key = 1
DYNAMIC HASH JOIN
Dynamic Hash Filters: whmgr.a110.bus_dept_key =
whmgr.a111.bus_dept_key
7) 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
NESTED LOOP JOIN
8) 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
9) whmgr.a14: SEQUENTIAL SCAN
Filters:
Table Scan Filters: whmgr.a14.format_type_id = 'SUPERMKT '
DYNAMIC HASH JOIN
Dynamic Hash Filters: whmgr.a14.sales_chain_id =
whmgr.a15.sales_chain_id
10) whmgr.a12: SEQUENTIAL SCAN
Filters: whmgr.a12.fiscal_week_id = 201238
NESTED LOOP JOIN
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 a13
t2 a17
t3 a18
t4 a19
t5 a110
t6 a111
t7 a16
t8 a15
t9 a14
t10 a12
t11 a11
t12 zzt6jqnptnrmd003
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 270 270 270 00:00.01 13
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 268 8102 268 00:00.17 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 268 270 00:00.21 374
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 268 3807 268 00:00.00 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 268 270 00:00.21 583
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t4 268 722 268 00:00.02 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 268 270 00:00.22 736
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t5 268 25 268 00:00.00 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 268 251 00:00.17 785
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t6 11 11 22 00:00.00 3
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 268 126 11 268 0 00:00.07 835
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t7 1876 7 1876 00:00.03 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 1876 878 00:00.17 987
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t8 2412 25 2412 00:00.04 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 2412 669 00:00.22 1132
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t9 137 23 230 00:00.00 14
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 36716 645 137 2412 0 00:00.15 1274
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t10 1395208 39 459243728 01:56.18 426
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 1395208 24945 01:56.51 273898
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t11 775830 66768516 775830 58:43.74 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 775830 1861 65:49.52 293661
type rows_prod est_rows rows_cons time est_cost
------------------------------------------------------------
group 19 925 775830 32:56.45 3383
type rows_prod est_rows rows_cons time
-------------------------------------------------
group 19 925 19 32:56.45
type table rows_ins time
-----------------------------------
insert t12 19 131:45.81
