Rebuild indexes
Job details
Name: | Rebuild indexes |
Platform: | Oracle |
Category: | Maintenance |
Premium package: | Maintenance package |
Description: | Rebuild indexes automatically. |
Long description: | Rebuild indexes based on structural statistics to improve performance |
Version: | 1.6 |
Default schedule: | 15 2 * * |
Requires engine install: | Yes |
Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & eleven_and_above=‘1′] |
Parameters
Name | Default value | Description |
---|---|---|
history threshold | 180 | Defines how many days we keep data for in the dbw_rebuild_index_hist table. |
calculate time | 1 | Defines how long time we can use to rebuild indexes in hours. (approximately) |
Index min rows | 10000 | Indexes with less rows than this value will not be checked and rebuilt |
Index max height | 3 | Indexes with a height above this level will be checked |
Index max del leaf pct | 20 | Indexes with more deleted leaf rows vs leaf rows than this level will be checked (in percent) |
Block gets per index access | 4 | Indexes with more block gets per access than this level will be checked |
Min time between index rebuild | 30 | The minimum time between index rebuild of a given index. |
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.