Segment size status for old style tablespaces

Job details

Parameters

Job Summary

• Purpose:
  • The job is designed to monitor and analyze segment storage definitions within Oracle tablespaces that do not use local extent management. This is crucial for maintaining optimal database performance and space management.

• Why:
  • This job is essential for identifying tablespaces where the storage definition parameters might lead to inefficient space utilization caused by disproportionately large extent allocations relative to the segment’s total size. Identifying and addressing these segments can prevent potential issues such as space wastage and hindered performance.

• Manual checking:
  • To manually check these parameters in the database, you would query the DBA_SEGMENTS and DBA_TABLESPACES views to collect detailed information about storage parameters and calculate their efficiency relative to each other.

SELECT segment_type, owner, segment_name, tablespace_name, bytes/1024 AS size_kb, next_extent/1024 AS next_extent_kb
FROM dba_segments
WHERE tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE CONTENTS = 'PERMANENT' AND extent_management = 'DICTIONARY')
ORDER BY next_extent DESC;

Task Details

• Name: Segment size status for old style tablespaces
• Description: Checks various segment storage parameters and conditions to identify configurations that may lead to inefficient space use.
• Default schedule: Twice daily at 6:40 AM and 5:40 PM

Implementation

• Dependencies:
  • The task relies on internal data and definitions involving dictionary-managed tablespaces where extent management is not using localized settings.
• SQL Implementation details:
  1. Creation of a table to store segment storage data.
  2. A procedure that fetches and analyzes database segments against defined threshold parameters.
  3. Update or insertion of record into a monitoring table based on condition checks.

Reporting and Output

• Report Title: Segment size status
• Description: The report provides an overview of tablespaces, their status, and segment-wise storage definition parameters, majorly focusing on those not using localized extent management. Additionally, details such as Total Size, Next Extent, and Max Extents are listed to provide insights regarding the potential inefficiencies in space allocation.

Parameters Utilized

• “segm def next/total” – Threshold percentage that dictates when the next extent size is considered too large relative to the total segment size.
• “segm def min size” – Minimum segment size to consider for checks, provided in MB.
• “segm def ext/max” – Specifies the difference threshold between the extents allocated and max extents that triggers a warning.
• “segm def ext#” – Defines the upper threshold for the max-extents value for warning generation.

Output Tables

These tables aid in proactive database space management by alerting on potential configuration issues before they escalate into more significant problems.