Segment size collector (all segments — aggregate)

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect and aggregate data concerning the sizes and numbers of all segment types per segment-owner and tablespace name in Oracle databases version 10g or higher. It aids in tracking database capacity growth and helps in planning for capacity expansion.
• Why: This job is important as it offers a historical view of data growth, which is crucial for performance tuning, ensuring efficient space allocation and understanding trends in data storage. Monitoring growth trends helps in proactive database management and avoiding potential space constraints that could lead to downtime or performance degradation.
• Manual checking: To manually check the total size and number of segments, run the following SQL code:

SELECT tablespace_name, owner, segment_type, COUNT(*), SUM(bytes) FROM dba_segments GROUP BY tablespace_name, owner, segment_type

Specifics of Implementation

• “Segment_size_aggregate” Procedure: This is the core part of the job where Oracle DB segments are analyzed. It considers different types of segments such as DEFAULT, TEMPORARY, and ROLLBACK among others. The procedure is executed to gather data on the segments’ counts and sizes which then feeds into various reports and housekeeping tasks.
• Aggregation and Views Creation: Several views are created to support the aggregation and reporting on the database segments. These include views for historical data analysis, growth rate calculation, and reviews of largest segments, schemas, and tablespaces.
• Dependency Objects: This job requires several dependency tables and views to be present. These dependencies ensure that data integrity and historical tracking is maintained properly. They also serve to improve the efficiency by storing intermediate results and ranks required for generating reports and views.
• Housekeeping and Updates: The task includes clean-up processes where outdated data entries are removed according to specific criteria. This helps to manage the storage and improve the performance of queries executed against the aggregated historical data.

Scheduled Operations

• Default Schedule: The job is scheduled to run on the 1st and 5th day of every month at 6:50 PM. This scheduling helps to keep track of data growth and segment change on a near-monthly basis, providing consistent and actionable insights.
• Dependencies Management: If any task or dependency fails, the cleanup actions are triggered to ensure that there are no partial data writes that might affect the accuracy of future reports.

Outcome and Use of Data

• Reporting: The data collected and aggregated by this task feeds into several reports. These reports provide insights into segment sizes, database growth patterns, schema statistics, and tablespace utilization.
• Capacity Planning: By continuously tracking the size and number of segments, IT departments can better plan for needed expansions or adjustments, potentially saving costs and optimizing performance.
• Trend Analysis: Long-term data collection helps in analyzing trends which are crucial for predicting future needs and planning maintenance or optimizations.

Overall, this dbWatch job plays a crucial role in the monitoring and management of Oracle database segments, providing vital information for database administrators and IT departments focused on maintaining optimal database performance and capacity.