473,287 Members | 2,263 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Runstats manipulation

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
Nov 12 '05 #1
3 2136
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


Nov 12 '05 #2
"Jan Arickx" <ja********@tiscali.be> wrote in message
news:b5**************************@posting.google.c om...
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.
Nov 12 '05 #3
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********@tiscali.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.

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Spencer | last post by:
We are on 8.1.6 currently on AIX. Has anyone been able to come up with a solution that allows the ability to do a RUNSTATS from SQL prompt instead of the command line? I see lots of posts about...
7
by: Todd McNeill | last post by:
Hi- Something was just pointed out to me this morning. According to the V8 Command Reference, the RUNSTATS command no longer uses the SHRLEVEL CHANGE/REFERENCE clauses, and it looks to be...
3
by: Gilda | last post by:
Does anyone know what entity (utility?) checks if statistics collections are required, if the Automatic RUNSTATS is enabled, in version 8.2? How often are these checks done? Thanks.
1
by: raqfg | last post by:
Hi. I am trying to test out the automatic maintanance with notification feature of DB2 v8.2. I have enabled the auto maint with notification. The problem I am facing is that I only get email...
5
by: Jari Korkiakoski | last post by:
Hello, What kind of difference does it make to update statistics via 'REORGCHK UPDATE STATISTICS ON TABLE ALL' or by running directly 'runstats on table xxx.yyy with distribution and detailed...
4
by: kenfar | last post by:
I've got a large table on db2 8.2.1 that I rarely perform runstats on. It has about 600 million rows organized in a single MDC time dimension on a non-dpf warehouse. Anyhow, we recently ran...
4
by: db2udb | last post by:
Hi, I have just taken over as the DBA for a database that has not had any reorgs/runstats run against it for the last three years. As a first step, I have just run reorg/runstats against the system...
3
by: Norm | last post by:
If RUNSTATS is called directly, we can specify UTIL_IMPACT_PRIORITY to limit CPU consumption. For automatic runstats, there is no place to specify this paramater. Will it use the DBM value of...
1
by: satish mullapudi | last post by:
Hi Am using DB2 UDB ESE on AIX platform. I used to take a full/ incremental online backup regularly. This process used to go on since years. But recently, whenever am trying to take the same, am...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.