Tablespace Fragmentation (PD_PDTF)

Function

The Tablespace Fragmentation (PD_PDTF) record stores performance data indicating the status (at a specific point in time) of fragmentation of tablespaces. PFM - RM for Oracle creates one record for each tablespace in a database. This is a multi-instance record.

Default and changeable values

ItemDefault valueChangeable
Collection Interval600Y
Collection Offset135Y
LogNoY
LOGIF(Blank)Y

ODBC key fields

PD_PDTF_TABLESPACE_NAME

Lifetime

From the creation to the deletion of a tablespace

Record size

Fields

PFM - View name
(PFM - Manager name)
DescriptionSummaryFormatDeltaSupported versionData source
Avg Fragment
(AVERAGE_FRAGMENT)
Average fragment size in bytes.
The value of this field is free space when this field displays locally managed temporary tablespaces and the value of localtemp_option is Y, because fragment to the locally managed temporary tablespace is 1.
--doubleNoAll
  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:
    SUM(DBA_FREE_SPACE.BYTES) / COUNT(DBA_FREE_SPACE) where DBA_TABLESPACES.TABLESPACE_NAME = DBA_FREE_SPACE.TABLESPACE_NAME(+)
  • For locally managed temporary tablespaces when the value of localtemp_option is Y:
    SUM(DBA_TEMP_FILES.BYTES - V$TEMP_EXTENT_POOL.BYTES_USED)
  • For locally managed temporary tablespaces when the value of localtemp_option is N:
    SUM(V$TEMP_SPACE_HEADER.BYTES_FREE) / COUNT(V$TEMP_SPACE_HEADER) where DBA_TEMP_FILES.FILE_ID = V$TEMP_SPACE_HEADER.FILE_ID(+)
Extents
(EXTENTS)
Number of extents--ulongNoAll
  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, ordictionary managed temporary tablespaces:
    SUM(DBA_SEGMENTS.EXTENTS)
  • For locally managed temporary tablespaces when the value of localtemp_option is N:
    SUM(V$SORT_SEGMENT.TOTAL_EXTENTS)
Fragments
(FRAGMENTS)
Number of fragments.
Always 1 for locally managed temporary tablespaces when the value of localtemp_option is Y.
--ulongNoAll
  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:
    COUNT(DBA_FREE_SPACE) where DBA_TABLESPACES.TABLESPACE_NAME = DBA_FREE_SPACE.TABLESPACE_NAME(+)
  • For locally managed temporary tablespaces when the value of localtemp_option is Y:
    Remote Monitor Collector
  • For locally managed temporary tablespaces when the value of localtemp_option is N:
    COUNT(V$TEMP_SPACE_HEADER) where DBA_TEMP_FILES.FILE_ID = V$TEMP_SPACE_HEADER.FILE_ID(+)
High Max Extents
(HIGH_MAX_EXTENTS)
Number of segments whose PCT_MAX_EXTENTS value exceeds 90%--ulongNoAll
  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:
    COUNT(DBA_SEGMENTS) where EXTENTS > MAX_EXTENTS * 0.9
  • For locally managed temporary tablespaces:
    COUNT(V$SORT_SEGMENT) where EXTENT_SIZE > TOTAL_EXTENTS * 0.9
Largest Fragment
(LARGEST_FRAGMENT)
Largest fragment size in bytes
The value of this field is free space when this field displays locally managed temporary tablespaces and the value of localtemp_option is Y, because fragment to the locally managed temporary tablespace is 1.
--doubleNoAll
  • For dictionary managed permanent tabelspaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:
    MAX(DBA_FREE_SPACE.BYTES) where DBA_TABLESPACES.TABLESPACE_NAME = DBA_FREE_SPACE.TABLESPACE_NAME(+)
  • For locally managed temporary tablespaces when the value of localtemp_option is Y:
    SUM(DBA_TEMP_FILES.BYTES - V$TEMP_EXTENT_POOL.BYTES_USED)
  • For locally managed temporary tablespaces:
    MAX(V$TEMP_SPACE_HEADER.BYTES_FREE) where DBA_TEMP_FILES.FILE_ID = V$TEMP_SPACE_HEADER.FILE_ID(+)
Largest Fragment %
(LARGEST_FRAGMENT_PERCENT)
Percentage ratio of tablespace in the largest fragment--doubleNoAll
  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:
    MAX(DBA_FREE_SPACE.BYTES) / SUM(DBA_DATA_FILES.BYTES)) * 100
  • For locally managed temporary tablespaces when the value of localtemp_option is Y:
    SUM(DBA_TEMP_FILES.BYTES - V$TEMP_EXTENT_POOL.BYTES_USED) / SUM(DBA_TEMP_FILES.BYTES)) * 100
  • For locally managed temporary tablespaces when the value of localtemp_option is N:
    (MAX(V$TEMP_SPACE_HEADER.BYTES_FREE) / SUM(DBA_TEMP_FILES.BYTES)) * 100
Next Alloc Fails
(NEXT_ALLOC_FAILS)
Indicates whether the following extent allocation failed.
For failure:
1
For success:
0
These results take effect for the following conditions:
  • The tablespace is a locally managed tablespace.
  • Uniform extent management is performed for the extent.
0 is returned for any other conditions.
--ulongNoAll
  • For locally managed permanent tablespaces:
    MAX(DBA_SEGMENTS.NEXT_EXTENT) > MAX(DBA_FREE_SPACE.BYTES)
Overextended
(OVEREXTENDED)
Number of segments with more than five extents--ulongNoAll
  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:
    COUNT(DBA_SEGMENTS) where EXTENTS > 5
  • For locally managed temporary tablespaces:
    COUNT(V$SORT_SEGMENT) where TOTAL_EXTENTS > 5
Record Time
(RECORD_TIME)
Collection termination time for the performance data stored in the record--time_tNoAllRemote Monitor Collector
Record Type
(INPUT_RECORD_TYPE)
Record name (always PDTF)--string(4)NoAllRemote Monitor Collector
Segments
(SEGMENTS)
Number of segments
Always 1 for locally managed temporary tablespaces when the value of localtemp_option is Y.
--ulongNoAll
  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:
    COUNT(DBA_SEGMENTS)
  • For locally managed temporary tablespaces when the value of localtemp_option is Y:
    Remote Monitor Collector
  • For locally managed temporary tablespaces when the value of localtemp_option is N:
    COUNT(V$SORT_SEGMENT)
Start Time
(START_TIME)
Collection start time for the performance data stored in the record--time_tNoAllRemote Monitor Collector
Tablespace Name
(TABLESPACE_NAME)
Tablespace name--string(30)NoAllDBA_TABLESPACES.TABLESPACE_NAME
VA DeviceID
(VADEVICEID)
Device ID of virtual agent.--string(256)NoAllRemote Monitor Collector