IBM Developer

Article

Manage your Maintained Temporary Indexes (MTIs)

Keep temporary storage under control by tending your system's MTIs

By Tim Clark

On this page

Maintained Temporary Indexes briefly explained

In part 1 of this series, we described the kinds of temporary storage that the SQL optimizer uses. We showed how the QSYS2.SYSTMPSTG system view provides a high-level overview of temporary storage across the system. There are five global buckets that directly reflect usage by the SQL optimizer. Of these buckets, this article focuses on the one labeled, *DATABASE DSI SQE MTI. This bucket reports the amount of storage used across the system by maintained temporary indexes.

Maintained Temporary Indexes (MTIs) are indexes that the query optimizer creates without any user intervention. They function like any other index on the system. Internally, they are radix indexes, just like permanent (that is, user created) indexes, and they provide the ability to read rows in keyed order or to probe for specific values. Like a permanent index, the actual size of an MTI depends on the keys included in the index and on the underlying data. They are actively maintained, meaning that any change in the dataspace is immediately reflected in the MTI. In many cases, MTIs can be shared between queries, just like permanent indexes. But they differ from permanent indexes in two important ways: they use temporary storage, and they are managed entirely by the optimizer. They are created whenever the optimizer needs them and deleted when the optimizer is finished with them.

In simplest terms, there is only one reason for the optimizer to build an MTI: no suitable permanent index exists to meet the requirements of a query. But, if we drill down into this single basic reason, we’ll find a couple of distinct circumstances that cause the optimizer to need an index. Understanding each circumstance—and whether it applies to your workload—is crucial to effectively managing your usage of MTIs.

The first circumstance that might produce MTIs is when the optimizer decides that an MTI can provide a more efficient implementation than existing indexes can provide. If the estimated cost to build the MTI seems reasonable to the optimizer, it will generate the MTI and then use it to run the query. These MTIs are shared: they will be seen by and can be used by other queries. The lifecycle of shared MTIs is tied to the existence of the underlying dataspace and to the presence in the SQL plan cache of one or more queries that use the MTI. If the dataspace is deleted or if all related plans are evicted from the plan cache, the MTI will also be deleted. (Because they use temporary storage, MTIs also do not exist beyond the current IPL.)

The second circumstance that might produce MTIs is when queries perform ordering or grouping in an environment that uses a sensitive cursor or that has the ALWCPYDTA parameter set to *NO. When the query also has a predicate (WHERE clause), the optimizer may build the MTI as a sparse index, meaning that only those rows that match the predicate are included in the index. Sparse MTIs have a lifetime that is tied to the query that created them. When that query is hard closed, the MTI is deleted. While this behavior might seem beneficial in preventing MTIs from accumulating, it also means that sparse MTIs cannot be shared across queries. This can be a problem if a workload has hundreds or thousands of these queries. Each query will build its own sparse MTI, requiring additional processing and filling temporary storage. This becomes even more problematic if the applications do not close their cursors promptly. IBM support has seen more than one critical client situation like this, which could easily have been prevented by modifying the execution environment or by building a handful of permanent indexes.

Most of the time MTIs just work and require little attention from a database engineer or a system administrator. MTIs are simply one more tool that the optimizer uses as it silently pursues its goal of giving your queries the best possible performance. Still, there are two important reasons to monitor MTI usage on your system.

Monitoring for temporary storage

The first reason for monitoring MTIs is because too many and too large MTIs can have a negative effect on system capacity. Because MTIs are built on temporary storage, they take up space in the system auxiliary storage pool (ASP), even when the associated dataspace resides in another storage pool. As your system nears its temporary storage limits, processes slow or stop, and effective recovery might require an IPL.

Monitoring MTIs is especially important if your system allows users to run custom queries. Is your workload limited to applications running a predictable set of queries, or can some users generate their own ad hoc queries, in raw SQL or through use of a reporting or BI tool? If ad hoc queries are permitted, your system is more susceptible to variation in MTI usage. A query that is poorly written—or that simply does not have adequate supporting indexes—might kick off the creation of an MTI. The more this happens, the larger your MTI temporary storage usage will grow.

High-level information about MTI temporary storage usage is available by consulting the QSYS2.SYSTMPSTG view and looking for the GLOBAL_BUCKET_NAME of *DATABASE DSI SQE MTI. This will tell you how much storage MTIs are using.

You can also evaluate how many MTIs are currently active on your system. Point in time information is available from the Plan Cache tab of the IBM i Access Client Solutions SQL Performance Center, under the Temporary Index Summary section. The Total Number of Temporary Indexes Created displays the number of times an MTI has been created since the last IPL. The Current Number of Temporary Indexes tells how many MTIs are currently active on the system. In addition, information about non-reusable MTIs is further broken out. As described earlier, a large number of non-reusable MTIs can indicate an opportunity for improving application design.

Figure 1. Plan cache details from the SQL Performance Center
alt

You can find historical information about the number of MTIs, too. Data is gathered by Collection Services, so make sure that this is enabled on any system you want to monitor. To see this information visually, visit the Performance Data Investigator tool found in IBM Navigator for i. From the Database package, select the Maintained Temporary Indexes (MTIs) perspective from the SQL Performance Data > Collection Services perspective path.

Figure 2. MTI graph from IBM Navigator for i
alt

By looking at data across multiple days and weeks you can get an idea of the baseline behavior for your system. The bar graph shows MTIs as they are created and deleted, and the line graph displays the total number of active MTIs. Because the size of MTIs might vary from workload to workload, there is no correct or optimal number of MTIs to achieve. But if your system is running low on temporary storage and you see that *DATABASE DSI SQE MTI is a large contributor, this graph is a good place to start understanding the root cause. The performance data helps you identify spikes in MTI usage that correlate to increased storage usage. Once you identify a time interval with an anomalous number of MTI creations, you may then be able to identify certain jobs or applications that triggered the MTI activity.

Monitoring for optimal query performance

The second reason for monitoring MTIs is that they can be a clue that the optimizer is running under unnecessary constraints. This constraint can be a cursor sensitivity or an ALWCPYDTA setting that is overly strict. It may also be that the optimizer does not have the permanent indexes that it needs to do its job well. Addressing these constraints in depth is beyond the scope of this article, but the strategies and resources in the following sections will provide an overview.

Drilling down for details

After determining that your system has a problem with over-use of MTIs, you’ll need to evaluate the root causes. IBM provides two important tools for obtaining more information about MTIs. Index Advisor provides a historical perspective on MTIs. The MTI_INFO SQL service provides up-to-date information about the state of MTIs on your system “right now.” By combining insights from both tools, you can confidently take action to address the root causes of MTI proliferation.

Note that recent versions of IBM i Access Client Solutions provide a graphical interface to both tools, so make sure you’re running a current version of Access Client Solutions. See the “Related topics” section at the end of this article for a link to download Access Client Solutions.

Index Advisor

As the query engine optimizes each query, it generates a list of potential indexes that might have been helpful for improving the performance of the query. This function is called the Index Advisor, and the advice generated by the Index Advisor can help guide the database engineer in improving their data model. More importantly for us, however, is that the query optimizer also generates index advice every time an MTI is created or used. This advice is distinguishable by the presence of value in the MTI Last Used column. By inspecting this subset of index advice, we can learn important information about when and why the optimizer built and used MTIs.

The following steps demonstrate how to use the Index Advisor in Access Client Solutions to find highly used MTIs.

Within IBM i Access Client Solutions, select your system. Then click the Schemas tool under the Database section.

Figure 3. IBM i Access Client Solutions
alt

Select the database you wish to inspect. Then on the menu bar, click Actions -> Index Advisor -> Advised Indexes.

Figure 4. Schemas window within IBM i Access Client Solutions
alt

Within the list of advised indexes, the MTI Used, MTI Created, and MTI Last Used columns will help you determine the tables for which the query engine is most frequently using MTIs. If the performance graph (shown in Figure 2) is showing spikes in MTI creation, finding correlated times in the First Advised for Query Use or the MTI Last Used columns may help you identify new or changed workloads that have begun using MTIs.

Figure 5. Index Advisor
alt

Note: In the Index advisor (shown in Figure 5), some of the columns have been hidden for clarity.

If this is your first time looking at the Index Advisor, you may find yourself overwhelmed by the amount of information it displays. Because the Index Advisor is continually accumulating advice as queries run, it is possible to have multiple years’ worth of data to dig through. Helpfully, there are filtering options available to narrow down the advice shown. By going to the View menu and selecting Include, you may request to see only advice generated within a specified time interval. Newer versions of Access Client Solutions also provide additional filtering based on MTI usage (Figure 6.)

Figure 6. Index Advisor filtering
alt

Another option, if you don’t need the index advice right now, is to clear the index advice (click the Clear All Advised Indexes menu option shown in Figure 4) and then to let the advice accumulate over several days or weeks, as appropriate for the system’s workload. This gives you a clearer picture of how your system is performing now and a more accurate picture of the MTIs that are affecting your current configuration.

Combining this information with the MTI usage information and the historical graph described earlier can help you decide which MTIs are the most egregious consumers of temporary storage.

MTI_INFO and the SQL Performance Center

For a long time, Index Advisor was the only way to get any kind of detailed information about MTIs. But in recent years, IBM has added another powerful tool for providing in-depth information about every MTI on the system. Initially delivered as the MTI_INFO SQL service, this information has also been exposed in a graphical interface through the SQL Performance Center, as shown in Figure 6.

Figure 7. The Maintained Temporary Indexes tool in SQL Performance Center
alt

By providing information about the current state of your system, MTI_INFO provides insight into important aspects of MTIs: the table and columns used for the definition; the amount of temporary storage each uses; the user, job and query that created it; the queries and plans that are currently using it; timestamps for creating and building the MTI; and more.

For example, if temporary storage is a concern, you might begin by looking for the schemas and tables with the set of MTIs that is using the largest amount of memory. You could run the following query:

SELECT coalesce(table_schema,'<ALL SCHEMAS>') table_schema, 
       coalesce(table_name,'<ALL TABLES>') table_name, 
       COUNT(*) nbr_of_mtis, 
       SUM(mti_size) / (1024 * 1024) total_in_MBs
    FROM TABLE (qsys2.mti_info())
    GROUP BY rollup (table_schema, table_name)
    ORDER BY 4 DESC;

If you want to understand why a particularly large MTI has been created, the QRO Hash and Plan Identifier columns let you uniquely identify the originating query in the SQL plan cache. It is easy to do this from within the SQL Performance Center by right-clicking MTI and clicking Show Statements, and you can also do this programmatically by providing the plan identifier to the DUMP_PLAN_CACHE service.

If you want to find the MTIs that are used by the largest number of open cursors, you could run the following query. By creating permanent indexes that match these MTIs, you can help to stabilize the performance variability of frequently run queries.

SELECT *
    FROM TABLE (qsys2.mti_info())
    ORDER BY reference_count desc;

You can also find non-reusable MTIs as a starting point for cleaning up applications that are running with a sensitive cursor or ALWCPYDTA *NO. These restrictive environments limit how the optimizer can implement queries.

SELECT table_schema, table_name, user_name, qualified_job_nam
    FROM TABLE (qsys2.mti_info())
    WHERE REUSABLE = 'NO';

And MTI_INFO becomes even more powerful when combined with Index Advice. The following query allows you to find the most frequently recommended indexes that correspond to MTIs currently in use on your system.

SELECT adv.table_name, adv.table_schema, adv.key_columns_advised, times_advised, last_advised
    FROM qsys2.sysixadv adv
         INNER JOIN TABLE (qsys2.mti_info()) info
             ON adv.table_name = info.table_name AND adv.table_schema = info.table_schema AND
                 adv.key_columns_advised = info.key_definition
    ORDER BY times_advised DESC;

Managing MTIs

Because MTIs are entirely managed by the optimizer and have no user-accessible controls, reducing MTI temporary storage usage is largely a matter of indirectly influencing the optimizer away from MTIs. Remember that the optimizer only builds MTIs when it can’t find a better way to do its job.

For example, you cannot explicitly drop MTIs (without dropping the underlying table—rarely a feasible solution.) But you can provide a permanent index that can take the place of the MTI. Both Index Advisor and SQL Performance Center make this easy to do by right-clicking the MTI of interest and clicking Create Index. The next time the optimizer processes a query against the underlying dataspace, it will see the permanent index and use this instead of the MTI. As the MTI falls out of use, it will eventually be deleted. However, note that in the case of shareable MTIs “eventually” may be a relatively long time. In the meantime, you will be paying the storage cost of both the permanent index and the MTI. For this reason, permanent indexes should usually not be considered a silver bullet for a system that is at or near storage capacity. In such critical conditions, the best approach is to end or hold the workloads that are generating the MTIs. You can use the information gathered with the steps above together with other system data to identify and gain control of these jobs.

What this ultimately means is that the best time to recover from an MTI storage consumption problem is long before it happens. A well-considered indexing strategy combined with SQL best practices will go a long way to eliminating an over-reliance on MTIs. You can find more information about optimization settings and index advice in the database performance section in IBM Knowledge Center and by consulting the always useful Indexing Methods and Strategies white paper.

Solve a scenario

Before we end this article, let’s put these pieces together in an example scenario.

At 4:00 PM you receive an alert that your production system is reaching a critical level of temporary storage. You quickly connect to the system and query QSYS2.SYSTMPSTG. The *DATABASE DSI SQE MTI bucket is clearly the largest, and it is much bigger than normal. Your next stop is the Performance Data Investigator view of IBM Navigator for i where you open the graph for Maintained Temporary Indexes. You see a small but noticeable upward tick in the MTIs created shortly after 2:00 PM. Moving over to Index Advisor, you filter the list for MTIs last used after 2:00 PM. There are multiple MTIs, and they all cover columns belonging to your ERP application’s main fact table. You’ve spent a lot of time creating good indexes for this table, so it is surprising to see index advice here, but you notice that the advised keys are for columns that you don’t normally see used together. To confirm that these are really the source of your storage problem, you turn to the Maintained Temporary Indexes list in the SQL Performance Center. After sorting the list by Table Name, you find 20 MTIs over the table of interest. All of these have a recent creation time and together they total over 500 GB in storage on your system. That is enough to push your temporary storage over the critical line.

Now you need to figure out who or what is causing these MTIs to be built. From the Maintained Temporary Indexes list, you select several of the concerning MTIs. You right-click the selected entries and choose Show Statements to bring up information about the originating queries from the SQL Plan Cache. The plan cache information tells you both the user and the job that first created this plan. It also tells you that these queries are running with a sensitive cursor. With this information in hand, you track down the user behind these queries. You find that the user is trying out some analytics reports over the ERP data. These are leaving their cursors open as the user opens a new browser tab for each report. This is the source of the MTI problem.

The first thing to do is to convince the user to stop running new reports, and (if possible) close the existing ones. Next, (if possible) you change the cursor sensitivity to asensitive. Then you can determine whether these queries will be run frequently in the future. If so, the best plan may be to identify common sets of keys and create permanent indexes to cover these keys.

Give yourself a pat on the back! You’ve taken care of this crisis. But before you move on, consider this an opportunity to explore one of IBM i’s built in temporary storage monitoring tools to make sure you’re not surprised by this kind of situation in the future.

Maintenance-free MTIs

Now that you understand how and why MTIs appear and how Index Advisor and MTI_INFO can help you evaluate them, you’re better equipped to keep ahead of your system’s indexing needs. This in turn means better use of your system’s temporary storage resources. With the right understanding, you can help guide the IBM Db2® for i SQL optimizer as it works to make your system run as efficiently as possible.