Question & Answer
Question
Answer
- Additional Details for Q3
The optimizer requires several executions of a query in order to learn from the repeated index advice before it can justify the cost of creating the autonomic index. The system must observe frequent runs before the benefits of using the index start to outweigh the cost of creating it. Once the optimizer decides to create an MTI, it takes time to build the index so the first execution of the query could be slow as the user has to wait for the MTI to be built. Temporary indexes can be shared across jobs and other queries executing on the system; therefore, MTI usage depends on which queries have historically run and caused an MTI to be built. For example, Query2 might build an MTI and Query1 starts using that MTI. Query1 is an important query and is now using index access; the users have come to expect the fast performing index access plan. Then, the MTI is removed, due to an IPL, and Query2 is no longer executed as much as it was before the IPL so the MTI has not reappeared. In the meantime, users of Query1 start to complain about the performance degradation in Query1. The solution is to build a permanent index for Query1
- Additional Details for Q4
The amortization algorithm means that you might see the optimizer initially pick a non-MTI access plan but over several executions of the query, the optimizer can reestimate the cost of building and maintaining an MTI and determine it is now low enough to warrant a new access plan that does use an MTI. Amortization algorithms help to balance the one-time cost of creating the MTI with the expected runtime savings of subsequent query runs
create table mti_info as (select * from table(qsys2.MTI_INFO('*ALL', '*ALL')) ) with data;
select * from mti_info where mti_size > 1000000000;
-- MTIs that currently are the most active; the reference count is like an in use count
select * from mti_info x order by reference_count desc;
--MTIs used by the most queries, length of QRO hash identifies MTIs serviced by the most variety of queries
select length(qro_hash_json), x.* from mti_info x order by 1 desc;
-- MTIs that are the longest lived (current date is most relevant to when the snapshot of MTIs was taken, for example the snap is weeks old, would adjust current to
select current_date - date(create_time), x.* from mti_info x order by 1 desc;
Related Information
Was this topic helpful?
Document Information
Modified date:
10 May 2024
UID
ibm16205533