Oracle
SNMP Support Reference Guide
Release 8.0.5 A64404-01 |
|
This appendix provides information to help you interpret individual variables in the private Oracle Database MIB. Specifically, it includes sections covering SNMP variables in the following tables:
For information about the database instance performance ratios
listed in this appendix, see the section, "Most Useful Database Instance
Performance Ratios," in Chapter 3, "Designing Management Applications Based
on Oracle MIBs." For more information about the Oracle database server
and specific topics covered in this appendix, see the ORACLE Server
Reference, the Oracle Server Application Developer's Guide,
and the Oracle Server Concepts Manual specific to your system.
The oraDbSysTable contains various measurements used to support
database activities of current sessions on the managed node. This information
has been found to be particularly useful for monitoring global database
instance performance.
Each entry in the oraDbSysTable represents a current database
instance on the node. Each entry is indexed by rdbmsDb Index. However,
because rdbmsDbTable contains an entry for each database configured for
SNMP support, whether or not it is actively open, there may or may not
be a one-to-one correspondence between entries in the two tables at any
point in time. (For details about this index, see Appendix D, "Interpreting
Variables of the Public RDBMS MIB.") The value of each oraDbSysTable variable
is retrieved directly from a corresponding field in the V$SYSSTAT table
for that database instance on the managed node.
In many cases, a variable is meaningful only when considered in conjunction with other variables, often as a ratio. For more information on the ratios mentioned in the following variable descriptions, and for their significance in tuning the Oracle database server, see the Oracle Tuning Guide specific to your system.
Table A-1 lists each variable
of the oraDbSysTable and its corresponding object ID.
A description of each of these variables follows.
Counter32
read-only
This variable is retrieved directly from the value for CONSISTENT
CHANGES in V$SYSSTAT. It is incremented each time that a consistent get
operation cannot accept the block in its current state, owing to a change
made since the query consistency point.
oraDbSysConsistentChanges is used to calculate the following database instance performance ratio: the consistent change ratio.
two or more orders of magnitude less than the value of oraDbSysConsistentGets
important
oraDbSysConsistentGets
Counter32
read-only
This variable is retrieved directly from the value for CONSISTENT
GETS in V$SYSSTAT. This value is incremented each time that the Oracle
Buffer Manager is requested to locate a database block as part of a read
consistent operation (a query). Only if the requested block is not found
in Oracle's Buffer Cache will consistent get result in physical I/O.
oraDbSysConsistentGets is used to calculate the following database instance performance ratios: block get rate, block visits per transaction, cache hit ratio, changed block ratio, and the consistent change ratio.
0 - 4294967295; can increment at several thousand per second in the largest systems, so this variable is definitely a "wraparound" candidate
very important
oraDbSysUserCommits, oraDbSysConsistentChanges, oraDbSysPhysReads, oraDbSysDbBlockChanges, oraDbSysDbBlockGets
Counter32
read-only
This variable is retrieved directly from the value for DB
BLOCK CHANGES in V$SYSSTAT. It is incremented each time that a database
block is in memory to create a "dirty block." This statistic is a rough
indication of total database work.
oraDbSysDbBlockChanges is used to calculate the following database instance performance ratios: block changes per transaction, and the changed block ratio.
0 - 4294967295; can increment at several thousand per second in the largest systems, so this variable is definitely a "wraparound" candidate; value tends to be lower than that for oraDbSysConsistentGets
one of the lesser measures of work rate or throughput
oraDbSysBlockGets, oraDbSysConsistentGets, oraDbSysUserCalls
Counter32
read-only
This variable is retrieved directly from the value for DB
BLOCK GETS in V$SYSSTAT. It is incremented each time the buffer manager
is asked for a database block in current mode, that is, regardless of read
consistency. This mode is used to locate blocks which are to be updated,
and also to access control information such as segment headers. Only if
the requested block is not found in Oracle's Buffer cache will a db block
get result in physical I/O.
oraDbSysBlockGets is used to calculate the following database instance performance ratios: block get rate, block visits per transaction, the cache hit ratio, and the changed block ratio.
0 - 4294967295; can increment at several thousand per second in the largest systems, so this variable is definitely a "wraparound" candidate.
very important
oraDbSysDbBlockChanges, oraDbSysConsistentGets, oraDbSysPhysReads, oraDbSysUserCalls
Counter32
read-only
The "free buffer inspected" parameter from V$SYSTAT.
0 - 4294967295
This indicates the number of reusable buffers skipped over from the end of the Least Recently Used (LRU) queue in order to find a buffer. The difference between this and "dirty buffers inspected" equals the number of buffers that could not be used beause they were busy or needed to be written after rapid aging out. They may have a user, a waiter, or be being read or written.
oraDbSysFreeBufferRequested
Counter32
read-only
The "free buffer requested" parameter from V$SYSTAT.
0 - 4294967295
This is the count of the number of times a free buffer was requested to create or load a block.
oraDbSysFreeBufferInspected
Counter32
read-only
This variable is retrieved directly from the value for PARSE
COUNT(total) in V$SYSSTAT. It counts the number of parse calls received
by the RDBMS from both connected applications and recursive calls. In Oracle7
and Oracle8, parse calls are frequently satisfied by reference to the Shared
SQL area and this statistic does not measure the number of parses
which have taken place, only the number of calls.
oraDbSysParseCount is used to calculate the following database instance performance ratio: user calls per parse.
0 - 4294967295; unlikely to increment at a rate greater than a few hundred per second
important
oraDbLibraryCacheTable variables, oraDbSysUserCalls
Counter32
read-only
This variable is retrieved directly from the value for PHYSICAL
READS in V$SYSSTAT. It increments each time a DB Block is retrieved from
the disk subsystem, but is not necessarily equal to the number of I/O requests
made to the host operating system, as multiblock I/O may be used. A physical
read can be viewed as an SGA Buffer Cache miss, hampering throughput (contention
induction) and CPU (code pathlength) processing.
oraDbSysPhysReads is used to calculate the following database instance performance ratio: the cache hit ratio.
0 - 4294967295; unlikely to increment at a rate greater than a few hundred per second
very important
oraDbSysConsistentGets, oraDbSysDbBlockGets
Counter32
read-only
This variable is retrieved directly from the value for PHYSICAL WRITES in V$SYSSTAT. It increments each time a DB Block is written to disk. All writes are performed either by DBWR or LGWR, two background processes responsible for getting Blocks out of the SGA and back on disk.
0 - 4294967295; can increment at several thousand per second in the largest systems, so this variable is definitely a "wraparound" candidate
important
oraDbSysRedo family
Counter32
read-only
This variable is retrieved directly from the value for REDO
ENTRIES in V$SYSSTAT. Each time that REDO data is copied into the log buffer,
this counter is incremented. The value varies in relation to how many changes
are being made.
oraDbSysRedoEntries is used to calculate the following database instance performance ratio: redo log space wait ratio.
0 - 4294967295; can increment at several thousand per second in the largest systems, so this variable is definitely a "wraparound" candidate.
important
oraDbSysRedoLogSpaceRequests, oraDbSysRedoSyncWrites, oraDbSysUserCommits
Counter32
read-only
This variable is retrieved directly from the value for REDO
LOG SPACE REQUESTS in V$SYSSTAT. This counts the number of times that a
server process had to wait to acquire an entry in the redo log.
oraDbSysRedoLogSpaceRequests is used to calculate the following database instance performance ratios: redo log space wait ratio.
0 - 10,000,000 (a value of 0 is ideal, but an increment of 1 or 2 per second might be acceptable)
important
oraDbSysRedoEntries
Counter32
read-only
This variable is retrieved directly from the value for REDO SYNC WRITES parameter in V$SYSSTAT. It increments when a process waits for a ReDo write to complete, typically as a result of a Commit.
0 - 10,000,000 (typically increments at less than 1 per second)
less important
oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSysUserCommits
Counter32
read-only
This variable is retrieved directly from the value for SORTS
DISKS in V$SYSSTAT. It increments for each sort which has to allocate disk
work space.
oraDbSysSortsDisks is used to calculate the following database instance performance ratio: the sort overflow ratio.
for OLTP it should be 0
important
oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSyncWrites, oraDbSysSortsMemory, oraDbUserCommits
Counter32
read-only
This variable is retrieved directly from the value for SORTS
MEMORY in V$SYSSTAT. It is incremented for each sort which does not require
the allocation of work space on disk.
oraDbSysSortsMemory is used to calculate the following database instance performance ratio: the sort overflow ratio.
0 - 4294967295; unlikely to increment faster than tens per second, except in the largest systems
important
oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSyncWrites, oraDbSysSortsDisks, oraDbUserCommits
Counter32
read-only
This variable is retrieved directly from the value for SORTS ROWS in V$SYSSTAT. It is incremented for each sort, regardless of whether the allocation of disk space was required.
0 - 4294967295
important
oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSyncWrites, oraDbSysSortsMemory, oraDbSortsDisks, oraDbUserCommits
Counter32
read-only
This variable is retrieved directly from the value for TABLE
FETCH BY ROWID in V$SYSSTAT. This counter increments when a row is fetched
using a RowID (usually recovered from an index). It should be noted that
this statistic records all rows visited by the database engine, whether
or not they were return to the application.
oraDbSysTableFetchRowid is used to calculate the following database instance performance ratios: the continued row ratio and the row source ratio.
0 - 4294967295; can increment at several tens of thousands per second in the largest systems, so this variable is definitely a "wraparound" candidate
important
oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSyncWrites, oraDbSysSortsDisks, oraDbSysTableFetchContinuedRow, oraDbSysTableScanRows, oraDbUserCommits
Counter32
read-only
This variable is retrieved directly from the value for TABLE
FETCH CONTINUED ROW in V$SYSSTAT. It may be incremented either because
a row has been encountered which spans more than a DB block, or because
a migrated row has been retrieved by rowid.
oraDbSysTableFetchContinuedRow is used to calculate the following database instance performance ratio: the continued row ratio.
close to 0 unless long LONG values are in use.
important
oraDbSysTable FetchRowid, oraDbSysTableScanRows
Counter32
read-only
This variable is retrieved directly from the value for TABLE SCAN BLOCKS GOTTEN in V$SYSSTAT. During scanning operations, the RDBMS retrieves each row sequentially. This counter increments each time the RDBMS steps into a new DB Block during the scan. oraDbSysTableScanBlocks indicates how many DB Blocks were Consistently Read from the Buffer Cache for the purpose of scanning. Comparing this to the value of oraDbSysConsistentGets suggests how much Consistent Read activity can be attributed to scanning.
0 - 3 billion; in OLTP very low; in DSS applications, a few thousand per second, so this variable is definitely a "wraparound" candidate.
important
oraDbSysConsistentGets
Counter32
read-only
This variable is retrieved directly from the value for TABLE
SCAN ROWS GOTTEN in V$SYSSTAT. This counter increases each time a row is
processed during a Scan operation. Note that the row need not be parsed
back to the calling application in order for it to be counted here.
oraDbSysTableScanRows is used to calculate the following database instance performance ratio: the row source ratio, and the continued row ratio.
0 - 100,000,000 per day
important
oraDbSysTableFetchContinuedRow, oraDbSysTableFetchRowid
Counter32
read-only
This variable is retrieved directly from the value for TABLE SCANS LONG TABLES in V$SYSSTAT. Long tables are those whose number of data blocks below the high water mark exceeds the value of the initialization parameter SMALL _TABLE_THRESHOLD. DB Blocks visited in Long Full Table Scans are not prompted in the LRS List.
in OLTP, typically 0
important
oraDbSysTableScansShort
Counter32
read-only
This variable is retrieved directly from the value for TABLE SCANS SHORT TABLES in V$SYSSTAT. Short tables can be defined as tables that do not meet the long table criteria, as defined by oraDbSysTableScansLong. Short tables are those whose number of data blocks above the low water mark is low than the value of the initialization parameter LONG_TABLE_THRESHOLD.
0 - 4294967295; can increment at a few hundred per second
important
oraDbSysTableScansLong
Counter32
read-only
This variable is retrieved directly from the value for USER
CALLS in V$SYSSTAT. A user call is a Parse, an Execute, or a Fetch. With
the Oracle server's deferred and bundled execution capabilities, this statistic
will not necessarily correlate well with client/server message traffic.
oraDbSysUserCalls is used to calculate the following database instance performance ratios: block changes per transaction, the call rate, calls per transaction, the recursive-to-user call ratio, the user call rate, and user calls per parse.
0 - 10,000,000 per day
very important
oraDbSysDbBlockChanges, oraDbSysParseCount, oraDbSysRecursiveCalls, oraDbSysUserCommits
Counter32
read-only
This variable is retrieved directly from the value for USER
COMMITS in V$SYSSTAT. It records the number of database transactions successfully
completed and records the number of aborted database transactions.
oraDbSysUserCommits is used to calculate the following database instance performance ratios: block visits per transaction, calls per transaction, and the transaction rate.
In most real world applications, the value is less the 1 per second.
In OLTP, very important
oraDbBlockGets, oraDbSysConsistentGets, oraDbUserCalls
Counter32
read-only
This variable is retrieved directly from the value for USER
ROLLBACKS in V$SYSSTAT. It records the number of aborted database transactions.
oraDbSysUserRollbacks is used to calculate the following database instance performance ratio: the user rollback ratio.
0 - 4294967295
important
oraDbSysUserCommits
Counter32
read-only
This variable is retrieved directly from the value for WRITE REQUESTS in V$SYSSTAT. It records the number of write requests made to the host operating system.
0 - 4294967295; unlikely to increment at a rate greater than a few hundred per second
less important
oraDbSysPhysWrites
The oraDbTablespaceTable contains information on tablespaces
within a current Oracle database instance. A tablespace is a logical portion
of an Oracle database used to allocate storage for table and index data.
In a production environment, tables can fill up as transaction activity
mounts. Monitoring tablespace activity is important to avoid exhausting
the tablespaces and causing spaces.
Each entry in the oraDbTablespaceTable represents a given
tablespace within a current database instance. Each entry is indexed first
by rdbmsDbIndex, and then oraDbTablespaceIndex; thus, there will normally
be a one-to-many relationship between an rdbmsDbTable entry and corresponding
oraDbTablespaceTable entries. (For details about rdbmsDbIndex, see Appendix
D, "Interpreting Variables of the Public RDBMS MIB.") The current value
of each oraDbTablespaceTable variable is retrieved directly from a join
of DBA_TABLESPACES, DBA_DATA_FILES, and DBA_FREE_SPACE selecting for the
tablespace in question.
Table A-2 lists each of these
variables and its corresponding object.
Variable Name | Object ID |
---|---|
oraDbTablespaceIndex | 1.3.6.1.4.1.111.4.1.2.1.1 |
oraDbTablespaceName | 1.3.6.1.4.1.111.4.1.2.1.2 |
oraDbTablespaceSizeAllocated | 1.3.6.1.4.1.111.4.1.2.1.3 |
oraDbTablespaceSizeUsed | 1.3.6.1.4.1.111.4.1.2.1.4 |
oraDbTablespaceState | 1.3.6.1.4.1.111.4.1.2.1.5 |
oraDbTablespaceLargestAvailableChunk | 1.3.6.1.4.1.111.4.1.2.1.6 |
INTEGER (1..2147483647)
not-accessible
A numeric index, unique among tablespaces within a single Oracle database instance. This index is assigned to these tablespaces based on the alphabetical order of their names. On most platforms, a hard file limit of 1023 database files exists, so this number is the practical upper limit. Although some administrators prefer a one-to-one relationship between tablespaces and database files, multiple files can comprise a tablespace.
1 - 1,000
very important
oraDbTablespaceName, rdbmsDbIndex
DisplayString
read-only
Indicates the name of this tablespace, as retrieved from the value for TABLESPACE-NAME in DBA_TABLESPACES.
0 - 30 characters
important
oraDbTablespaceIndex
Integer32
read-only
Indicates the amount of disk space (in kilobytes) allocated for this tablespace. This is the sum of the sizes of the data files associated with the tablespace.
0 - 10 gigabytes
important
oraDbTablespaceSizeUsed
Integer32
read-only
Indicates the amount of disk space (in kilobytes) which is actually in use for storing data. This is the difference between the sum of the size of the datafiles associated with the tablespace and the sum of the size of the free spaces associated with the tablespace.
0 - value of oraDbTablespaceSizeAllocated
Significance
Very important
oraDbTablespaceSizeAllocated
INTEGER (1) online (2) offline, or (3) invalid
read-only
Indicates the current accessibility of this tablespace, as retrieved from the value for STATUS in DBA_TABLESPACES. If a tablespace is offline (2), then SQL statements cannot reference objects contained in the tablespace. An invalid (3) tablespace is one that has been dropped.
enumerated 1 - 3
very important
none
Integer32
read-only
Indicates the size (in kilobytes) of the largest contiguous set of free data blocks in the tablespace. This variable is retrieved directly from the value for NEXT_EXTENT in DBA_TABLES. If there is any object in the tablespace whose NEXT_EXTENT value is greater than the layout chunk, that object will be unable to expand. This might cause a major problem, or it might not matter.
Less than or equal to (oraDbTablespaceSizeAllocated minus oraDbTablespaceSizeUsed)
important
oraDbTablespaceSizeAllocated, oraDbTablespaceSizeUsed
oraDbDataFileTable contains information on the data files
within a current database instance on the managed node. A data file denotes
an area of disk allocated for database data. Monitoring data files is important
for two reasons; first, to determine whether space in files is being exhausted,
and second to determine O/S response time in accessing data on disk, especially
on platforms where there is no other way to measure disk queue length.
Each entry in the oraDbDataFileTable represents a given data
file within a current database instance on the node. Each entry is indexed
first by rdbmsDbIndex, and then by oraDbDataFileIndex; thus, there will
normally be a one-to-many relationship between an rdbmsDbTable entry and
corresponding oraDbDataFileTable entries. (For details about rdbmsDbIndex,
see Appendix E, "Interpreting Variables of the Public RDBMS MIB.") The
current value of each oraDbDataFileTable variable is retrieved directly
from a corresponding field in either V$FILESTAT or DBA_DATA_FILES for that
data file within that database instance.
Table A-3 lists each variable of the oraDbDataFileTable and its corresponding object ID
Variable Name | Object ID |
---|---|
oraDbDataFileIndex | 1.3.6.1.4.1.111.4.1.3.1.1 |
oraDbDataFileName | 1.3.6.1.4.1.111.4.1.3.1.2 |
oraDbDataFileSizeAllocated | 1.3.6.1.4.1.111.4.1.3.1.3 |
oraDbDataFileDiskReads | 1.3.6.1.4.1.111.4.1.3.1.4 |
oraDbDataFileDiskWrites | 1.3.6.1.4.1.111.4.1.3.1.5 |
oraDbDataFileDiskReadBlocks | 1.3.6.1.4.1.111.4.1.3.1.6 |
oraDbDataFileDiskWrittenBlocks | 1.3.6.1.4.1.111.4.1.3.1.7 |
oraDbDataFileDiskReadTimeTicks | 1.3.6.1.4.1.111.4.1.3.1.8 |
oraDbDataFileDiskWriteTimeTicks | 1.3.6.1.4.1.111.4.1.3.1.9 |
INTEGER (1..214748364)
not-accessible
A numeric index, unique among data files associated with a single tablespace. The value of oraDbDataFileIndex is retrieved directly from the value of FILE_ID in DBA_DATA_FILES. For a small database, a tablespace typically contains 1 data file; for a large database (80 Gbytes), a tablespace typically contains 200 datafiles in a tablespace. The upper limit is hard coded as 1023.
1 - 400
very important
rdbmsDbIndex
DisplayString
read-only
Indicates the fully-qualified name of this data file. The value is retrieved from the value of FILE_NAME in DBA_DATA_FILES.
10 - 100 characters; about 15 for raw partitions.
important
oraDbTablespaceName, because the file is a member of a tablespace.
Integer32
read-only
Indicates the allocated size (in kilobytes) of this data file, as retrieved from the value of BYTES in DBA_DATA_FILES. Indicates how much space has been used so far. Monitoring this variable is very important, because running out of space can require taking the database down, depending on which table this file supports. Normally, however, adding another data file to the Tablespace solves the problem.
100 Mbytes - 2 Gbytes
very important
none
Counter32
read-only
Indicates the total number of reads issued against this data file since database instance startup, as retrieved from the value of PHYRDS in V$FILESTAT.
10,000 - 100,000 or much more, depending on the life of the instance
less important
oraDbDataFileDiskReadBlocks, oraDbDataFileDiskReadTimeTicks
Counter32
read-only
Indicates the total number of writes issued against this data file since database instance startup, as retrieved from the value of PHYWRTS in V$FILESTAT.
10,000 - 100,000 or much more, depending on the life of the instance
less important
oraDbDataFileDiskWriteBlocks, oraDbDataFileDiskWriteTimeTicks
Counter32
read-only
Indicates the total number of physical blocks read from this data file since database instance startup. The value is retrieved from the value of PHYBLKRD in V$FILESTAT. The number of bytes read can be determined by multiplying this value by the value of oraDbConfigDbBlockSize, typically 2Kbytes.
10,000 - 100,000 or much more, depending on the life of the instance
important
oraDbConfigDbBlockSize, oraDbDataFileDiskReads
Counter32
read-only
Indicates the total number of physical blocks written to this data file since database instance startup. The value is retrieved from the value of PHYBLKWRT in V$FILESTAT. The number of bytes written can be determined by multiplying this value by the value of the INIT.ORA parameter, DB_BLOCK_SIZE, typically 2Kbytes.
10,000 - 100,000 or much more, depending on the life of the instance
important
oraDbDataFileDiskWrites
Counter32
read-only
Indicates the time spent writing to this data file since
database instance startup IF the INIT.ORA parameter TIMED_STATISTICS is
TRUE. (The unit of measurement on a UNIX platform is time ticks of 10 ms.)
If TIMED_STATISTICS is FALSE, then a value of 0 is returned. The value
of oraDbDataFileDiskReadTimeTicks is retrieved from the value of READTIM
in V$FILESTAT.
Because knowing how long it takes to perform I/O per block is the important statistic, knowing this aggregate measure is not very significant. If the average time to do reads is generally exceeding a couple of ticks, then an I/O sybsystem response time problem exists. Most high performance disk drives can easily locate any sector on the disk in 10 ms.
0 - 4294967295
less important
oraDbDataFileDiskReads
Counter32
read-only
Indicates the time spent writing to this data file since
database instance startup IF the INIT.ORA parameter TIMED_STATISTICS is
TRUE. (The unit of measurement on a UNIX platform is time ticks of 10 ms.)
If TIMED_STATISTICS is FALSE, then a value of 0 is returned. The value
of oraDbDataFileDiskWriteTimeTicks is retrieved from the value of WRITETIM
in V$FILESTAT.
Because knowing how long it takes to perform I/O per block is the important statistic, knowing this aggregate measure is not very significant. If the average time to do writes is generally exceeding three ticks on non-mirrored data, then an I/O subsystem response time problem exists. Most high performance disk drives can easily perform a write in 15 ms.
Can wrap around the upper limit of 2147483647 in about three days of continuous instance operation for a busy database
less important
oraDbDataFileDiskWrites
The oraDbLibraryCacheTable contains variables measuring library
cache activity since the most recent database instance startup. A library
cache is a memory structure containing shared SQL and PL/SQL areas. Monitoring
the library cache is important to determine whether it is necessary to
resize Oracle's shared pool.
Each entry in the oraDbLibraryCacheTable represents a given
library cache within a current database instance on the node. Each entry
is indexed first by rdbmsDbIndex, and then by oraDbLibraryCacheIndex; thus,
there will normally be a one-to-many relationship between an rdbmsDbTable
entry and corresponding oraDbLibraryCacheTable entries. (For details about
rdbmsDbIndex, see Appendix E, "Interpreting Variables of the Public RDBMS
MIB.") The current value of each oraDbLibraryCacheTable variable is retrieved
directly from a corresponding field in the V$LIBRARYCACHE table for that
library cache within that database instance.
Table A-4 lists each variable of the oraDbLibraryCacheTable and its corresponding object ID.
INTEGER (1..2147483647)
read-only
A unique integer for each row of the oraDbLibraryCacheTable.
8 (only)
very important
oraDbLibraryCacheNameSpace
DisplayString
read-only
Indicates the namespace of the V$LIBRARYCACHE table to which this row is related. The value is retrieved from the value of NAMESPACE in V$LIBRARYCACHE.
0 - 30 characters
important
oraDbLibraryCacheIndex
Counter32
read-only
Indicates the number of times, since database instance startup, that the system requests handles to library objects in this namespace. The value of oraDbLibraryCacheGets is retrieved from the value of GETS in V$LIBRARYCACHE.
0 - 500,00 per day
important
oraDbLibraryCacheGetHits
Counter32
read-only
Indicates the number of times, since database instance startup, that the handles are already allocated in the cache. The value of oraDbLibraryCacheGetHits is retrieved from the value of GETHITS in V$LIBRARYCACHE.
0 - 4294967295
less important
oraDbLibraryCacheGets
Counter32
read-only
Indicates the number of times, since database instance startup,
that the system issues pin requests in order to access objects in the cache.
The value of oraDbLibraryCachePins is retrieved from the value of PINS
in V$LIBRARYCACHE.
oraDbLibraryCachePins is used to calculate the following database instance performance ratio: library cache miss ratio.
0 - 4294967295
less important
oraDbLibraryPinHits, oraDbLibraryCacheReloads
Counter32
read-only
Indicates the number of times, since database instance startup, that the objects the system is pinning are already allocated and initialized in the cache. The value of oraDbLibraryCachePinHits is retrieved from the value of PINHITS in V$LIBRARYCACHE.
0 - 4294967295
important
oraDbLibraryCachePins
Counter32
read-only
Indicates the number of times, since database instance startup,
that library objects have to be reinitialized and reloaded with data because
they have been aged out or invalidated. The value of oraDbLibraryCacheReloads
is retrieved from the value of RELOADS in V$LIBRARYCACHE.
oraDbLibraryCacheReloads is used to calculate the following database instance performance ratio: library cache miss ratio.
0 - 4294967295
important
oraDbLibraryCachePins
Counter32
read-only
Indicates the number of times, since database instance startup, that non-persistent library objects (like shared SQL areas) have been invalidated. The value of oraDbLibraryCacheInvalidations is retrieved from the value of INVALIDATIONS in V$LIBRARYCACHE.
0 - 4294967295
less important
none
The LibraryCacheSum table tracks for all library caches in a database instance as a whole the same information that the LibraryCache table tracks for the caches individually. It is primarily a convenience.
Counter32
read-only
Indicates the number of times, since database instance startup, that the system requests handles to library objects in this namespace. The value of oraDbLibraryCacheSumGets is derived from the value of GETS in V$LIBRARYCACHE.
0 - 500,00 per day
important
oraDbLibraryCacheSumGetHits
Counter32
read-only
Indicates the number of times, since database instance startup, that the handles are already allocated in the cache. The value of oraDbLibraryCacheSumGetHits is derived from the value of GETHITS in V$LIBRARYCACHE.
0 - 4294967295
less important
oraDbLibraryCacheSumGets
Counter32
read-only
Indicates the number of times, since database instance startup,
that the system issues pin requests in order to access objects in the cache.
The value of oraDbLibraryCacheSumPins is derived from the value of PINS
in V$LIBRARYCACHE.
oraDbLibraryCacheSumPins is used to calculate the following database instance performance ratio: library cache miss ratio.
0 - 4294967295
less important
oraDbLibraryPinHits, oraDbLibraryCacheSumReloads
Counter32
read-only
Indicates the number of times, since database instance startup, that the objects the system is pinning are already allocated and initialized in the cache. The value of oraDbLibraryCacheSumPinHits is derived from the value of PINHITS in V$LIBRARYCACHE.
0 - 4294967295
important
oraDbLibraryCacheSumPins
Counter32
read-only
Indicates the number of times, since database instance startup,
that library objects have to be reinitialized and reloaded with data because
they have been aged out or invalidated. The value of oraDbLibraryCacheSumReloads
is derived from the value of RELOADS in V$LIBRARYCACHE.
oraDbLibraryCacheSumReloads is used to calculate the following database instance performance ratio: library cache miss ratio.
0 - 4294967295
important
oraDbLibraryCacheSumPins
Counter32
read-only
Indicates the number of times, since database instance startup, that non-persistent library objects (like shared SQL areas) have been invalidated. The value of oraDbLibraryCacheSumInvalidations is derived from the value of INVALIDATIONS in V$LIBRARYCACHE.
0 - 4294967295
less important
none
The oraDbSGATable contains storage allocation information
on the current System Global Area (SGA). The SGA is a common database storage
area for information that supports current sessions. As the values of its
attributes are configured in the INIT.ORA file, they do not change while
the database instance is running, thus, the SGA at this aggregate level
is not as important to monitor as other database structures.
Each entry in the oraDbSGATable represents the SGA for a
current database instance on the node. Each entry is indexed by rdbmsDbIndex.
However, because rdbmsDbTable contains an entry for each installed database
configured for SNMP support, whether or not it is actively open, there
may or may not be a one-to-one correspondence between entries in the two
tables at any point in time. (For details about rdbmsDbIndex, see Appendix
D, "Interpreting Variables of the Public RDBMS MIB.") The current value
of each oraDbSGATable variable is retrieved directly from a corresponding
field in the V$SGA table for that database instance.
Table A-6 lists each variable of the oraDbSGATable and its corresponding object ID.
INTEGER (1..2147483647)
read-only
The size in kilobytes of the fixed-size portion of the SGA as shown in V$SGASTAT.
1 - 2147483647
less important
oraDbSGAVariableSize
INTEGER (1..2147483647)
read-only
The size in kilobytes of the variable-size portion of the SGA as shown in V$SGASTAT.
1 - 2147483647
less important
oraDbSGAFixedSize
INTEGER (1..2147483647)
read-only
The number of database buffers allocated in the SGA as shown in V$SGASTAT.
4 Kbytes - 2 Gbytes
less important
oraDbSGARedoBuffers
INTEGER (1..2147483647)
read-write
The number of redo buffers allocated in the SGA as shown in V$SGASTAT.
1 - 2147483647
less important
oraDbSGADatabaseBuffers
The oraDbConfigTable contains variables whose values are
retrieved directly from the V$PARAMETER table. These parameters control
systemwide resources and can impact the values of variables in the rest
of this MIB. This table includes those initialization parameters that closely
relate to the task of monitoring database instance performance. By assigning
these parameters easy-to-use, fixed object IDs, it is easier to graph them
together with the dynamic performance values they impact. For more information
on these parameters, see the ORACLE Server Reference, and also the
ORACLE Server Concepts Manual specific to your system.
Each entry in the oraDbConfigTable is indexed by rdbmsDbIndex.
However, because rdbmsDbTable contains an entry for each installed database,
whether or not it is actively open, there may or may not be a one-to-one
correspondence between entries in the two tables at any point in time.
(For details about rdbmsDbIndex, see Appendix D, "Interpreting Variables
of the Public RDBMS MIB.")
Table A-7 lists each variable of the oraDbConfigTable and its corresponding object ID
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the DB_BLOCK_BUFFERS parameter of the INIT.ORA file. Indicates the number of database blocks cached in memory of the SGA. This parameter is the most significant determinant of the SGA size and database instance performance. The size of each block can be determined using oraDbConfigDbBlockSize.
1 - 2147483647
very important
oraDbConfigDbBlockSize
INTEGER (0..2147483647)
read-only
The value of this variable is retrieved directly from the DB_BLOCK_ CHECKPOINT_BATCH parameter of the INIT.ORA file. Indicates the maximum number of blocks that the DBWR process will write in one batch devoted to checkpoints.
0 - 2147483647
less important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the DB_BLOCK_SIZE parameter of the INIT.ORA file. Indicates the size, in bytes, of database blocks for this database. This information has significance for a number of other variables.
1024 - 8192 (O/S dependent)
important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the DB_FILE_SIMULTANEOUS_WRITES parameter of the INIT.ORA file. Indicates the number of simultaneous writes (batches) for each database file when written by DBWR.
1 - 2147483647
less important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the DB_FILE_MULTIBLOCK_READ_COUNT parameter of the INIT.ORA file. Indicates the maximum number of blocks read in one I/O operation during a sequential scan. The default is a function of the DB_BLOCK_BUFFERS and PROCESSES parameters in the INIT.ORA file.
Values in the range of 4 to 32 are reasonable. Actual maximums vary by operating system
important
oraDbConfigDbBlockBuffers, oraDbConfigDbProcesses
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the DISTRIBUTED_LOCK_TIMEOUT parameter of the INIT.ORA file. Indicates the amount of time (in seconds) that distributed transactions must wait for locked resources.
1 - 2147483647
important
none
INTEGER (0..2147483647)
read-only
The value of this variable is retrieved directly from the DISTRIBUTED_ RECOVERY_CONNECTION_HOLD_ TIME parameter of the INIT.ORA file. Indicates the length of time (in seconds) to hold a remote connection open after a distributed transaction fails, in hopes that communication will be restored without having to reestablish the connection. Any value above 1800 means the connection never closes.
1 - 1800
important
none
INTEGER (0..2147483647)
read-only
The value of this variable is retrieved directly from the DISTRIBUTED_ TRANSACTIONS parameter of the INIT.ORA file. Indicates the maximum number of distributed transactions in which this database can concurrently participate.
0 - 2147483647
none
oraDbConfigDistTransactions
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the LOG_ARCHIVE_BUFFER_SIZE parameter of the INIT.ORA file. Indicates the size of each archival buffer, in redo log blocks (operating system blocks). The default should be adequate for most applications.
1 - 2147483647
less important
oraDbConfigLogArchiveBuffers, oraDbConfigLogBuffer
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the LOG_ARCHIVE_BUFFERS parameter from the INIT.ORA file. Indicates the number of buffers allocated for archiving.
1 - 2147483647
important
oraDbConfigLogArchiveBufferSize, oraDbConfigLogBuffer
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the LOG_BUFFER parameter of the INIT.ORA file. Indicates the number of bytes allocated to the redo log buffer in the SGA. In a busy system, a value of 65536 or higher would not be unreasonable.
1,000 - 100,000
important
oraDbConfigLogArchiveBuffers, oraDbConfigLogArchiveBufferSize
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the LOG_CHECKPOINT_ INTERVAL parameter of the INIT.ORA file. Indicates the amount of time (in seconds) to pass before another checkpoint occurs. A value of 0 disables time-based checkpoints.
1 - 2147483647
important
oraDbConfigLogCheckpointTimeout
INTEGER (0..2147483647)
read-only
The value of this variable is retrieved directly from the LOG_CHECKPOINT_TIMEOUT parameter of the INIT.ORA file. The amount of time (in seconds) to pass between checkpoints. A value of 0 disables time-based checkpoints.
0 - 2147483647
important
oraDbConfigLogCheckpointInterval
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the LOG_FILES parameter of the INIT.ORA file. Indicates the maximum log group number. This value specifies the maximum number of redo log files that can be opened at database instance run time.
1 - 2147483647
important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the MAX_ROLLBACK_SEGMENTS parameter of the INIT.ORA file. Indicates the maximum size of the rollback segment cache in the SGA.
1 - 65536
important
none
INTEGER (1..2147483647)
The value of this variable is retrieved directly from the MTS_MAX_ DISPATCHERS parameter of the INIT.ORA file. Indicates the maximum number of dispatcher processes allowed to be running simultaneously.
1 - 2147483647
important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the MTS_MAX_SERVERS parameter of the INIT.ORA file. Indicates the maximum number of shared server processes allowed to be running simultaneously.
1 - 2147483647
important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the MTS_SERVERS parameter of the INIT.ORA file. Indicates the number of server processes that are created when an instance is started.
1 - 2147483647
important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the OPEN_CURSORS parameter of the INIT.ORA file. Indicates the maximum number of open cursors (context areas) a single-user process can have at once.
1 - 2147483647
important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the OPEN_LINKS parameter of the INIT.ORA file. Indicates the maximum number of concurrent open connections to remote database instances per user process. This parameter refers only to connections used for distributed transactions; direct connections to a remote database instance specified as an application connects are not counted. If set to 0, then no distributed transactions are allowed.
0 - 255
important
none
DisplayString
read-only
The value of this variable is retrieved directly from the OPTIMIZER_MODE parameter of the INIT.ORA file. When set to RULE, this parameter causes rule-based optimization to be used, unless hints are specified in the query. When set to COST, the optimizer uses a cost-based approach for an SQL statement, if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.)
RULE/COST
important
oraDbConfigSessions
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the PROCESSES parameter of the INIT.ORA file. For a multiple-process operation, this parameter specifies the maximum number of user processes that can simultaneously connect to an Oracle server. The PROCESSES parameter is used to derive the SESSIONS INIT.ORA parameter. The default is 50.
6 - O/S dependent
important
oraDbConfigSessions, oraDbConfigTransactions
gauge or simple string
TruthValue
read-only
The value of this variable is retrieved directly from the SERIALIZABLE parameter of the INIT.ORA file. A value of TRUE indicates that queries acquire table-level read locks, preventing any update of objects read until the transaction containing the query is committed.
true/false
important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the SESSIONS parameter of the INIT.ORA file. Indicates the total number of user and system sessions. The default for parameter is derived as the value of the INIT.ORA PROCESSES parameter multiplied by 1.1.
1 - 2147483647
important
oraDbConfigProcesses
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the SHARED_POOL_SIZE parameter of the INIT.ORA file. Indicates the size (in bytes) of the shared pool. The shared pool contains shared cursors and stored procedures. The default is 3.5 Mbytes.
300 Kbytes - O/S dependent
important
none
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the SORT_AREA_SIZE parameter of the INIT.ORA file. Indicates the maximum amount of PGA memory to use for an exterrnal (disk) sort (in bytes). The default (O/S dependent) is adequate for most DB operations.
1 - 2147483647
none
oraDbConfigRetainedSize, oraDbConfigSortSpaceMapSize
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the SORT_AREA_RETAINED_SIZE parameter of the INIT.ORA file. Indicates the maximum amount of session memory (in bytes) that will be used for an in-memory sort. The default value is equal to that assigned for the SORT_AREA_SIZE INIT.ORA parameter.
0..value of oraDbConfigSortAreaSize
important
oraConfigSortAreaSize
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the TRANSACTIONS parameter of the INIT.ORA file. Indicates the maximum number of concurrent transactions. The default is the value of the PROCESSES INIT.ORA parameter multiplied by 1.1.
1 - 2147483647
important
oraDbConfigProcesses
INTEGER (1..2147483647)
read-only
The value of this variable is retrieved directly from the TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter of the INIT.ORA file. Indicates the number of concurrent transactions allowed per rollback segment. The default is 30.
1..O/S dependent
important
oraDbConfigTransactions
The oraRep Table contains general information about the replicated schemas in the current database. Besides providing a count of the number of schemas being replicated to remote sites, it reports problems with the current node. These problems include the inability to propagate or receive replicated data.
Gauge32
read-only
This indicates the number of failed distributed transactions in a PREPARED state. Used for monitoring configurations where databases are communicating synchronously.
0
This indicates the number of distributed transactions that remain unresolved because of a failure during the commit process. It can help measure the impact of failures.
oraRepLinkErrors
INTEGER32
read-only
This indicates the number of replication triggers that have been disabled. As triggers are the mechanism by which local changes are propogated to remote databases, disabled replication triggers cause databases in the replication environment to go out of sync.
0
Changes applied to replicated tales with a disabled replication trigger will not be propagated to remote sites.
oraRepInvalidPackages
guage or error indicator (if >0)
INTEGER32
read-only
This indicates the number of replication packages that have been invalidated. As these packages are the mechanism by which remote changes are propogated to the local database, invalidated replication packages cause databases in the replication environment to go out of sync.
0
Invalid replication packages may cause transactions to build up in the error queue.
oraRepDisabledTriggers
guage or error indicator (if >0)
INTEGER32
read-only
This indicates the number of schemas at the local node that are involved in replication.
configuration dependent
The number of database schema invoved in replication at the local site.
oraRepSchemaName
The oraRepSchema Table contains information about the replicated
database schemas at the current site. For each such replicated schema,
there exists one instance of this table.
The values of these variables are retrieved directly from the replication catalog. The current replication environment's status (normal, quiescing, quiesced) and configuration (type of replication site and database link to remote replication sites) are useful for displaying a visual representation of the replication environment.
INTEGER32
read-only
This is a numeric index unique among replication schemas contained in this database.
not applicable
Provides a unique identifier for the schema.
none
DisplayString
read-only
This is the name of the replicated schema. This name must be the same at all sites involved in the replication of the schema.
valid Oracle identifier
Identifies replicated schemas. Useful in displaying a visual representation of the replication environment.
oraRepSchemaType
INTEGER{masterdef(1), master(2), snapshot(3)}
read-only
This indicates whether the replicated schema is a master definition schema, a master schema, or a snapshot schema.
not applicable
Most replication administration tasks can be performed only at the master definition sites.
oraRepSchemaName
color indicator or simple string
INTEGER{normal(1), quiescing(2), quiesced(3)}
read-only
This indicates the state of the replicated schema. Replication activity is suspended in the quiesced state and the schema can then be altered.
1 - 3
The meanings of the values are as follows:
Snapshot sites are always in state 1 (normal).
oraRepSchemaType
color indicator or simple string
INTEGER32
read-only
This indicates the number of database links used in the schema for replication.
configuration dependent
For masters there must be at least one database link from
the local site to each remote master.
For snapshots there must be at least one database link from the snapshot site to its master.
oraRepLinkName
The oraRepMasterSchema Table contains specific information
for replicated schemas at the current master or master definition site.
There exists one instance of this table for each replicated schema at the
current site. These values are retrieved from the replication catalog.
The values in this table are useful for displaying a visual
representation of multi-master configurations. Master and master definition
sites are not required to have database links to their snapshot sites.
Snapshot schemas are currently not registered in the replication catalog
at their associated master site. Given an oraRepMasterSchema Table in an
arbitrary replication environment, it is not be possible to locate all
snapshot sites based solely on the information in this table.
Information is also provided for the detection of replication
administration request errors and a relative count of the number of conflicts
encountered at the current site. The table provides a count of the number
of resolved conflicts. Not all conflicts may be resolved (the DBA did not
declare the appropriate conflict resolution method(s) or a conflict was
unable to be resolved due to other problems.
Gauge32
read-only
This indicates the number of administration requests that have encountered errors. The errors themselves are stored in the administration queue at the source database of the request.
0
If the variable is > 0, then 1 or more administration commands failed at 1 or more remote sites.
oraRepLinkAdminRequests
Gauge32
read-only
This indicates the number of conflicts that were successfully resolved at the local database. For this variable to be valid, the DBA must activate statistics collection for conflict resolution.
configuration dependent
If the environment is configured to be conflict-free, this value always should be 0. Otherwise, this value gives the adminsdtrator a rough count of the conflicts that are occuring, although it is imprecise because it does not include any conflicts that have not been resolved.
none
INTEGER32
read-only
This indicates the number of remote master databases involved in the replication of the schema.
configuration dependent
useful for displaying a map of the replication environment.
oraRepSchemaLinks
The oraRepSnapshotSchema Table contains specific information
for snapshot schemas at the current snapshot site. One instance of this
table exists for each snapshot schema at the current site. These values
are retrieved from the replication catalog.
The values in this table are useful for displaying a visual representation of Master- Snapshot configurations. Snapshot sites are required to have database links to their master sites, but a master site is not required to have database links to its associated snapshot sites.
Variable Name | Object ID |
---|---|
oraRepSnapshotSchemaMaster |
1.3.6.1.4.1.111.4.3.4.1.1 |
oraRepSnapshotSchemaSnapshots |
1.3.6.1.4.1.111.4.3.4.1.2 |
DisplayString
read-only
This provides the name of the master database from which the snapshot schema is refreshed. A snapshot schema can be owned by only one master database at a time, but that ownership is dynamic.
valid Oracle identifier
useful for displaying a map of the replication environment.
oraRepLinkName
INTEGER32
read-only
This indicates the number of snapshots in the schema.
configuration dependent
Useful in displaying a visual representation of the replication environment.
The oraRepLink Table contains information for each database
link used for replication at the current site. One instance of this table
exists for each database link used for replication at the current site.
These values are retrieved from both the replication catalog and the Deferred
RPC tables.
All information is reported on a per-database link basis.
For example, oraRepLinkError counts all errors from the database link oraRepLinkName
within the instance of the oraRepLink Table that identifies that link.
This is also true for oraRepLinkDeferredTransactions and oraRepLinkAdminRequests.
Administration requests are only made from the master definition sites to remote master sites. Snapshot sites neither send nor receive replication administration requests.
INTEGER32
read-only
This is a numeric index whose value is unique among the replication links associated with a particular schema.
not applicable
This provides a unique identifier for the link.
none
DisplayString
read-only
This provides the name of the database link.
valid Oracle database link name
This shows the destination to which transactions will be replicated.
none
Gauge32
read-only
This is the number of deferred transactions queued for propagation to the destination of the dblink. The transactions in the queue are stored, organized by destination, at the database originating the transaction.
configuration dependent
As a general rule, there should not be a large backlog of queued transactions. A moderate queue size reduces poential problems with conflicts, integrity constraints, and cascading errors. This value is one measure of the cost of replication in terms of data traffic.
none
Gauge32
read-only
This is the number of errors at the local database that were caused by deferred transactions from the dblink in question. These errors are caused by incorrectly formed transactions or by conflicts. The errors in the queue are stored by source at the database at which the errors occurred.
0
Errors indicate unresolved conflicts or other problems with the replication environment (for example, replication users do not have the correct privileges or the database links were not created correctly).
none
Gauge32
read-only
This is the number of administration requests queued for the local database from the dblink in question. The items in the queue are stored by the source of the requests.
configuration dependent
A large backlog of requests may indicate a problem in the background process which periodically pushes the queue. A gauge would be useful for displaying the percent completion of a single or of a set of adminstration requests (i.e., when the value reaches zero, all requests are complete)
none