Performance - Informix SE 7.22
As highlighted in my previous posting an upgrade from HP Ux 9.04 to
10.10 has created a 12X performance decrease in some queries run-time.
The system is spending the majority of its time spinning though temphash
overflow files created in DBTEMP. I believe it is the engines decision
to create these files that is the root cause of the problem.
Here is the explain from a sample query, as well as the main sales
structure
ORIGINAL QUERY - AFTER UPDATE STATISTICS HIGH on Head Columns
QUERY:
------
select sa.div_code, sh.sa_cust, sa.cust_num, sa.ship_num, it.sa_item
, sum(sa.sales_1), sum(sa.sales_2), sum(sa.sales_3), sum(sa.sales_4)
, sum(sa.sales_5), sum(sa.sales_6), sum(sa.sales_7), sum(sa.sales_8)
, sum(sa.sales_9), sum(sa.sales_10),sum(sa.sales_11), sum(sa.sales_12)
, sum(sa.sales_13)
, sum(sa.cost_1), sum(sa.cost_2), sum(sa.cost_3), sum(sa.cost_4)
, sum(sa.cost_5), sum(sa.cost_6), sum(sa.cost_7), sum(sa.cost_8)
, sum(sa.cost_9), sum(sa.cost_10), sum(sa.cost_11), sum(sa.cost_12)
, sum(sa.cost_13)
, sum(sa.ly_sales_1), sum(sa.ly_sales_2), sum(sa.ly_sales_3)
, sum(sa.ly_sales_4), sum(sa.ly_sales_5), sum(sa.ly_sales_6)
, sum(sa.ly_sales_7), sum(sa.ly_sales_8), sum(sa.ly_sales_9)
, sum(sa.ly_sales_10), sum(sa.ly_sales_11), sum(sa.ly_sales_12)
, sum(sa.ly_sales_13)
, sum(sa.ly_cost_1), sum(sa.ly_cost_2), sum(sa.ly_cost_3),
sum(sa.ly_cost_4)
, sum(sa.ly_cost_5), sum(sa.ly_cost_6), sum(sa.ly_cost_7),
sum(sa.ly_cost_8)
, sum(sa.ly_cost_9), sum(sa.ly_cost_10), sum(sa.ly_cost_11),
sum(sa.ly_cost_12)
, sum(sa.ly_cost_13)
from
sa1_his sa
,item it
,cust_shp sh
where
sa.cust_num = sh.cust_num
and sa.ship_num = sh.ship_num
and it.item_num = sa.item_num
group by 1,2,3,4,5
order by 1,2,3,4,5
Estimated Cost: 31180
Estimated # of Rows Returned: 3
Temporary Files Required For: Order By Group By
1) root.sh: SEQUENTIAL SCAN
2) root.sa: INDEX PATH
(1) Index Keys: cust_num ship_num
Lower Index Filter: (root.sa.ship_num = root.sh.ship_num AND
root.sa.cus
t_num = root.sh.cust_num )
3) root.it: INDEX PATH
(1) Index Keys: item_num
Lower Index Filter: root.it.item_num = root.sa.item_num
TABLE STRUCTURES
SA1_HIS - Main History Table
{ TABLE "root".sa1_his row size = 1728 number of columns = 240 index
size = 252 }
create table "root".sa1_his
(
div_code char(8),
item_num char(30),
cust_num char(10),
ship_num char(10),
slmn_num char(4),
whse_code char(2),
sales_1 decimal(12,6),
sales_2 decimal(12,6),
sales_3 decimal(12,6),
sales_4 decimal(12,6),
sales_5 decimal(12,6),
sales_6 decimal(12,6),
sales_7 decimal(12,6),
sales_8 decimal(12,6),
sales_9 decimal(12,6),
sales_10 decimal(12,6),
sales_11 decimal(12,6),
sales_12 decimal(12,6),
sales_13 decimal(12,6),
gross_sales_1 decimal(12,6),
gross_sales_2 decimal(12,6),
gross_sales_3 decimal(12,6),
gross_sales_4 decimal(12,6),
gross_sales_5 decimal(12,6),
gross_sales_6 decimal(12,6),
gross_sales_7 decimal(12,6),
gross_sales_8 decimal(12,6),
gross_sales_9 decimal(12,6),
gross_sales_10 decimal(12,6),
gross_sales_11 decimal(12,6),
gross_sales_12 decimal(12,6),
gross_sales_13 decimal(12,6),
credit_1 decimal(12,6),
credit_2 decimal(12,6),
credit_3 decimal(12,6),
credit_4 decimal(12,6),
credit_5 decimal(12,6),
credit_6 decimal(12,6),
credit_7 decimal(12,6),
credit_8 decimal(12,6),
credit_9 decimal(12,6),
credit_10 decimal(12,6),
credit_11 decimal(12,6),
credit_12 decimal(12,6),
credit_13 decimal(12,6),
add_exp_1 decimal(12,6),
add_exp_2 decimal(12,6),
add_exp_3 decimal(12,6),
add_exp_4 decimal(12,6),
add_exp_5 decimal(12,6),
add_exp_6 decimal(12,6),
add_exp_7 decimal(12,6),
add_exp_8 decimal(12,6),
add_exp_9 decimal(12,6),
add_exp_10 decimal(12,6),
add_exp_11 decimal(12,6),
add_exp_12 decimal(12,6),
add_exp_13 decimal(12,6),
wh_exp_1 decimal(12,6),
wh_exp_2 decimal(12,6),
wh_exp_3 decimal(12,6),
wh_exp_4 decimal(12,6),
wh_exp_5 decimal(12,6),
wh_exp_6 decimal(12,6),
wh_exp_7 decimal(12,6),
wh_exp_8 decimal(12,6),
wh_exp_9 decimal(12,6),
wh_exp_10 decimal(12,6),
wh_exp_11 decimal(12,6),
wh_exp_12 decimal(12,6),
wh_exp_13 decimal(12,6),
coop_exp_1 decimal(12,6),
coop_exp_2 decimal(12,6),
coop_exp_3 decimal(12,6),
coop_exp_4 decimal(12,6),
coop_exp_5 decimal(12,6),
coop_exp_6 decimal(12,6),
coop_exp_7 decimal(12,6),
coop_exp_8 decimal(12,6),
coop_exp_9 decimal(12,6),
coop_exp_10 decimal(12,6),
coop_exp_11 decimal(12,6),
coop_exp_12 decimal(12,6),
coop_exp_13 decimal(12,6),
other_exp_1 decimal(12,6),
other_exp_2 decimal(12,6),
other_exp_3 decimal(12,6),
other_exp_4 decimal(12,6),
other_exp_5 decimal(12,6),
other_exp_6 decimal(12,6),
other_exp_7 decimal(12,6),
other_exp_8 decimal(12,6),
other_exp_9 decimal(12,6),
other_exp_10 decimal(12,6),
other_exp_11 decimal(12,6),
other_exp_12 decimal(12,6),
other_exp_13 decimal(12,6),
cost_1 decimal(12,6),
cost_2 decimal(12,6),
cost_3 decimal(12,6),
cost_4 decimal(12,6),
cost_5 decimal(12,6),
cost_6 decimal(12,6),
cost_7 decimal(12,6),
cost_8 decimal(12,6),
cost_9 decimal(12,6),
cost_10 decimal(12,6),
cost_11 decimal(12,6),
cost_12 decimal(12,6),
cost_13 decimal(12,6),
qty_1 decimal(12,3),
qty_2 decimal(12,3),
qty_3 decimal(12,3),
qty_4 decimal(12,3),
qty_5 decimal(12,3),
qty_6 decimal(12,3),
qty_7 decimal(12,3),
qty_8 decimal(12,3),
qty_9 decimal(12,3),
qty_10 decimal(12,3),
qty_11 decimal(12,3),
qty_12 decimal(12,3),
qty_13 decimal(12,3),
ly_sales_1 decimal(12,6),
ly_sales_2 decimal(12,6),
ly_sales_3 decimal(12,6),
ly_sales_4 decimal(12,6),
ly_sales_5 decimal(12,6),
ly_sales_6 decimal(12,6),
ly_sales_7 decimal(12,6),
ly_sales_8 decimal(12,6),
ly_sales_9 decimal(12,6),
ly_sales_10 decimal(12,6),
ly_sales_11 decimal(12,6),
ly_sales_12 decimal(12,6),
ly_sales_13 decimal(12,6),
ly_gross_sales_1 decimal(12,6),
ly_gross_sales_2 decimal(12,6),
ly_gross_sales_3 decimal(12,6),
ly_gross_sales_4 decimal(12,6),
ly_gross_sales_5 decimal(12,6),
ly_gross_sales_6 decimal(12,6),
ly_gross_sales_7 decimal(12,6),
ly_gross_sales_8 decimal(12,6),
ly_gross_sales_9 decimal(12,6),
ly_gross_sales_10 decimal(12,6),
ly_gross_sales_11 decimal(12,6),
ly_gross_sales_12 decimal(12,6),
ly_gross_sales_13 decimal(12,6),
ly_credit_1 decimal(12,6),
ly_credit_2 decimal(12,6),
ly_credit_3 decimal(12,6),