Index fragmentation

Job details

Name:	Index fragmentation
Platform:	Oracle
Category:	Maintenance
Premium package:	Maintenance package
Description:	Checks the index fragmentation in all database schemas. If DELETED LEAF ROWS is high, you will get low block density.
Long description:	Checks the index fragmentation in all database schemas.
Version:	1.3
Default schedule:	40 4 4 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & nine_and_above=‘1′]

Parameters

Name	Default value	Description
history threshold	90	The number of days the statistics (size, rows count, fragmentation, etc.) will be kept in the history table.
max objects	10	The number of objects (indexes and tables) for which the statistics will be checked and collected into the history table.
size threshold	50	The minimum size (in MB) of the indexes to be analyzed for fragmentation. Only type NORMAL indexes are analyzed (not BITMAP, LOB, etc.)
with clause	VALIDATE STRUCTURE	Clause which should be specifies when running ANALYZE INDEX command. Valid inputs are “VALIDATE STRUCTURE“ or “COMPUTE STATISTICS“.
max elapsed time	60	The maximum time (in minutes) the procedure is allowed to run.
warning threshold	50	Warning threshold value in percent. If the average leaf blocks density in the B-Tree (in percent) in the index get below the threshold value, the job will return a warning.
alarm threshold	30	Alarm threshold value in percent. If the average leaf blocks density in the B-Tree (in percent) in the index get below the threshold value, the job will return an alarm.
ignore schemas	SYS, SYSTEM	List of schemas (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
enable warnings and alarms	NO	If set to “NO” (default), the job will only collect statistics without returning status warning or alarm. Value “YES“ will activate alerting.
max size threshold	5000	The maximum size (in MB) of the objects (indexes) to be analyzed for fragmentation.
continue on next run	YES	If set to “YES“ (default) the procedure will continue where it left off.
max objects per graph	5	The maximum number of objects visualized in the report graphs.
max objects per table	30	The maximum number of objects visualized in the report table.

Job Summary

Purpose: The monitoring job is designed to monitor and analyze index fragmentation across all database schemas in an Oracle environment.
Why: Index fragmentation can significantly degrade database performance and efficiency. By detecting high fragmentation levels, necessary maintenance can be performed to optimize database performance.
Manual checking: You can manually check index fragmentation by running an SQL command on the database:

ANALYZE INDEX [index_name] VALIDATE STRUCTURE;

Job Description

This job checks index fragmentation using the “INDEX_STATS“ dictionary view after executing the “ANALYZE INDEX“ command either with “VALIDATE STRUCTURE“ or “COMPUTE STATISTICS“ options.
High index fragmentation, indicated by “DELETED LEAF ROWS“ leading to low block density, triggers alerts based on defined threshold values.
The job processes each index considering multiple parameters like index size and type, but disregards specific schemas as specified.

Dependencies and Pre-requisites

The job relies on multiple database objects to collect, store, and manage fragmentation data.
These include various tables such as “dbw_index_fragm_tab” and “dbw_index_fragm_tab_histr” for storing current and historical fragmentation data respectively.
Necessary permissions like “ANALYZE ANY“ might be required for the job to run effectively, usually granted in pre-implementation steps.

Deployment and Execution

The job is scheduled to run periodically, typically starting at designated times, to ensure continuous monitoring.
Initial setup creates requisite database tables and procedures necessary for collecting and analyzing index fragmentation statistics.
It is recommended to verify and potentially adjust the execution flags and thresholds based on specific database workloads and performance considerations.

Output and Reporting

The job generates reports detailing index fragmentation metrics across selected indexes, with visual aids such as graphs for discerning historical trends and current state evaluations.
Alerts are raised if fragmentation metrics cross predefined warning and alarm thresholds, informing DBAs of potentially critical states requiring intervention.
Supplementary tables and logs record detailed execution metadata and error information to assist in troubleshooting and maintaining transparency of the job’s operational impact.

Upgrade Considerations

Upgrades typically involve adding new parameters or modifying existing procedural logic to enhance monitoring accuracy and effectiveness.
Version history and upgrade paths are maintained to ensure that enhancements are rolled out smoothly without affecting the existing setup adversely.

This monitoring job is integral for maintaining optimal performance in databases by proactively managing and minimizing index fragmentation. Regular updates and careful tuning of the job’s parameters and schedules are advised to keep pace with evolving database usage patterns and sizes.