DB2 Optimizer is not using Summary Table (DB2 V 8.1.7)

[Andrea]

Hi,

I've read the former postings but was not able to solve my problem:

I have a Summary Table (or MQT as you like it) and the query optimizer
does not seem to use the summary table.

I run the following commands:

set current query optimization 9;
set current refresh age = any;

drop table db2admin.PIW_MQT_PROC_AVGDUR;

create summary table db2admin.PIW_MQT_PROC_AVGDUR AS (select
p.proc_template_name, d.the_year, d.quarter, d.month_of_year,
count(*) as count,
avg(p.overall_duration) as avg_duration,
max(p.overall_duration) as max_duration,
min(p.overall_duration) as min_duration,
avg(p.waiting_duration) as wait_duration,
max(p.waiting_duration) as max_waitduration,
min(p.waiting_duration) as min_waitduration
from db2admin.piw_process p, db2admin.piw_date d
where p.start_date_key=d.date_key
group by p.proc_template_name, d.the_year, d.quarter, d.month_of_year)
Data initially deferred
Refresh deferred
Enable Query Optimization
Maintained by system
Not Logged initially
IN EVENTSPACE ;

comment on table db2admin.PIW_MQT_PROC_AVGDUR Is 'AST created for
OLAPEVT';

commit;

Alter Table db2admin.PIW_MQT_PROC_AVGDUR Activate Not Logged Initially;

Refresh Table db2admin.PIW_MQT_PROC_AVGDUR;

commit;

Runstats On Table db2admin.PIW_MQT_PROC_AVGDUR;

commit;

(I took the sequence of commands from the example sql script to create
a summary table:
http://publib.boulder.ibm.com/infoce...coptscript.htm)
Afterwards I try the following command in the Command Editor:

select p.proc_template_name, d.the_year, d.quarter, d.month_of_year,
count(*) as count,
avg(p.overall_duration) as avg_duration,
from db2admin.piw_process p, db2admin.piw_date d
where p.start_date_key=d.date_key
group by p.proc_template_name, d.the_year, d.quarter, d.month_of_year

Viewing the Access Plan (Explain SQL) the query is using the basic
tables (piw_date, piw_process) and not the summary table.

Any ideas? Are there any other settings I didn't consider?

Thanks,
Andrea

[peteh]

Hello Andrea;
I had this problem a few years ago... If you issue the "set current
refresh age=any;" statement before your query, you will tell the
optimizer that you don't care if the data in the MQT is a little stale.
You can set this to various numeric values as well to control WHEN the
optimizer chooses the MQT based on how "fresh" the data is. See the doc
for details. Good luck!

Pete H

[Andrea]

Hello Pete,

I tried your hint setting the current refresh age to 99999999999999 (as
written in the help possible values are 0, 99999999999999 or ANY; but
ANY ist just a shorthand for 99999999999999).

Beside this I found another special register and tried it as well: set
current maintained table types for optimization = ALL; This specifies
that all possible types of maintained tables controlled by this special
register, now and in the future, are to be considered when optimizing
the processing of dynamic SQL queries.

Nevertheless the optimizer is still not using the summary table!

Thanks,
Andrea

################################################## ###########

Sequence of used sql statements:

set current query optimization = 9;
set current refresh age = 99999999999999;
set current maintained table types for optimization = ALL;

- the rest is the same as in the first posting

[Philip Sherman]

All of the parameters you are manipulating are used to inform the
optimizer that it's acceptable to attempt to use use the MQT table
during optimization. None of these guarantees that the optimizer will
choose to use it. Your query asks for the count(*) which, I would guess,
is prohibiting use of the MQT. This is reasonable because a single
insert or update would invalidate the count maintained in the MQT. Have
you tried explaining retrievals for count and avg independently? I
wouldn't be surprised if the optimizer chose not to use the MQT for either.

The AVG function is not allowed (SQL ref V2 [db2s2e81-3] p326) in an MQT
when using REFRESH IMMEDIATE; a reasonable performance restriction.
Imagine the overhead of recalculating AVG for every insert, delete,
update statement.

You could try setting up indexes on the columns used by the query. Split
the index data between the index columns and INCLUDE columns to make
best advantage of the indexes for this and other queries. The optimizer
will tell you if the indexes are better performing that the base tables.

The column names (date information) you are using indicate that the
tables will contain much historical data. With the exception of the
current month; all of the data may be static and would not change once
the month ends. If this is true; then you can retrieve all data except
the current month directly from the MQT. You will have to refresh the
MQT immediately after the end of the month to keep its data "current".
Queries that include the current month should be split into a UNION of
the MQT and the base tables where only the current month's data is
retrieved from the base tables.
Phil Sherman

Andrea wrote:

Hello Pete,

I tried your hint setting the current refresh age to 99999999999999 (as
written in the help possible values are 0, 99999999999999 or ANY; but
ANY ist just a shorthand for 99999999999999).

Beside this I found another special register and tried it as well: set
current maintained table types for optimization = ALL; This specifies
that all possible types of maintained tables controlled by this special
register, now and in the future, are to be considered when optimizing
the processing of dynamic SQL queries.

Nevertheless the optimizer is still not using the summary table!

Thanks,
Andrea

################################################## ###########

Sequence of used sql statements:

set current query optimization = 9;
set current refresh age = 99999999999999;
set current maintained table types for optimization = ALL;

- the rest is the same as in the first posting

[mirof007]

Andrea,

As Pete mentioned above, you have to issue the

SET CURRENT REFRESH AGE=ANY;

in the same session/window where you're issuing your query or your
explain statement.
And as Philip said, there is no guarantees that DB2 will choose the
MQT, the decision is based on cost, although in this case, assuming you
have some real data in your tables and runstats have been ran on both
base tables as well as the mqt I would expect DB2 to pick the MQT.

[Andrea]

Thanks for your hints!

The count should be no problem: for refresh immediate Tables with GROUP
BY it is even required. Anyways I tried it without count and changed
the avg function to a sum function - without success.

Concerning the prohibition of AVG for REFRESH IMMEDIATE: thats why the
Summary Table is REFRESH DEFERRED as it would not make sense to refresh
the summary tables for every little change in the base tables.

I also set up indexes now - the give a good performance gain.
Nevertheless the costs idicate that it would be better to use the
summaryt table. I simlyfied the table like this:
create summary table db2admin.PIW_MQT_TEST AS (select
p.proc_template_name,
sum(p.overall_duration) as sum_overall_duration
from db2admin.piw_process p
where p.wpc_eventcode = 21004
group by p.proc_template_name)
Data initially deferred
Refresh deferred
Enable Query Optimization
Maintained by system
IN EVENTSPACE ;

The total cost of the query as used to create the summary table is
133.87 timerons and the cost of a direct query to the summary table
db2admin.PIW_MQT_TEST is 10.66 timerons.

Yes thats right: the tables are meant to analyse historical data. I
will most probably implement the refresh of the summary tables at first
in the corresponding web application: just a button and the user can
decide when to refresh. This is just a prototpye and for that reason
that should be sufficient.

[Andrea]

I still use the following sequence of commands in one and only one
connection to the database:

set current query optimization = 9;
set current refresh age = ANY;
set current maintained table types for optimization = ALL;

drop table db2admin.PIW_MQT_TEST;

create summary table db2admin.PIW_MQT_TEST AS (select
p.proc_template_name,
sum(p.overall_duration) as sum_overall_duration
from db2admin.piw_process p
where p.wpc_eventcode = 21004
group by p.proc_template_name)
Data initially deferred
Refresh deferred
Enable Query Optimization
Maintained by system
IN EVENTSPACE ;
comment on table db2admin.PIW_MQT_TEST Is 'AST created for OLAPEVT';

commit;

Refresh Table db2admin.PIW_MQT_TEST ;

commit;

Runstats On Table db2admin.PIW_MQT_TEST WITH DISTRIBUTION AND DETAILED
INDEXES ALL;

Runstats On Table db2admin.PIW_PROCESS WITH DISTRIBUTION AND DETAILED
INDEXES ALL;

commit;

Afterwards I test it with the same query as used to create the summary
table and the optimizer decides to use the base tables.

Are there any other settings/commands I might have fogotten?

[mirof007]

You don't have to set the refresh age prior to the creation of the mqt,
but you do have to have it set to ANY when running the query.

[Andrea]

The setting of refresh age is by chance prior to the creation of the
mqt. I run the query in the same database connection as the creation of
the mqt.

[Andrea]

I found a solution:

The Visual Explain (integrated into the Command Editor or Control
Center) is showing the wrong Access Plan. I tested this by changing the
basic date, NOT refreshing the MQT - running a query matching to the
MQT the "old" data will be displayed, running a query not matching to
the MQT (e.g. with other column functions) the fresh data from the
basic table will be displayed. Nevertheless the same Access plan will
be showed.

When I am using the Command line explain I can see that the optimizer
is using the MQT. This one shows also an Access Plan.

I run the following commands in a db2 command window. (You get this by
running the command "db2cmd" in a normal command window):

-- setting the registers: current refresh age = any, current query
optimization = 5 and current maintained table types for optimization =
all

-- delete all entries from the explain_instance table
db2 "delete from explain_instance"

-- set the current explain mode to yes
db2 set current exlpain mode yes

-- run the query
query

-- optional depending if you want to explain more sql statements
db2 set current explain mode no

- generate a ASCII file from the explain information which are at the
moment in the database (as the explain_instance was emptied before
there is just the one query in)
db2exfmt -d <database_name> -o <output_file> -1