DW query tuning - explain on
A user on IDS 10 (AIX) with a star-schema data warehouse found that a Business Objects-generated query joining a 211M-row fact table to several dimensions took 33 minutes, while a hand-rewritten version using IN (subquery) for the dnis/toll_type dimensions ran in 4 minutes; the BO SQL could not be changed. Respondents asked about up-to-date/detailed distributions and accurate row estimates, suspected hash joins starved of DS memory (MAXPDQPRIORITY 5), and suggested testing with PDQPRIORITY 100 and watching it in xtree. Art Kagel noted that explicit star-query expansion exists only in XPS, not IDS; Jack Parker added that the needed bitmap indexes aren't in v10. No fix or confirmed improvement is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello
We have an Informix 10FC4 database on AIX in a Data warehouse type set
up with a star type schema. We have queries coming in from a Business
Object server, what this means is that I cannot change/tune the sql
statement in question. We've had an IBM Informix employee review our
onconfig, and overall design, and we've followed his report, and
generally this DW works well for non-BO type queries. We want to see
if we can get the optimizer to recognize the following query as a star
query.
The table is partitioned by month, and takes 33 minues to run, a
modified (we can't do this though as it is a BO query) is provided
that runs in 4 minutes:
Any advice is appreciated. Thanks.
QUERY:
------
SELECT /*+EXPLAIN, AVOID_EXECUTE */
company_dim_a.company_number,
company_dim_a.company_name,
owner_dim_a.owner_number,
TRIM(owner_dim_a.owner_fname) || ' ' ||
TRIM(owner_dim_a.owner_lname),
start_date_dim_a.cal_year_month,
sum(conf_part_fact.duration)
FROM
dwadmin.company_dim company_dim_a,
dwadmin.owner_dim owner_dim_a,
dwadmin.date_dim start_date_dim_a,
dwadmin.conf_part_fact,
dwadmin.toll_type_dim toll_type_dim_a,
dwadmin.dnis_dim dnis_dim_a
WHERE company_dim_a.company_key=conf_part_fact.company_key
AND owner_dim_a.owner_key=conf_part_fact.owner_key
AND conf_part_fact.date_key=start_date_dim_a.date_key
AND conf_part_fact.dnis_key=dnis_dim_a.dnis_key
AND
toll_type_dim_a.toll_type_key=dwadmin.conf_part_fact.toll_type_key
AND start_date_dim_a.cal_year_month In ( '2007-10','2007-11' )
AND toll_type_dim_a.toll_type = 'ITFS '
AND dnis_dim_a.dnis = '8886410204'
GROUP BY 1, 2, 3, 4, 5
DIRECTIVES FOLLOWED:EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 29615820
Estimated # of Rows Returned: 1
Maximum Threads: 29
Temporary Files Required For: Group By
1) dwadmin.conf_part_fact: SEQUENTIAL SCAN (Parallel, fragments:
ALL)
2) dwadmin.toll_type_dim_a: INDEX PATH
Filters: dwadmin.toll_type_dim_a.toll_type = 'ITFS '
(1) Index Keys: toll_type_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.toll_type_dim_a.toll_type_key =
dwadmin.conf_part_fact.toll_type_key
NESTED LOOP JOIN
3) dwadmin.dnis_dim_a: INDEX PATH
(1) Index Keys: dnis (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.dnis_dim_a.dnis = '8886410204'
DYNAMIC HASH JOIN
Dynamic Hash Filters: dwadmin.conf_part_fact.dnis_key =
dwadmin.dnis_dim_a.dnis_key
4) dwadmin.start_date_dim_a: INDEX PATH
(1) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-10'
(2) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-11'
DYNAMIC HASH JOIN
Dynamic Hash Filters: dwadmin.conf_part_fact.date_key =
dwadmin.start_date_dim_a.date_key
5) dwadmin.company_dim_a: INDEX PATH
(1) Index Keys: company_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.company_dim_a.company_key =
dwadmin.conf_part_fact.company_key
NESTED LOOP JOIN
6) dwadmin.owner_dim_a: INDEX PATH
(1) Index Keys: owner_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.owner_dim_a.owner_key =
dwadmin.conf_part_fact.owner_key
NESTED LOOP JOIN
The next query is similar one, but modified and it takes 4 min to run.
However this is not typical of how query would be written.
QUERY:
------
SELECT /*+EXPLAIN, AVOID_EXECUTE */
company_dim_a.company_number,
company_dim_a.company_name,
owner_dim_a.owner_number,
TRIM(owner_dim_a.owner_fname) || ' ' ||
TRIM(owner_dim_a.owner_lname),
start_date_dim_a.cal_year_month,
sum(conf_part_fact.duration)
FROM
conf_part_fact,
company_dim company_dim_a,
owner_dim owner_dim_a,
date_dim start_date_dim_a
WHERE conf_part_fact.company_key = company_dim_a.company_key
AND conf_part_fact.owner_key = owner_dim_a.owner_key
AND conf_part_fact.date_key = start_date_dim_a.date_key
AND start_date_dim_a.cal_year_month In ( '2007-10','2007-11' )
AND toll_type_key in (select toll_type_key from toll_type_dim
where toll_type = 'ITFS ' )
AND dnis_key in ( select dnis_key from dnis_dim where dnis =
'8886410204' )
GROUP BY 1, 2, 3, 4, 5
DIRECTIVES FOLLOWED:EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 771048
Estimated # of Rows Returned: 1
Maximum Threads: 25
Temporary Files Required For: Group By
1) dwadmin.conf_part_fact: SEQUENTIAL SCAN (Parallel, fragments:
ALL)
Filters: (dwadmin.conf_part_fact.dnis_key = ANY <subquery> AND
dwadmin.conf_part_fact.toll_type_key = ANY <subquery> )
2) dwadmin.start_date_dim_a: INDEX PATH
(1) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-10'
(2) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-11'
DYNAMIC HASH JOIN
Dynamic Hash Filters: dwadmin.conf_part_fact.date_key =
dwadmin.start_date_dim_a.date_key
3) dwadmin.owner_dim_a: INDEX PATH
(1) Index Keys: owner_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.conf_part_fact.owner_key =
dwadmin.owner_dim_a.owner_key
NESTED LOOP JOIN
4) dwadmin.company_dim_a: INDEX PATH
(1) Index Keys: company_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.conf_part_fact.company_key =
dwadmin.company_dim_a.company_key
NESTED LOOP JOIN
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) dwadmin.toll_type_dim: SEQUENTIAL SCAN
Filters: dwadmin.toll_type_dim.toll_type = 'ITFS
'
Subquery:
---------
Estimated Cost: 3
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) dwadmin.dnis_dim: INDEX PATH
(1) Index Keys: dnis (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.dnis_dim.dnis = '8886410204'
Can I see the schema of the tables involved and have number of rows for each
table please.
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of bill.abler@gmail.com
Sent: Friday, February 08, 2008 1:53 PM
To: informix-list@iiug.org
Subject: DW query tuning - explain on
Hello
We have an Informix 10FC4 database on AIX in a Data warehouse type set
up with a star type schema. We have queries coming in from a Business
Object server, what this means is that I cannot change/tune the sql
statement in question. We've had an IBM Informix employee review our
onconfig, and overall design, and we've followed his report, and
generally this DW works well for non-BO type queries. We want to see
if we can get the optimizer to recognize the following query as a star
query.
The table is partitioned by month, and takes 33 minues to run, a
modified (we can't do this though as it is a BO query) is provided
that runs in 4 minutes:
Any advice is appreciated. Thanks.
QUERY:
------
SELECT /*+EXPLAIN, AVOID_EXECUTE */
company_dim_a.company_number,
company_dim_a.company_name,
owner_dim_a.owner_number,
TRIM(owner_dim_a.owner_fname) || ' ' ||
TRIM(owner_dim_a.owner_lname),
start_date_dim_a.cal_year_month,
sum(conf_part_fact.duration)
FROM
dwadmin.company_dim company_dim_a,
dwadmin.owner_dim owner_dim_a,
dwadmin.date_dim start_date_dim_a,
dwadmin.conf_part_fact,
dwadmin.toll_type_dim toll_type_dim_a,
dwadmin.dnis_dim dnis_dim_a
WHERE company_dim_a.company_key=conf_part_fact.company_key
AND owner_dim_a.owner_key=conf_part_fact.owner_key
AND conf_part_fact.date_key=start_date_dim_a.date_key
AND conf_part_fact.dnis_key=dnis_dim_a.dnis_key
AND
toll_type_dim_a.toll_type_key=dwadmin.conf_part_fact.toll_type_key
AND start_date_dim_a.cal_year_month In ( '2007-10','2007-11' )
AND toll_type_dim_a.toll_type = 'ITFS '
AND dnis_dim_a.dnis = '8886410204'
GROUP BY 1, 2, 3, 4, 5
DIRECTIVES FOLLOWED:EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 29615820
Estimated # of Rows Returned: 1
Maximum Threads: 29
Temporary Files Required For: Group By
1) dwadmin.conf_part_fact: SEQUENTIAL SCAN (Parallel, fragments:
ALL)
2) dwadmin.toll_type_dim_a: INDEX PATH
Filters: dwadmin.toll_type_dim_a.toll_type = 'ITFS '
(1) Index Keys: toll_type_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.toll_type_dim_a.toll_type_key =
dwadmin.conf_part_fact.toll_type_key
NESTED LOOP JOIN
3) dwadmin.dnis_dim_a: INDEX PATH
(1) Index Keys: dnis (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.dnis_dim_a.dnis = '8886410204'
DYNAMIC HASH JOIN
Dynamic Hash Filters: dwadmin.conf_part_fact.dnis_key =
dwadmin.dnis_dim_a.dnis_key
4) dwadmin.start_date_dim_a: INDEX PATH
(1) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-10'
(2) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-11'
DYNAMIC HASH JOIN
Dynamic Hash Filters: dwadmin.conf_part_fact.date_key =
dwadmin.start_date_dim_a.date_key
5) dwadmin.company_dim_a: INDEX PATH
(1) Index Keys: company_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.company_dim_a.company_key =
dwadmin.conf_part_fact.company_key
NESTED LOOP JOIN
6) dwadmin.owner_dim_a: INDEX PATH
(1) Index Keys: owner_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.owner_dim_a.owner_key =
dwadmin.conf_part_fact.owner_key
NESTED LOOP JOIN
The next query is similar one, but modified and it takes 4 min to run.
However this is not typical of how query would be written.
QUERY:
------
SELECT /*+EXPLAIN, AVOID_EXECUTE */
company_dim_a.company_number,
company_dim_a.company_name,
owner_dim_a.owner_number,
TRIM(owner_dim_a.owner_fname) || ' ' ||
TRIM(owner_dim_a.owner_lname),
start_date_dim_a.cal_year_month,
sum(conf_part_fact.duration)
FROM
conf_part_fact,
company_dim company_dim_a,
owner_dim owner_dim_a,
date_dim start_date_dim_a
WHERE conf_part_fact.company_key = company_dim_a.company_key
AND conf_part_fact.owner_key = owner_dim_a.owner_key
AND conf_part_fact.date_key = start_date_dim_a.date_key
AND start_date_dim_a.cal_year_month In ( '2007-10','2007-11' )
AND toll_type_key in (select toll_type_key from toll_type_dim
where toll_type = 'ITFS ' )
AND dnis_key in ( select dnis_key from dnis_dim where dnis =
'8886410204' )
GROUP BY 1, 2, 3, 4, 5
DIRECTIVES FOLLOWED:EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 771048
Estimated # of Rows Returned: 1
Maximum Threads: 25
Temporary Files Required For: Group By
1) dwadmin.conf_part_fact: SEQUENTIAL SCAN (Parallel, fragments:
ALL)
Filters: (dwadmin.conf_part_fact.dnis_key = ANY <subquery> AND
dwadmin.conf_part_fact.toll_type_key = ANY <subquery> )
2) dwadmin.start_date_dim_a: INDEX PATH
(1) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-10'
(2) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-11'
DYNAMIC HASH JOIN
Dynamic Hash Filters: dwadmin.conf_part_fact.date_key =
dwadmin.start_date_dim_a.date_key
3) dwadmin.owner_dim_a: INDEX PATH
(1) Index Keys: owner_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.conf_part_fact.owner_key =
dwadmin.owner_dim_a.owner_key
NESTED LOOP JOIN
4) dwadmin.company_dim_a: INDEX PATH
(1) Index Keys: company_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.conf_part_fact.company_key =
dwadmin.company_dim_a.company_key
NESTED LOOP JOIN
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) dwadmin.toll_type_dim: SEQUENTIAL SCAN
Filters: dwadmin.toll_type_dim.toll_type = 'ITFS
'
Subquery:
---------
Estimated Cost: 3
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) dwadmin.dnis_dim: INDEX PATH
(1) Index Keys: dnis (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.dnis_dim.dnis = '8886410204'
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
bill.abler@gmail.com wrote: > Hello > > We have an Informix 10FC4 database on AIX in a Data warehouse type set > up with a star type schema. We have queries coming in from a Business > Object server, what this means is that I cannot change/tune the sql > statement in question. We've had an IBM Informix employee review our > onconfig, and overall design, and we've followed his report, and > generally this DW works well for non-BO type queries. We want to see > if we can get the optimizer to recognize the following query as a star > query. > > The table is partitioned by month, and takes 33 minues to run, a > modified (we can't do this though as it is a BO query) is provided > that runs in 4 minutes: > > Any advice is appreciated. Thanks. > <SNIP> Let's get the obvious out of the way... Tuning issues asside for now, since you provide nothing to judge by. Are the data distributions up-to-date and of sufficient quality? Are the row count estimates in the two set explain outputs roughly accurate? If not your stats are not detailed enough. Art S. Kagel Oninit > > =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================
Thanks - here's some more background information on the tables and the
onconfig:
# of rows for table company_dim= 236244
{ TABLE "admin".company_dim row size = 74 number of columns = 8 index
size = 63
}
create table "admin".company_dim
(
company_key integer,
company_number integer,
company_name char(40),
begin_effect_date date,
end_effect_date date,
current_flag smallint,
dw_ins_date datetime year to second
default current year to second,
dw_upd_date datetime year to second
default current year to second
) in company_dim_00_t extent size 100000 next size 10000 lock mode
page; revoke all on "admin".company_dim from "public";
create unique index "admin".idx_company_dim_1 on "admin".company_dim
(company_key) using btree in company_dim_00_i ;
create index "admin".idx_company_dim_2 on "admin".company_dim
(company_number) using btree in company_dim_00_i ;
create index "admin".idx_company_dim_name on "admin".company_dim
(company_name) using btree in company_dim_00_i ;
# of rows for table owner_dim= 3211464
{ TABLE "admin".owner_dim row size = 84 number of columns = 9 index
size = 26 }
create table "admin".owner_dim
(
owner_key integer,
owner_number integer,
owner_fname char(20),
owner_lname char(30),
begin_effect_date date,
end_effect_date date,
current_flag smallint,
dw_ins_date datetime year to second
default current year to second,
dw_upd_date datetime year to second
default current year to second
)
fragment by round robin in owner_dim_01_t , owner_dim_02_t ,
owner_dim_03_t
, owner_dim_04_t
extent size 100000 next size 10000 lock mode page;
revoke all on "admin".owner_dim from "public";
create unique index "admin".idx_owner_dim_1 on "admin".owner_dim
(owner_key) using btree in owner_dim_00_i ;
create index "admin".idx_owner_dim_2 on "admin".owner_dim
(owner_number) using btree in owner_dim_00_i ;
# of rows for table date_dim= 1097
{ TABLE "admin".date_dim row size = 109 number of columns = 19 index
size = 30
}
create table "admin".date_dim
(
date_key integer,
cal_date date,
date_type char(10),
day_no_in_month smallint,
day_no_in_year smallint,
week_no_in_year char(10),
day_of_week smallint,
day_of_wk_desc char(10),
cal_month smallint,
cal_month_desc char(10),
cal_year_month char(7),
week_in_month char(10),
cal_quarter char(2),
cal_year_quarter char(10),
cal_year char(5),
weekday_ind char(1),
dw_ins_date datetime year to second
default current year to second,
dw_upd_date datetime year to second
default current year to second,
current_flag smallint
) in cess_sm_t_03_t extent size 1000 next size 1000 lock mode page;
revoke all on "admin".date_dim from "public";
create index "admin".idx_cal_date_2 on "admin".date_dim (cal_date)
using btree in cess_sm_t_00_i ;
create index "admin".idx_date_dim_cym on "admin".date_dim
(cal_year_month) using btree in cess_sm_t_03_t ;
create unique index "admin".idx_date_key on "admin".date_dim
(date_key) using btree in cess_sm_t_00_i ;
# of rows for conf_part_fact=211800334
{ TABLE "admin".conf_part_fact row size = 122 number of columns = 23
index size
= 13 }
create table "admin".conf_part_fact
(
conf_part_fact_key int8 not null ,
bu_key smallint not null ,
company_key integer not null ,
account_key integer not null ,
owner_key integer not null ,
date_key integer not null ,
time_key integer not null ,
bridge_key integer not null ,
dnis_key integer not null ,
toll_type_key integer not null ,
npanxx_key integer not null ,
billing_month_key integer not null ,
country_key integer not null ,
ace_conf_id integer,
ace_res_id integer,
conn_start_time datetime year to second,
conn_end_time datetime year to second,
duration integer,
pretax_amt decimal(16),
tax_amt decimal(16),
dialout smallint,
sys_date_ins datetime year to second
default current year to second,
sys_date_upd datetime year to second
)
fragment by expression
partition oct2006 ((date_key <= 5303 ) AND (date_key > 5272
) ) in conf_part_fact_2006_t ,
partition nov2006 ((date_key <= 5333 ) AND (date_key > 5303
) ) in conf_part_fact_2006_t ,
partition dec2006 ((date_key <= 5364 ) AND (date_key > 5333
) ) in conf_part_fact_2006_t ,
partition jan2007 ((date_key <= 5395 ) AND (date_key > 5364
) ) in conf_part_fact_2007_t ,
partition feb2007 ((date_key <= 5423 ) AND (date_key > 5395
) ) in conf_part_fact_2007_t ,
partition mar2007 ((date_key <= 5454 ) AND (date_key > 5423
) ) in conf_part_fact_2007_t ,
partition apr2007 ((date_key <= 5484 ) AND (date_key > 5454
) ) in conf_part_fact_2007_t ,
partition may2007 ((date_key <= 5515 ) AND (date_key > 5484
) ) in conf_part_fact_2007_t ,
partition jun2007 ((date_key <= 5545 ) AND (date_key > 5515
) ) in conf_part_fact_2007_t ,
partition jul2007 ((date_key <= 5576 ) AND (date_key > 5545
) ) in conf_part_fact_2007_t ,
partition aug2007 ((date_key <= 5607 ) AND (date_key > 5576
) ) in conf_part_fact_2007_t ,
partition sep2007 ((date_key <= 5637 ) AND (date_key > 5607
) ) in conf_part_fact_2007_t ,
partition oct2007 ((date_key <= 5668 ) AND (date_key > 5637
) ) in conf_part_fact_2007_t ,
partition nov2007 ((date_key <= 5698 ) AND (date_key > 5668
) ) in conf_part_fact_2007_t ,
partition dec2007 ((date_key <= 5729 ) AND (date_key > 5698
) ) in conf_part_fact_2007_t ,
partition jan2008 ((date_key <= 5760 ) AND (date_key > 5729
) ) in conf_part_fact_2008_t ,
partition feb2008 ((date_key <= 5789 ) AND (date_key > 5760
) ) in conf_part_fact_2008_t ,
partition mar2008 ((date_key <= 5820 ) AND (date_key > 5789
) ) in conf_part_fact_2008_t ,
partition p_remainder remainder in conf_part_fact_2007_t
extent size 1770000 next size 94000 lock mode page;
revoke all on "admin".conf_part_fact from "public";
create index "admin".idx_conf_part_ft_bm on "admin".conf_part_fact
(billing_month_key) using btree in conf_part_fact_04_i ;
# of rows for toll_type_dim=5
{ TABLE "admin".toll_type_dim row size = 43 number of columns = 6
index size =
7 }
create table "admin".toll
The timing difference between these two shouts dynamic hash join with
insufficient DS memory at me. You have 878MB set aside for DS memory and a
MAXPDQPRIORITY of 5, so the max the query can have is ~44MB (even if it is
setup to have a PDQPRIORITY of 100). Looking at the date dimension, you
probably only need 2-3K for that hash join (assuming ~60 rows pass the
filter). I don't know how many rows are passing the filter in the dnis
table, if not many then your maxpdq of 5 is fine - which would lead me to
ask if BO is running with PDQPRIORITY on at all. You could test that by
running the same query in dbaccess with PDQ on and off - and see if the
timing changes.
That is where I would start looking. I would also crack open Xtree on the
query and watch it run.
I would complain about the sizing on some of the tables, but they are in
their own dbspaces so it doesn't matter anyway. From here, without crawling
through the $ONCONFIG, it looks like a decent setup.
cheers
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of bill.abler@gmail.com
Sent: Friday, February 08, 2008 1:53 PM
To: informix-list@iiug.org
Subject: DW query tuning - explain on
Hello
We have an Informix 10FC4 database on AIX in a Data warehouse type set
up with a star type schema. We have queries coming in from a Business
Object server, what this means is that I cannot change/tune the sql
statement in question. We've had an IBM Informix employee review our
onconfig, and overall design, and we've followed his report, and
generally this DW works well for non-BO type queries. We want to see
if we can get the optimizer to recognize the following query as a star
query.
The table is partitioned by month, and takes 33 minues to run, a
modified (we can't do this though as it is a BO query) is provided
that runs in 4 minutes:
Any advice is appreciated. Thanks.
QUERY:
------
SELECT /*+EXPLAIN, AVOID_EXECUTE */
company_dim_a.company_number,
company_dim_a.company_name,
owner_dim_a.owner_number,
TRIM(owner_dim_a.owner_fname) || ' ' ||
TRIM(owner_dim_a.owner_lname),
start_date_dim_a.cal_year_month,
sum(conf_part_fact.duration)
FROM
dwadmin.company_dim company_dim_a,
dwadmin.owner_dim owner_dim_a,
dwadmin.date_dim start_date_dim_a,
dwadmin.conf_part_fact,
dwadmin.toll_type_dim toll_type_dim_a,
dwadmin.dnis_dim dnis_dim_a
WHERE company_dim_a.company_key=conf_part_fact.company_key
AND owner_dim_a.owner_key=conf_part_fact.owner_key
AND conf_part_fact.date_key=start_date_dim_a.date_key
AND conf_part_fact.dnis_key=dnis_dim_a.dnis_key
AND
toll_type_dim_a.toll_type_key=dwadmin.conf_part_fact.toll_type_key
AND start_date_dim_a.cal_year_month In ( '2007-10','2007-11' )
AND toll_type_dim_a.toll_type = 'ITFS '
AND dnis_dim_a.dnis = '8886410204'
GROUP BY 1, 2, 3, 4, 5
DIRECTIVES FOLLOWED:EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 29615820
Estimated # of Rows Returned: 1
Maximum Threads: 29
Temporary Files Required For: Group By
1) dwadmin.conf_part_fact: SEQUENTIAL SCAN (Parallel, fragments:
ALL)
2) dwadmin.toll_type_dim_a: INDEX PATH
Filters: dwadmin.toll_type_dim_a.toll_type = 'ITFS '
(1) Index Keys: toll_type_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.toll_type_dim_a.toll_type_key =
dwadmin.conf_part_fact.toll_type_key
NESTED LOOP JOIN
3) dwadmin.dnis_dim_a: INDEX PATH
(1) Index Keys: dnis (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.dnis_dim_a.dnis = '8886410204'
DYNAMIC HASH JOIN
Dynamic Hash Filters: dwadmin.conf_part_fact.dnis_key =
dwadmin.dnis_dim_a.dnis_key
4) dwadmin.start_date_dim_a: INDEX PATH
(1) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-10'
(2) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-11'
DYNAMIC HASH JOIN
Dynamic Hash Filters: dwadmin.conf_part_fact.date_key =
dwadmin.start_date_dim_a.date_key
5) dwadmin.company_dim_a: INDEX PATH
(1) Index Keys: company_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.company_dim_a.company_key =
dwadmin.conf_part_fact.company_key
NESTED LOOP JOIN
6) dwadmin.owner_dim_a: INDEX PATH
(1) Index Keys: owner_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.owner_dim_a.owner_key =
dwadmin.conf_part_fact.owner_key
NESTED LOOP JOIN
The next query is similar one, but modified and it takes 4 min to run.
However this is not typical of how query would be written.
QUERY:
------
SELECT /*+EXPLAIN, AVOID_EXECUTE */
company_dim_a.company_number,
company_dim_a.company_name,
owner_dim_a.owner_number,
TRIM(owner_dim_a.owner_fname) || ' ' ||
TRIM(owner_dim_a.owner_lname),
start_date_dim_a.cal_year_month,
sum(conf_part_fact.duration)
FROM
conf_part_fact,
company_dim company_dim_a,
owner_dim owner_dim_a,
date_dim start_date_dim_a
WHERE conf_part_fact.company_key = company_dim_a.company_key
AND conf_part_fact.owner_key = owner_dim_a.owner_key
AND conf_part_fact.date_key = start_date_dim_a.date_key
AND start_date_dim_a.cal_year_month In ( '2007-10','2007-11' )
AND toll_type_key in (select toll_type_key from toll_type_dim
where toll_type = 'ITFS ' )
AND dnis_key in ( select dnis_key from dnis_dim where dnis =
'8886410204' )
GROUP BY 1, 2, 3, 4, 5
DIRECTIVES FOLLOWED:EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 771048
Estimated # of Rows Returned: 1
Maximum Threads: 25
Temporary Files Required For: Group By
1) dwadmin.conf_part_fact: SEQUENTIAL SCAN (Parallel, fragments:
ALL)
Filters: (dwadmin.conf_part_fact.dnis_key = ANY <subquery> AND
dwadmin.conf_part_fact.toll_type_key = ANY <subquery> )
2) dwadmin.start_date_dim_a: INDEX PATH
(1) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-10'
(2) Index Keys: cal_year_month (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.start_date_dim_a.cal_year_month =
'2007-11'
DYNAMIC HASH JOIN
Dynamic Hash Filters: dwadmin.conf_part_fact.date_key =
dwadmin.start_date_dim_a.date_key
3) dwadmin.owner_dim_a: INDEX PATH
(1) Index Keys: owner_key (Parallel, fragments: ALL)
Lower Index Filter: dwadmin.conf_part_fact.owner_key =
dwadmin.owner_dim_a.owner_key
NESTED LOOP JOIN
4) dwadmin.company_dim_a: INDEX PATH
(1) Index Keys: company_key (Parallel, fragments: ALL)
Lower Index Filter: d
OK - I've tried the queries with PDQ set to 0 & 5 in dbaccess, run
times are almost exactly the same. One underlying question I have,
does this version, of IDS 10.00.FC3X6 (non-XPS) Informix support star
transformation?
bill.abler@gmail.com wrote:
Only XPS has explicit support for Star query expansion.
Art S. Kagel
Oninit
> OK - I've tried the queries with PDQ set to 0 & 5 in dbaccess, run
> times are almost exactly the same. One underlying question I have,
> does this version, of IDS 10.00.FC3X6 (non-XPS) Informix support star
> transformation?
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
> ===========================================================================================
> Please access the attached hyperlink for an important electronic communications disclaimer:
>
> http://www.oninit.com/home/disclaimer.php
>
> ===========================================================================================
>
>
===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.oninit.com/home/disclaimer.php
===========================================================================================
No, in dbaccess set PDQPRIORITY to 100, otherwise you are asking for 5%
(your session) of 5% (MAXPDQ) of 878MB = 2.2MB.
Not sure what you mean by star transformation. AFAIK, there is no bitmap
index in 10 which is what is required to truly handle this query properly.
I suspect the reasons for this to be more political than technical.
cheers
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of bill.abler@gmail.com
Sent: Sunday, February 10, 2008 8:59 PM
To: informix-list@iiug.org
Subject: Re: DW query tuning - explain on
OK - I've tried the queries with PDQ set to 0 & 5 in dbaccess, run
times are almost exactly the same. One underlying question I have,
does this version, of IDS 10.00.FC3X6 (non-XPS) Informix support star
transformation?
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list