Chapter 2. DB2 UDB’s materialized views 67
2.8.7 Step 7: Introduce cost issues into materialized view routing
Once all the user queries have been processed and confirmed to route to the
empty materialized views, we need to confirm that this routing will occur with
populated materialized views as well.
Create appropriate indexes on the materialized views using the Index Advisor if
needed, and update production data statistics for the materialized views and
indexes.
If EXPLAIN shows that a particular query is
no longer being routed to the
materialized view, then it is because the DB2 optimizer has determined that it is
more efficient to access the base tables directly than via the materialized view.
2.8.8 Step 8: Estimate performance gains
Having established that routing occurs to the materialized views with production
statistics, we still need to determine whether such routing will result in
satisfactory performance gains. However, given the sheer volume of data
involved and the time it takes to load a materialized view, we need to ascertain
performance gains without having to load the actual production data.
The solution is to:
1. Create “miniature” base tables and materialized views using sample data that
is representative of production data.
Attention: Do not populate the materialized views with production data, as
this may be a very time consuming process. We first need to verify routing with
production data statistics before populating the materialized views.
Important: This may or may not be a problem, since only an actual runtime
comparison with and without materialized view routing can help pinpoint a
potential issue. Forcing a routing to the materialized view will require updating
materialized view statistics to deceive the DB2 optimizer into thinking that the
materialized view has fewer rows than is actually the case. This scenario will
have to be dealt on a case by case basis, depending upon the priority and
performance of the query.
Important: Without a representative sample of the real world environment,
performance estimates using this approach will be inconclusive,
68 High-Function Business Intelligence in e-business
2. Perform a comparison of query performance with and without materialized
view routing.
The following is an overview of the steps involved:
1. Create “miniature” replicas of the base tables, materialized views, and their
corresponding indexes.
2. Extract a representative sample of production data from the base tables, and
populate the “miniature” base tables with this sample data.
3. Refresh the “miniature” materialized views from the “miniature” base tables.
4. Perform
runstats against both “miniature” base tables and materialized
views.
5. EXPLAIN the query to ensure that routing to the materialized views is
occurring.
6. Execute the query and measure the performance with routing in effect.
7. Disable query optimization against the materialized view. This can be
achieved either by setting the query optimization level as described in
“State considerations” on page 44, or ALTERing the materialized view AS
DEFINITION ONLY as described in “Materialized view ALTER considerations”
on page 41.
8. EXPLAIN the query to ensure that the materialized view is being ignored.
Take appropriate action to disable materialized view routing — worst case is
to drop the materialized view!
9. Execute the query and measure the performance with
no routing in effect.
10.Quantify the performance gains with and without routing to the materialized
view.
11.Extrapolate the performance gains to production data.
With satisfactory estimates of performance gains, we can proceed to the next
step of loading the production data into the materialized views, executing
runstats against them, and enabling them for optimization.
Note: If routing does not occur, then the causes need to be investigated
and corrected using techniques described earlier.
Attention: If the estimated performance gains are unsatisfactory, then the
design of the materialized view has to be reviewed, and the entire design
process reiterated.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.