Extent fragmentation

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor extent fragmentation in Oracle databases and signal any potential issues.
• Why: Assessing extent fragmentation is crucial for maintaining the performance and efficiency of database operations. If fragmentation is too high, it can degrade performance and increase the time required for database operations. This job helps in identifying those tablespaces that require rebuilds to optimize performance.
• Manual checking: You can manually check extent fragmentation in an Oracle database by using the following SQL command:

select /*+ RULE*/ tablespace_name, count(*) from dba_free_space group by tablespace_name;

Job Details

• Name: Oracle Extent Fragmentation
• Company: dbwatch.com
• Version: 1.24
• Category: Maintenance
• Default Schedule: Every 12 hours
• Timeout: 600 seconds

Execution Logic

This monitoring job performs the following steps:

• Query the Oracle database for extent counts by tablespace from the dba_free_space view.
• Convert resultant data to evaluate if any tablespace exceeds the defined threshold and is not part of the excluded tablespaces.
• Generate a status and message based on the extent count, indicating whether a rebuild is needed or if everything is within acceptable parameters.

Output Handling

• The job produces a detailed message describing the status of each tablespace regarding extent fragmentation.
• If the fragmentation is above the warning threshold for a particular tablespace, it is marked for a rebuild.
• Tablespaces explicitly excluded will not be considered in fragmentation checks.
• A comprehensive status summary is set, highlighting any detected issues or confirming the absence of fragmentation concerns.

Extent Fragmentation Reporting (Table Format)

JavaScript Logic Analysis

• Checks each tablespace for extent count against the threshold, skipping excluded tablespaces.
• Aggregates messages for those tablespaces needing attention.
• Sets the job status to normal or warning based on evaluation results, and constructs an error message appropriately.

Through automation, this job efficiently manages database maintenance concerns related to extent fragmentation, contributing to database health and optimized performance.