Hitachi

JP1 Version 12 JP1/Performance Management - Remote Monitor for Oracle Description, User's Guide and Reference

Database (PD_PDDB)

Organization of this page

Function

The Database (PD_PDDB) record stores performance data, taken at a specific point in time, indicating the following:

Notes

If the monitoring target is Oracle Database 12c Release 2 or later in a CDB configuration, some fields in this record include information of performance data for the PDBs that are being monitored and common performance data for database instances. For details, see the descriptions of each field.

To Page Top

Default and changeable values

Item

Default value

Changeable

Collection Interval

3600

Y

Collection Offset

20

Y

Log

No

Y

LOGIF

(Blank)

Y

Over 10 Sec Collection Time

No

N

To Page Top

ODBC key fields

None

To Page Top

Lifetime

From the creation to the deletion of a database

To Page Top

Record size

To Page Top

Fields

PFM - View name

(PFM - Manager name)

Description

Summary

Format

Delta

Supported version

Data source

Archive Change #

(ARCHIVE_CHANGE_NUM)

Last archived system change number (SCN)#10

--

double

No

All

V$DATABASE.ARCHIVE_CHANGE#

Blocks

(BLOCKS)

Size of tablespace in Oracle blocks#8

--

double

No

All

  • 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#10

--

double

No

All

V$DATABASE.CHECKPOINT_CHANGE#

Created

(CREATED)

Creation date#10

--

string(20)

No

All

V$DATABASE.CREATED

Datafiles

(DATAFILES)

Number of data files#8

--

ulong

No

All

  • For Oracle that does not have any locally managed temporary tablespaces:

    COUNT(V$DATAFILE)

    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#8

--

double

No

All

  • 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#10

--

string(9)

No

All

V$DATABASE.NAME

Extents

(EXTENTS)

Correct values cannot be collected in this field.

Number of extents.

--

double

No

All

  • 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#8

--

double

No

All

  • 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$SORT_SEGMENT.USED_EXTENTS * AVG(V$TEMP_EXTENT_MAP.BYTES))) / (DBA_DATA_FILES.BYTES + DBA_TEMP_FILES.BYTES)) * 100

  • 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)) / (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 Extents

(FREE_EXTENTS)

Number of free extents#8

--

double

No

All

  • 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_MAP.BYTES) - V$SORT_SEGMENT.USED_EXTENTS

  • 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#8

--

double

No

All

  • 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$SORT_SEGMENT.USED_EXTENTS * AVG(V$TEMP_EXTENT_MAP.BYTES))) / (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 Extents

(HIGH_MAX_EXTENTS)

Correct values cannot be collected in this field.

Number of segments whose PCT_MAX_EXTENTS value exceeds 90%.

--

ulong

No

All

  • 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

Links

(LINKS)

This field is not supported.

Number of database links

--

ulong

No

Not supported

COUNT(V$DBLINK)

Links In Tran

(LINKS_IN_TRAN)

This field is not supported.

Number of current database links in the transaction.

--

ulong

No

Not supported

SUM(V$DBLINK.IN_TRANSACTION)

Links Logged On

(LINKS_LOGGED_ON)

This field is not supported.

Number of database links currently logged in.

--

ulong

No

Not supported

SUM(V$DBLINK.LOGGED_ON)

Links Open Cursors

(LINKS_OPEN_CURSORS)

This field is not supported.

Number of database links with open cursor.

--

ulong

No

Not supported

SUM(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.

Correct values cannot be collected in this field (always 0).

--

double

No

All

--

Log Mode

(LOG_MODE)

Archive log mode. Valid values are NOARCHIVE, LOG, and ARCHIVELOG.#10

--

string(12)

No

All

V$DATABASE.LOG_MODE

Mbytes

(BYTES)

Size of a database file in megabytes#8

--

double

No

All

  • 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.

--

ulong

No

All

COUNT(DBA_SEGMENTS) where NEXT_EXTENT > MAX(FETS$.LENGTH) * DB_BLOCK_SIZE

Overextended

(OVEREXTENDED)

Correct values cannot be collected in this field.

Number of segments with more than five extents.

--

ulong

No

All

  • For Oracle that does not have any locally managed temporary tablespaces:

    COUNT(DBA_SEGMENTS) where EXTENTS > 5

  • For Oracle that has locally managed temporary tables spaces:

    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#8

--

double

No

All

SUM(V$FILESTAT.PHYBLKRD)

Physical Blocks Written

(PHYSICAL_BLOCKS_WRITTEN)

Number of physical block write operations#8

--

double

No

All

SUM(V$FILESTAT.PHYBLKWRT)

Physical Reads

(PHYSICAL_READS)

Number of physical read operations that were completed#8

--

double

No

All

SUM(V$FILESTAT.PHYRDS)

Physical Writes

(PHYSICAL_WRITES)

Number of physical write operations that were completed#8

--

double

No

All

SUM(V$FILESTAT.PHYWRTS)

Record Time

(RECORD_TIME)

Collection termination time for the performance data stored in the record

--

time_t

No

All

Remote Monitor Collector

Record Type

(INPUT_RECORD_TYPE)

Record name (always PDDB)

--

string(4)

No

All

Remote Monitor Collector

Redo Files

(REDO_FILES)

Number of REDO log files#10

--

ulong

No

All

COUNT(V$LOGFILE)

Rollback Segments

(ROLLBACK_SEGMENTS)

Number of rollback segments#8

--

ulong

No

All

COUNT(V$ROLLNAME)

Rollback Segments Hit %

(ROLLBACK_SEGMENTS_HIT_PERCENTAGE)

Rate at which the rollback segment header was obtained without waiting#8

--

double

No

All

((SUM(V$ROLLSTAT.GETS) - SUM(V$ROLLSTAT.WAITS)) / SUM(V$ROLLSTAT.GETS)) * 100

Rollback Segments Trans

(ROLLBACK_SEGMENTS_TRANS)

Number of currently active transactions#8

--

ulong

No

All

SUM(V$ROLLSTAT.XACTS)

Segments

(SEGMENTS)

Correct values cannot be collected in this field.

Number of segments.

--

ulong

No

All

  • 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.

--

ulong

No

All

COUNT(V$SORT_SEGMENT)

Sorting Users

(SORTING_USERS)

Number of active users for the current sort segment#8

--

long

No

All

SUM(V$SORT_SEGMENT.CURRENT_USERS)

Start Time

(START_TIME)

Collection start time for the performance data stored in the record

--

time_t

No

All

Remote Monitor Collector

Tablespaces

(TABLESPACES)

Number of tablespaces#8

--

double

No

All

COUNT(DBA_TABLESPACES)

Used Mbytes

(USED_BYTES)

Size of used area in megabytes.

If the monitoring target is locally managed temporary tablespaces, performance data are not collected. #8

--

double

No

All

  • 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)

No

All

Remote Monitor Collector

Write %

(WRITE_PERCENTAGE)

Percentage ratio of write operations#8

--

double

No

All

(SUM(V$FILESTAT.PHYWRTS) / (SUM(V$FILESTAT.PHYRDS) + SUM(V$FILESTAT.PHYWRTS))) * 100
#8

Displays performance data for the PDBs that are being monitored.

#10

Displays common performance data for database instances.

To Page Top

Copyright (C) 2019, 2020, Hitachi, Ltd.

Copyright (C) 2019, 2020, Hitachi Solutions, Ltd.