Rebuild indexes

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to automatically rebuild indexes based on their structural statistics to improve the performance of Oracle database systems.
• Why: This job is important because maintaining optimal index structure is crucial for efficient database operations, particularly in high-volume environments. Well-maintained indexes increase the speed of data retrieval operations, thereby enhancing overall system performance. Continuous monitoring and rebuilding of indexes prevent performance degradation over time.
• Manual checking: You can check the need for index rebuilding manually in the Oracle database by executing the following SQL commands:

SELECT index_name, table_name, last_analyzed, blevel, leaf_blocks, distinct_keys, num_rows, avg_leaf_blocks_per_key, avg_data_blocks_per_key
FROM all_indexes
WHERE owner = 'YourSchema';

Job Description and Details

This monitoring job specifically targets instances where the Oracle database version is 11g or above. It carefully profiles these instances to ensure that the indexes needing attention are highlighted and processed.

Features:

• Scheduled to run automatically.
• Primarily intended for maintenance category tasks.
• Version controlled by dbwatch.com, ensuring that the process aligns with best practices and recent software updates.
• Acknowledges dependencies and attempts cleanup in cases of failures.

Dependencies

The job has several dependencies, which must be successfully managed for the operation to complete:

• Internal procedure dbw_rebuild_index_proc which contains the logic for deciding which indexes need to be rebuilt,
• Various history tables like dbw_rebuild_indexes_stat, dbw_not_rebuild_indexes, and dbw_rebuild_indexes_hist to track the rebuilding processes and exceptions.

Parameters
(Key configuration settings influencing job execution; their actual descriptions and values are excluded by request.)

SQL Code Implementation Details

Code segments crucial for the job include creating necessary tables and procedures that ensure detailed tracking and logging of rebuild operations:

• Tables to store statistics about indexes before and after rebuilding,
• Procedures that define the logic for selecting indexes to rebuild based on certain database parameter values like index height, leaf row deletions, and time since last index access.

The procedure dbw_rebuild_index_proc orchestrates the entire process:

• It decides whether an index should be rebuilt based on statistics.
• Executes SQL commands to analyze and rebuild the index if required.
• Logs detailed information about the index performance before and after the rebuild.
• Captures error information on failures to provide insights into why certain indexes weren’t successfully rebuilt.

Output Reports

• A comprehensive report is generated, which includes:
• Historical data of indexed rows before and after the rebuild.

The report is formatted into a table layout, ensuring readability and easy access to historical performance data, which aids in continuous monitoring and tuning decisions.

Conclusion

This monitoring job is a critical component of Oracle database performance tuning. Its automatic scheduling, detailed parameterization, and thorough logging mechanism make it a valuable tool for database administrators aiming to achieve optimal performance through proactive index management.