Indexes and Reorganization - Relational Database Index Design and the Optimizers - page 224

According to the QUBE, the elapsed time per one million rows is 4min for
the second alternative (2 million sequential touches, 2 million SQL calls, 1 million
rows sorted), but many utilities are faster because they bypass the application
programming interface. The sort phase is often the longest component, and it
consumes a lot of CPU time (e.g., 10 s for one million rows).
The lock waits created by index reorganization are product and option depen-
dent. With a simple utility, the whole table may be S locked (updaters have to
wait) while the table or the index is being scanned. The lock wait time is much
shorter if the utility saves the updates performed during the scan and applies
them before the sort phase.
Large indexes may have to be reorganized at inconvenient hours. In the worst
case, locking problems make frequent reorganizations impossible. In this case,
some volatile indexes may have to be made resident (pinned in memory).

SPLIT MONITORING

The reorganization characteristics of certain selected indexes, at least those on
large tables that have a lot of inserts and updates, should be predicted as we have
shown earlier. For the majority, however, the decision to reorganize an index can
be based on monitoring.
The number of leaf page splits per index can be monitored explicitly or
simply determined by observing the number of leaf pages. When the inserts are
random and pages are not left empty at reorganization, the LPSR (the number
of leaf page splits divided by the number of leaf pages) is adequate to identify
reorganization candidates. The number of long leaf page splits divided by the
number of leaf pages—let us call this ratio LLPSR—is a better indicator (fewer
false alarms) because short leaf page splits seldom create random touches for
an index slice scan. The LLPSR is reported by DB2 for z/OS V7 Real Time
Statistics, for instance:

(100 × REORGLEAFFAR / NACTIVE)

If the DBMS splits the last leaf page when a new index row is added at the
end of the index, indexes with ever-increasing keys should be manually excluded
from the list of reorganization candidates if the LLSPR is not available.
Let us assume a scenario with 2000 tables and 5000 indexes. Most indexes
are created with P = 10% (the default in Oracle and DB2); the indexes of read-
only tables have P = 0, and selected indexes of fast growing tables have higher,
tailored P values and reorganization schedules. Other indexes are reorganized
once a year.
If there are many inserts and updates, the state of the indexes should probably
be checked weekly. The most important report should show all indexes with
LPSR

> >

1% if this facility is available); but supposing this
report contains hundreds of indexes? The reported indexes are only suspects;
they may need reorganization, but it is quite possible that most of them do not;
it depends on how they are used, for example:

1% (or LLPSR