Segment size collector (all segments — aggregate)

Job details

Parameters

Job Summary

• Purpose: This job is designed to collect, store, and manage statistics about all segment types within an Oracle 9i instance, based on the segment-owner and tablespace name. It helps in monitoring the size and number of segments to provide insights into database growth and storage usage.

• Why: This job is crucial for maintaining efficient database capacity management:
  • It helps in forecasting database growth and planning for necessary scalability operations.
  • Efficiently monitors segment size changes, which supports performance tuning and optimization efforts.
  • Stores historical data, allowing for trend analysis and audit reports on segment growth over time.
• Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT * FROM DBA_SEGMENTS;

SELECT * FROM DBA_FREE_SPACE;

SELECT * FROM DBA_TABLESPACES;

Detailed Description and Dependencies

The job “Segment size collector (all segments – aggregate)” is implemented to gather detailed information about database segments using a variety of views and tables that are custom-created as part of the job setup. Below are notable SQL implementations and their dependencies involved:

Dependencies Overview:

• Primary Dependencies:
  • Segment tables such as DBW_ALL_SEGM, DBW_ALL_SEGM_HISTORY which store the segment records and historical segment data, respectively.
  • Supporting views like dbv_histr_period, dbv_tot_size, and dbv_space_used help in calculating total sizes and historical periods which aid in growth calculations.
  • Views for schemas and tablespaces like dbv_largest_schemas and dbv_largest_tablespaces rank and organize schema and tablespace data according to their sizes.

• Key Views and Their Functions:
  • dbv_size_growth: Calculates the space growth between recorded intervals.
  • dbv_GR_per_year: Estimates the annual growth rate based on size growth and period.
  • dbv_ts_used_space, dbv_ts_free_space: Provide details on the used and free space in tablespaces, important for capacity planning.

SQL Implementation Specifics:

```sql
CREATE OR REPLACE PROCEDURE Segment_Size_Aggregate(taskid IN NUMBER) IS […] — Main procedure for aggregating segment size
CREATE TABLE DBW_ALL_SEGM […] — Main table holding the segment data
CREATE OR REPLACE VIEW dbv_space_used AS […] — View to calculate used space
```

Execution and Scheduling

• This job is scheduled to run automatically as defined, aiming at regular intervals (e.g., monthly or bi-weekly) to ensure the database statistics are up to date and reflect the current state of the segments.
• The auto-scheduling is designed to not overload the database during peak hours, adhering to best practices about system performance management.

Automated and Manual Monitoring

While dbWatch Control Center automates this job to reduce admin overhead, database administrators can still manually query related views and tables to verify data or perform ad-hoc checks based on current database performance or issues needing immediate attention.

Impact and Cleanup

• This job’s data help in proactive database management but can grow significantly over time. Hence, regular cleanup tasks are automated to purge old entries that are no longer relevant, ensuring system efficiency.
• Dependency checks ensure that necessary objects exist before job execution, and cleanup tasks help maintain a minimal footprint on system resources.

Security and Access Control

• Proper grants and privileges are required by the procedure to access system views and tables. These privileges need to be maintained and audited to ensure security compliance and to prevent unauthorized access to sensitive data.