Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Maintained Temporary Indexes (MTIs)... everyone has them, but they're similar to building a house on sand... your foundation for performance is not rock solid. Use this Gist to gain some insight into this topic!
--
-- Gist: Largest MTIs in use today
--
-- Use SQL to understand where Maintained Temporary Indexes (MTIs) are being used, and more...
--
--
--
-- Find the tables with the 10 largest MTIs
--
select table_schema, table_name, system_table_schema, system_table_name, maintained_temporary_index_size
from qsys2.systablestat
where maintained_temporary_index_size > 0
order by maintained_temporary_index_size desc
limit 10;
stop;
--
-- Find the tables with the 10 largest MTIs, and discover how many MTIs exist
--
select s.table_schema, s.table_name, maintained_temporary_index_size, index_name, s.system_table_schema, s.system_table_name
from qsys2.systablestat s, lateral (
select *
from qsys2.syspartitionindexes
where table_schema = s.table_schema and table_name = s.table_name and index_type = 'TEMPORARY'
)
where maintained_temporary_index_size > 0
order by maintained_temporary_index_size desc
limit 10;
stop;
--
-- Find the tables with the 10 largest MTIs, and see the index advice detail
--
-- Note: There isn't a direct correlation between the MTIs that exist 'right now' and the MTIs that appear in the index advice AND have been used since the last IPL
-- So, if you see 1 MTI from the previous query, you could see more than 1 MTI advices from this query
--
with last_ipl (ipl_time) as (
select job_entered_system_time
from table (
qsys2.job_info(job_status_filter => '*ACTIVE', job_user_filter => 'QSYS')
) x
where job_name = '000000/QSYS/SCPF'
)
select s.table_schema, s.table_name, maintained_temporary_index_size, adv.key_columns_advised, adv.mti_created,
adv.mti_used, adv.last_mti_used, adv.mti_used_for_stats, adv.last_mti_used_for_stats, s.system_table_schema,
s.system_table_name
from qsys2.systablestat s,
lateral (
select *
from last_ipl, qsys2.condidxa c
where c.table_schema = s.table_schema and c.table_name = s.table_name and (c.last_mti_used > ipl_time or
c.last_mti_used_for_stats > ipl_time)
) adv
where maintained_temporary_index_size > 0
order by maintained_temporary_index_size desc
limit 10;
stop;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment