Skip to content
English
Questions? Comments? Customer portal Sign in
Download ProTop
  • There are no suggestions because the search field is empty.

Report: Index Compaction Candidate

The *.idxcompact.rpt files under rpt/ list indexes that are large enough to matter and under-utilized enough that running proutil ... -C idxcompact would likely free space and speed up index reads.

They are kicked off by util/dbanalys.p and written by the idxcompactReport procedure insidessg/sausage20.i.

One file is written per database, named <shortname>.idxcompact.rpt and
placed in $RPTDIR. A companion shell script (idxcompact.<shortname>.sh on Unix or idxcompact.<shortname>.bat on Windows) is written to $pt_scriptDir containing ready-to-run proutil -C idxcompact commands for the same indexes.

Selection criteria

An index is reported only if both of the following are true (matching
tblReport / areaReport data captured earlier in the run):

  • ixlist.blks >= 1000 — the index occupies at least 1000 blocks (so the
    potential payoff is meaningful).
  • ixlist.pctut < 70 — average index-block utilization is below 70%.

Rows are sorted ascending by ixlist.pctut so the worst offenders appear
first.

Layout

Index Compaction Summary for <pt_shortName> <pdbname(1)>

    Table.Index Name                              Blocks         Util%
    --------------------                  --------------  ------------
    <table>.<index>                       <blks>          <pctut>%
    ...

Columns:

  • Table.Index Name — ixlist.idx, joined as <table>.<index>.
  • Blocks — ixlist.blks, the number of database blocks the index
    occupies right now.
  • Util% — ixlist.pctut, the average percentage of each index block
    that contains real key data. The lower this is, the more space (and
    cache) compaction can reclaim.

If no index matches the thresholds the body is empty (only the header is
printed).

Side effects

  • For each reported index, while zmonName = "healthcheck" an alarm is
    published via publish "alarm" ( "ixUtil", alertText ). The alert text
    carries the table.index, blocks, and pctut so the dispatcher can
    surface it.

  • After the report is closed, lib/uploader.p ships it off as part of the
    ProTop run, same as the other DB-analysis reports.

  • A second file is written to $pt_scriptDir containing executable
    compaction commands. Per-row content:

    $DLC/bin/proutil <db> -C idxcompact <table>.<index> $IDXPCT  # <blks> <pctut>

    on Unix, or the proutil call equivalent on Windows. The script
    defaults IDXPCT=90 (target post-compaction fill rate) and is meant to
    be reviewed and run manually.

Reading the report

  • An index appearing here with very low Util% and a Blocks count in
    the thousands is essentially the textbook win for idxcompact — running
    the companion script will reclaim space and reduce logical reads.
  • Indexes that drop in and out of the report between runs typically indicate
    workloads with bulk insert/delete cycles; consider whether a higher
    target (IDXPCT) is warranted.
  • The 1000-block / 70%-util cutoffs are deliberately conservative — if you
    want a more aggressive or more lenient list, edit the where-clause in
    idxcompactReport.