Runstats manipulation

[Jan Arickx]

My situation is as follows. I have several big SQL queries in a data
warehouse using 1 big fact tables and 10 dimension tables. The queries
join all of them together but the optimizer doesn't care which
dimension table it joins first (we ran runstats on all of them but the
dimensions look a lot alike... about same order of rows, ...). I know
it should first join on 1 specific dimension if it's in the query
since that dimension will discard most of the facts.

I can't change the queries as they are being generated by an
off-the-shelf tool. I was thinking of manipulating the runstats data
manually (db2look -m ..., and then applying manual changes) but can
anyone enlighten me how to "fix" the runstats information to give this
table more preference by the optimizer.

We're running v7.2 of IBM DB2 on HP-UX. And I know it's not a nice
solution but if it makes the difference between a runtime of 1 minute
and 8 hours... I'm willing to do some dirty tricks (I played with the
SQL query manually by adding some coalesce's and when the optimizer
goes over my 1 dimension the query is blazingly fast).

Regards,
Jan

[Larry]

You might want to take a look at the SET OPTIMIZATION LEVEL command, and
you also might want to open up a PMR with IBM support to see if they
have any suggestions. You never know when an optimizer defect has been
reported, or if they can offer a suggested circumvention.

Larry Edelstein

Jan Arickx wrote:

My situation is as follows. I have several big SQL queries in a data
warehouse using 1 big fact tables and 10 dimension tables. The queries
join all of them together but the optimizer doesn't care which
dimension table it joins first (we ran runstats on all of them but the
dimensions look a lot alike... about same order of rows, ...). I know
it should first join on 1 specific dimension if it's in the query
since that dimension will discard most of the facts.

I can't change the queries as they are being generated by an
off-the-shelf tool. I was thinking of manipulating the runstats data
manually (db2look -m ..., and then applying manual changes) but can
anyone enlighten me how to "fix" the runstats information to give this
table more preference by the optimizer.

We're running v7.2 of IBM DB2 on HP-UX. And I know it's not a nice
solution but if it makes the difference between a runtime of 1 minute
and 8 hours... I'm willing to do some dirty tricks (I played with the
SQL query manually by adding some coalesce's and when the optimizer
goes over my 1 dimension the query is blazingly fast).

Regards,
Jan

[Mark A]

"Jan Arickx" <ja********@tis cali.be> wrote in message
news:b5******** *************** ***@posting.goo gle.com...

My situation is as follows. I have several big SQL queries in a data
warehouse using 1 big fact tables and 10 dimension tables. The queries
join all of them together but the optimizer doesn't care which
dimension table it joins first (we ran runstats on all of them but the
dimensions look a lot alike... about same order of rows, ...). I know
it should first join on 1 specific dimension if it's in the query
since that dimension will discard most of the facts.

I can't change the queries as they are being generated by an
off-the-shelf tool. I was thinking of manipulating the runstats data
manually (db2look -m ..., and then applying manual changes) but can
anyone enlighten me how to "fix" the runstats information to give this
table more preference by the optimizer.

We're running v7.2 of IBM DB2 on HP-UX. And I know it's not a nice
solution but if it makes the difference between a runtime of 1 minute
and 8 hours... I'm willing to do some dirty tricks (I played with the
SQL query manually by adding some coalesce's and when the optimizer
goes over my 1 dimension the query is blazingly fast).

Regards,
Jan

Try running the runstats with distribution on key columns (or all columns if
you have enough of a window). You probably don't need to keep capturing
these detailed stats unless your data changes significantly.

Then change the query optimization level to 7 (from default of 5) for
problematic queries. There are several ways of changing the query
optimization level, depending on your interface method (static sql, clp,
jdbc, etc.). You can also set the default for the database if the entire
database is data warehouse and you don't care about the slight delay in each
query to do the extra optimization each time. Don't try optimization level 9
unless you are sure it will work better than 7.

[Amy DBA]

You might play around with the bufferpools, too. I've seen the optimizer
decide not to use an index in a DW situation based on the size of the
bufferpool. You should also examine the EXPLAIN info to get a better idea
of what the optimizer is deciding to do.

Amy

"Jan Arickx" <ja********@tis cali.be> wrote in message

I can't change the queries as they are being generated by an
off-the-shelf tool. I was thinking of manipulating the runstats data
manually (db2look -m ..., and then applying manual changes) but can
anyone enlighten me how to "fix" the runstats information to give this
table more preference by the optimizer.