Segment size collector (large segments — detail)

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and collect data related to the size and extents of large segments within an Oracle database environment.
• Why: This job is crucial because it helps in understanding space utilization and growth trends in database segments. By focusing on larger segments, database administrators can optimize performance and plan capacity effectively. Anomalies in segment sizes can indicate potential issues such as uncontrolled growth due to incorrect application logic or query inefficiencies.
• Manual checking: You can check segment details manually in the database by querying DBA_SEGMENTS and the custom views for large segments:

SELECT owner, segment_name, segment_type, tablespace_name, extents, bytes FROM dba_segments ORDER BY bytes DESC;

Detailed Implementation
The “Segment size collector (large segments — detail)” job involves several SQL and PL/SQL operations managed through dbWatch Control Center. These include:

Tables and Views Creation

1. Creating tables to hold historical and current largest segment data:
   • DBW_LARGE_SEGM – stores current snapshot of largest segments.
   • DBW_LARGE_SEGM_HISTR – stores historical data on large segments for trend analysis.

1. Creating views for easier access and reporting on segment data:
   • dbv_large_segm – view to simplify querying current large segment data.
   • dbv_large_segm_histr – view to handle data concerning historical trends in segment growth.

Procedural Implementation

1. A PL/SQL procedure “segment_size_detail” is central to this job, which:
   • Compiles a list of large segments from DBA_SEGMENTS depending on specified criteria such as ‘CLUSTER’, ‘INDEX’, etc.
   • Updates the DBW_LARGE_SEGM and DBW_LARGE_SEGM_HISTR tables with the latest segment data and historical tracking.
   • Handles changes in segment metadata such as size, type, and extent alterations.
   • Is responsible for managing executions based on the number of significant segments to track, which is configurable.

Dependencies and Execution

1. This task depends on:
   • Self-referential dependency to maintain execution integrity.
   • Access to specific system and dictionary views, requiring appropriate privileges.
   • Table and view dependencies for data storage and manipulation.

1. Automatic execution is configured to run at specified intervals using the `default-schedule` parameter ensuring regular updates.

Reporting and Analysis

• Reports are generated as part of this job, focusing on:
  • Space usage statistics, providing details about segment types and specifics about the largest segments.
  • Growth trends of large segments, useful for capacity planning and performance tuning.

• Presentation setups in the reports ensure that data is visually interpretable, featuring tables and charts for effective analysis.