Database Interval (PI_PIDB)

Function

The Database Interval (PI_PIDB) record stores performance data, taken at specific intervals, about a database.

Default and changeable values

ItemDefault valueChangeable
Collection Interval3600Y
Collection Offset10Y
LogNoY
LOGIF(Blank)Y

ODBC key fields

None

Lifetime

From the creation to the deletion of a database

Record size

Fields

PFM - View name
(PFM - Manager name)
DescriptionSummaryFormatDeltaSupported versionData source
Archive Change #
(ARCHIVE_CHANGE_NUM)
Last archived system change number (SCN)#1COPYdoubleNoAllV$DATABASE.ARCHIVE_CHANGE#
Blocks
(BLOCKS)
Size of the database in Oracle blocks#2AVGdoubleNoAll
  • For Oracle that does not have any locally managed temporary tablespaces:
    SUM(DBA_DATA_FILES.BLOCKS)
  • For Oracle that has locally managed temporary tablespaces:
    SUM(DBA_DATA_FILES.BLOCKS) + SUM(DBA_TEMP_FILES.BLOCKS)
Checkpoint Change #
(CHECKPOINT_CHANGE_NUM)
System change number (SCN) at the last checkpoint#1COPYdoubleNoAllV$DATABASE.CHECKPOINT_CHANGE#
Created
(CREATED)
Creation date #1COPYstring(20)NoAllV$DATABASE.CREATED
Datafiles
(DATAFILES)
Number of data files #2AVGulongNoAll
  • For Oracle that does not have any locally managed temporary tablespaces:
    COUNT(V$DATAFILE)
  • For Oracle that has locally managed temporary tablespaces:
    COUNT(V$DATAFILE) + COUNT(DBA_TEMP_FILES)
DB Files %
(PERCENT_DB_FILES)
Percentage ratio of the data files to the DB_FILES parameter value in the init.ora file #2AVGdoubleNoAll
  • For Oracle that does not have any locally managed temporary tablespaces:
    (COUNT(V$DATAFILE) / init.ora DB_FILES) * 100
  • For Oracle that has locally managed temporary tablespaces:
    ((COUNT(V$DATAFILE) + COUNT(DBA_TEMP_FILES)) / init.ora DB_FILES) * 100
DB Name
(NAME)
Database name #1COPYstring(9)NoAllV$DATABASE.NAME
Extents
(EXTENTS)
Correct values cannot be collected in this field.
Number of extents. #2
AVGdoubleNoAll
  • For Oracle that does not have any locally managed temporary tablespaces:
    SUM(DBA_SEGMENTS.EXTENTS)
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option is Y:
    SUM(DBA_SEGMENTS.EXTENTS) + SUM(DBA_TEMP_FILES.BYTES / V$TEMP_EXTENT_MAP.BYTES)
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option is N:
    SUM(DBA_SEGMENTS.EXTENTS) + SUM(V$SORT_SEGMENT.TOTAL_EXTENTS)
Free %
(PERCENT_FREE)
Percentage ratio of free space #2AVGdoubleNoAll
  • For Oracle that does not have any locally managed temporary tablespaces or has UNDO tablespaces when the value of undospace_option is N:
    (SUM(DBA_FREE_SPACE.BYTES) / DBA_DATA_FILES.BYTES) * 100
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option is Y:
    ((SUM(DBA_FREE_SPACE.BYTES) + SUM(DBA_TEMP_FILES.BYTES - V$TEMP_EXTENT_POOL. BYTES_USED)) / (DBA_DATA_FILES.BYTES + DBA_TEMP_FILES.BYTES)) * 100
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option isN:
    ((SUM(DBA_FREE_SPACE.BYTES) + SUM(V$TEMP_SPACE_HEADER.BYTES_FREE)) / (DBA_DATA_FILES.BYTES+DBA_TEMP_FILES.BYTES)) * 100
  • For the UNDO tablespaces when the value of undospace_option is Y:
    ((SUM(DBA_FREE_SPACE.BYTES) + SUM(DBA_UNDO_EXTENTS.BYTES) WHERE STATUS='EXPIRED') / DBA_DATA_FILES.BYTES) * 100
Free Change
(FREE_CHANGE)
Change to the free space in bytesAVGdoubleNoAll
  • For Oracle that does not have any UNDO tablespaces or has UNDO tablespaces when the value of undospace_option is N:
    SUM(DBA_FREE_SPACE.BYTES)
  • For the UNDO tablespaces when the value of undospace_option is Y:
    (SUM(DBA_FREE_SPACE.BYTES) + SUM(DBA_UNDO_EXTENTS.BYTES) WHERE STATUS='EXPIRED')
Free Extents
(FREE_EXTENTS)
Number of free extents #2AVGdoubleNoAll
  • For Oracle that does not have any locally managed temporary tablespaces:
    COUNT(DBA_FREE_SPACE)
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option is Y:
    COUNT(DBA_FREE_SPACE) + SUM((DBA_TEMP_FILES.BYTES - V$TEMP_EXTENT_POOL.BYTES_USED) / V$TEMP_EXTENT_MAP.BYTES)
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option is N:
    COUNT(DBA_FREE_SPACE) + COUNT(V$TEMP_SPACE_HEADER)
Free Mbytes
(FREE_BYTES)
Size of free space in megabytes #2AVGdoubleNoAll
  • For Oracle that does not have any locally managed temporary tablespaces or has UNDO tablespaces when the value of undospace_option is N:
    SUM(DBA_FREE_SPACE.BYTES) / (1024 * 1024)
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option is Y:
    (SUM(DBA_FREE_SPACE.BYTES) + SUM(DBA_TEMP_FILES.BYTES - V$TEMP_EXTENT_POOL.BYTES_USED)) / (1024 * 1024)
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option is N:
    (SUM(DBA_FREE_SPACE.BYTES) + SUM(V$TEMP_SPACE_HEADER.BYTES_FREE)) / (1024 * 1024)
  • For the UNDO tablespaces when the value of undospace_option is Y:
    (SUM(DBA_FREE_SPACE.BYTES) + SUM(DBA_UNDO_EXTENTS.BYTES) WHERE STATUS='EXPIRED') / (1024 x 1024)
High Max Extent
(HIGH_MAX_EXTENTS)
Correct values cannot be collected in this field.
Number of segments whose PCT_MAX_EXTENTS value exceeds 90%. #2
HILOulongNoAll
  • For Oracle that does not have any locally managed temporary tablespaces:
    COUNT(DBA_SEGMENTS) where DBA_SEGMENTS.EXTENTS >0.9 * DBA_SEGMENTS.MAX_EXTENTS
  • For Oracle that has locally managed temporary tablespaces:
    COUNT(DBA_SEGMENTS) where DBA_SEGMENTS.EXTENTS > 0.9 * DBA_SEGMENTS.MAX_EXTENTS + COUNT(V$SORT_SEGMENT) where V$SORT_SEGMENTS.TOTAL_EXTENTS > 0.9 * V$SORT_SEGMENT.MAX_SIZE
I/O Ops/sec
(IO_RATE)
Number of I/O operations per second #2AVGdoubleNoAll(SUM(V$FILESTAT.PHYRDS) + SUM(V$FILESTAT.PHYWRTS)) / seconds in interval
Links
(LINKS)
This field is not supported.
Number of database links. #2
AVGlongNoNot supportedCOUNT(V$DBLINK)
Links In Tran
(LINKS_IN_TRAN)
This field is not supported.
Number of current database links in the transaction. #2
AVGlongNoNot supportedSUM(V$DBLINK.IN_TRANSACTION)
Links Logged On
(LINKS_LOGGED_ON)
This field is not supported. Number of database links currently logged on. #2AVGlongNoNot supportedSUM(V$DBLINK.LOGGED_ON)
Links Open Cursors
(LINKS_OPEN_CURSORS)
This field is not supported. Number of database links with open cursor. #2AVGlongNoNot supportedSUM(V$DBLINK.OPEN_CURSORS)
Log Files %
(PERCENT_LOG_FILES)
Percentage ratio of REDO log files to the LOG_FILES parameter value in the init.ora file #2
Correct values cannot be collected in this field (always 0).
AVGdoubleNoAll--
Log Mode
(LOG_MODE)
Archive log mode. Valid values are NOARCHIVE, LOG, and ARCHIVELOG. #1COPYstring(12)NoAllV$DATABASE.LOG_MODE
Mbytes
(BYTES)
Size of a database file in megabytes #2AVGdoubleNoAll
  • For Oracle that does not have any locally managed temporary tablespaces:
    SUM(DBA_DATA_FILES.BYTES) / (1024 * 1024)
  • For Oracle that has locally managed temporary tablespaces:
    (SUM(DBA_DATA_FILES.BYTES) + SUM(DBA_TEMP_FILES.BYTES)) / (1024 * 1024)
Next Alloc Fails
(NEXT_ALLOC_FAILS)
Correct values cannot be collected in this field.
Number of segments that exceed the maximum fragments permitted for NEXT_EXTENT.
For locally managed temporary tablespaces, always 0.#2
HILOulongNoAllCOUNT(DBA_SEGMENTS) where NEXT_EXTENT > MAX(FET$.LENGTH) * DB_BLOCK_SIZE
Overextended
(OVEREXTENDED)
Correct values cannot be collected in this field.
Number of segments with more than five extents. #2
HILOulongNoAll
  • For Oracle that does not have any locally managed temporary tablespaces:
    COUNT(DBA_SEGMENTS) where EXTENTS > 5
  • For Oracle that has locally managed temporary tablespaces:
    COUNT(DBA_SEGMENTS) where EXTENTS > 5 + COUNT(V$SORT_SEGMENT) where TOTAL_EXTENTS > 5
Physical Blocks Read
(PHYSICAL_BLOCKS_READ)
Number of physical block read operations #2AVGdoubleYesAllSUM(V$FILESTAT.PHYBLKRD)
Physical Blocks Written
(PHYSICAL_BLOCKS_WRITTEN)
Number of physical block write operations #2AVGdoubleYesAllSUM(V$FILESTAT.PHYBLKWRT)
Physical Reads
(PHYSICAL_READS)
Number of physical read operations that were completed #2AVGdoubleYesAllSUM(V$FILESTAT.PHYRDS)
Physical Writes
(PHYSICAL_WRITES)
Number of physical write operations that were completed #2AVGdoubleYesAllSUM(V$FILESTAT.PHYWRTS)
Reads/sec
(READ_RATE)
Number of read operations per second #2AVGdoubleNoAllSUM(V$FILESTAT.PHYRDS) / seconds in interval
Record Time
(RECORD_TIME)
Collection termination time for the performance data stored in the record#1COPYtime_tNoAllRemote Monitor Collector
Record Type
(INPUT_RECORD_TYPE)
Record name (always PIDB)#1COPYstring(4)NoAllRemote Monitor Collector
Redo Files
(REDO_FILES)
Number of REDO log files #2AVGulongNoAllCOUNT(V$LOGFILE)
Rollback Segments
(ROLLBACK_SEGMENTS)
Number of rollback segments #2AVGulongNoAllCOUNT(V$ROLLNAME)
Rollback Segments Hit %
(ROLLBACK_SEGMENTS_HIT_PERCENTAGE)
Rate at which the rollback segment header was obtained without waiting #2AVGdoubleNoAll((SUM(V$ROLLSTAT.GETS) - SUM(V$ROLLSTAT.WAITS)) / SUM(V$ROLLSTAT.GETS)) * 100
Rollback Segments Trans
(ROLLBACK_SEGMENTS_TRANS)
Number of currently active transactions #2AVGlongNoAllSUM(V$ROLLSTAT.XACTS)
Segments
(SEGMENTS)
Correct values cannot be collected in this field.
Number of segments. #2
AVGulongNoAll
  • For Oracle that does not have any locally managed temporary tablespaces:
    COUNT(DBA_SEGMENTS)
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option is Y:
    COUNT(DBA_SEGMENTS) + COUNT(DBA_TEMP_FILES GROUP BY TABLESPACE_NAME)
  • For Oracle that has locally managed temporary tablespaces when the value of localtemp_option is N:
    COUNT(DBA_SEGMENTS) + COUNT(V$SORT_SEGMENT)
Sort Segments
(SORT_SEGMENTS)
Correct values cannot be collected in this field.
Number of sort segments. #2
AVGulongNoAllCOUNT(V$SORT_SEGMENT)
Sorting Users
(SORTING_USERS)
Number of active users for the current sort segment #2AVGlongNoAllSUM(V$SORT_SEGMENT.CURRENT_USERS)
Start Time
(START_TIME)
Collection start time for the performance data stored in the record#1COPYtime_tNoAllRemote Monitor Collector
Tablespaces
(TABLESPACES)
Number of tablespaces #2AVGdoubleNoAllCOUNT(DBA_TABLESPACES)
Used Change
(USED_CHANGE)
Change to the used space in bytes#2AVGdoubleNoAll
  • For Oracle that does not have any UNDO tablespaces or has UNDO tablespaces when the value of undospace_option is N:
SUM(sm$ts_uses.bytes)
  • For the UNDO tablespaces when the value of undospace_option is Y:
    (SUM(sm$ts_uses.bytes) - SUM(DBA_UNDO_EXTENTS.BYTES) WHERE STATUS='EXPIRED')
Used Mbytes
(USED_BYTES)
Size of used area in megabytes.
If the monitoring target is locally managed temporary tablespaces, performance data are not collected. #2
AVGdoubleNoAll
  • For Oracle that does not have any UNDO tablespaces or has UNDO tablespaces when the value of undospace_option is N:
    SUM(sm$ts_used.bytes) / (1024 * 1024)
  • For the UNDO tablespaces when the value of undospace_option is Y:
    (SUM(sm$ts_used.bytes) - SUM(DBA_UNDO_EXTENTS.BYTES) WHERE STATUS='EXPIRED') / (1024 * 1024)
VA DeviceID
(VADEVICEID)
Device ID of virtual agent.--string(256)NoAllRemote Monitor Collector
Write %
(WRITE_PERCENTAGE)
Percentage ratio of write operations #2AVGdoubleNoAll(SUM(V$FILESTAT.PHYWRTS) / (SUM(V$FILESTAT.PHYRDS) + SUM(V$FILESTAT.PHYWRTS))) * 100
Writes/sec
(WRITES_RATE)
Number of write operations per second#2AVGdoubleNoAllSUM(V$FILESTAT.PHYWRTS) / seconds in interval