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 E, "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 Oracle7 database server, see the Oracle7 Tuning Guide.
Table A - 1 lists each variable of the oraDbSysTable and its corresponding object ID.
| Variable Name | Object ID |
| oraDbSysConsistentChanges | 1.3.6.1.4.1.111.4.1.1.1.1 |
| oraDbSysConsistentGets | 1.3.6.1.4.1.111.4.1.1.1.2 |
| oraDbSysDbBlockChanges | 1.3.6.1.4.1.111.4.1.1.1.3 |
| oraDbSysDbBlockGets | 1.3.6.1.4.1.111.4.1.1.1.4 |
| oraDbSysFreeBufferInspected | 1.3.6.1.4.1.111.4.1.1.1.5 |
| oraDbSysFreeBufferRequested | 1.3.6.1.4.1.111.4.1.1.1.6 |
| oraDbSysParseCount | 1.3.6.1.4.1.111.4.1.1.1.7 |
| oraDbSysPhysReads | 1.3.6.1.4.1.111.4.1.1.1.8 |
| oraDbSysPhysWrites | 1.3.6.1.4.1.111.4.1.1.1.9 |
| oraDbSysRedoEntries | 1.3.6.1.4.1.111.4.1.1.1.10 |
| oraDbSysRedoLogSpaceRequests | 1.3.6.1.4.1.111.4.1.1.1.11 |
| oraDbSysRedoSyncWrites | 1.3.6.1.4.1.111.4.1.1.1.12 |
| oraDbSysSortsDisk | 1.3.6.1.4.1.111.4.1.1.1.13 |
| oraDbSysSortsMemory | 1.3.6.1.4.1.111.4.1.1.1.14 |
| oraDbSysSortsRows | 1.3.6.1.4.1.111.4.1.1.1.15 |
| oraDbSysTableFetchRowid | 1.3.6.1.4.1.111.4.1.1.1.16 |
| oraDbSysTableFetchContinuedRow | 1.3.6.1.4.1.111.4.1.1.1.17 |
| oraDbSysTableScanBlocks | 1.3.6.1.4.1.111.4.1.1.1.18 |
| oraDbSysTableScanRows | 1.3.6.1.4.1.111.4.1.1.1.19 |
| oraDbSysTableScansLong | 1.3.6.1.4.1.111.4.1.1.1.20 |
| oraDbSysTableScansShort | 1.3.6.1.4.1.111.4.1.1.1.21 |
| oraDbSysUserCalls | 1.3.6.1.4.1.111.4.1.1.1.22 |
| oraDbSysUserCommits | 1.3.6.1.4.1.111.4.1.1.1.23 |
| oraDbSysUserRollbacks | 1.3.6.1.4.1.111.4.1.1.1.24 |
| oraDbSysWriteRequests | 1.3.6.1.4.1.111.4.1.1.1.25 |
A description of each of these variables follows.
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | two or more orders of magnitude less than the value of oraDbSysConsistentGets |
| Significance | important |
| Related Variables | oraDbSysConsistentGets |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | 0 - 4294967295; can increment at several thousand per second in the largest systems, so this variable is definitely a "wraparound" candidate |
| Significance | very important |
| Related Variables | oraDbSysUserCommits, oraDbSysConsistentChanges, oraDbSysPhysReads, oraDbSysDbBlockChanges, oraDbSysDbBlockGets |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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." |
| oraDbSysDbBlockChanges is used to calculate the following database instance performance ratios: block changes per transaction, and the changed block ratio. | |
| Typical Range | 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 |
| Significance | one of the lesser measures of work rate or throughput |
| Related Variables | oraDbSysBlockGets, oraDbSysConsistentGets, oraDbSysUserCalls |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | 0 - 4294967295; can increment at several thousand per second in the largest systems, so this variable is definitely a "wraparound" candidate |
| Significance | very important |
| Related Variables | oraDbSysDbBlockChanges, oraDbSysConsistentGets, oraDbSysPhysReads, oraDbSysUserCalls |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | The "free buffer inspected" parameter from V$SYSTAT. |
| Typical Range | 0 - 4294967295 |
| Significance | This indicates the number of buffers skipped over from the end of the SLU queue in order to find a free 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. |
| Related Variables | oraDbSysFreeBufferRequested |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | The "free buffer requested" parameter from V$SYSTAT. |
| Typical Range | 0 - 4294967295 |
| Significance | This is the count of the number of times a free buffer wanted to create or load a block. |
| Related Variables | oraDbSysFreeBufferInspected |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | This variable is retrieved directly from the value for PARSE COUNT in V$SYSSTAT. It counts the number of parse calls received by the RDBMS from both connected applications and recursive calls. In Oracle7, 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. | |
| Typical Range | 0 - 4294967295; unlikely to increment at a rate greater than a few hundred per second |
| Significance | important |
| Related Variables | oraDbLibraryCacheTable variables, oraDbSysUserCalls |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | 0 - 4294967295; unlikely to increment at a rate greater than a few hundred per second |
| Significance | very important |
| Related Variables | oraDbSysConsistentGets, oraDbSysDbBlockGets |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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 the DBWR or the CHKD., two background processes responsible for getting Blocks out of the SGA and back on disk. |
| Typical Range | 0 - 4294967295; can increment at several thousand per second in the largest systems, so this variable is definitely a "wraparound" candidate |
| Significance | important |
| Related Variables | oraDbSysRedo family |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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 | |
| Typical Range | 0 - 4294967295; can increment at several thousand per second in the largest systems, so this variable is definitely a "wraparound" candidate. |
| Significance | important |
| Related Variables | oraDbSysRedoLogSpaceRequests, oraDbSysRedoSyncWrites, oraDbSysUserCommits |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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 buffer. |
| oraDbSysRedoLogSpaceRequests is used to calculate the following database instance performance ratios: redo log space wait ratio. | |
| Typical Range | 0 - 10,000,000 (a value of 0 is ideal, but an increment of 1 or 2 per second might be acceptable) |
| Significance | important |
| Related Variables | oraDbSysRedoEntries |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. |
| Typical Range | 0 - 10,000,000 (typically increments at less than 1 persecond |
| Significance | less important |
| Related Variables | oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSysUserCommits |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | for OLTP it should be 0 |
| Significance | important |
| Related Variables | oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSyncWrites, oraDbSysSortsMemory, oraDbUserCommits |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | 0 - 4294967295; unlikely to increment faster than tens per second, except in the largest systems |
| Significance | important |
| Related Variables | oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSyncWrites, oraDbSysSortsDisks, oraDbUserCommits |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. |
| Typical Range | 0 - 4294967295 |
| Significance | important |
| Related Variables | oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSyncWrites, oraDbSysSortsMemory, oraDbSortsDisks, oraDbUserCommits |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | 0 - 4294967295; can increment at several tens of thousands per second in the largest systems, so this variable is definitely a "wraparound" candidate |
| Significance | important |
| Related Variables | oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSyncWrites, oraDbSysSortsDisks, oraDbSysTableFetchContinuedRow, oraDbSysTableScanRows, oraDbUserCommits |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | close to 0 unless long LONG values are in use |
| Significance | important |
| Related Variables | oraDbSysTable FetchRowid, oraDbSysTableScanRows |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. |
| Typical Range | 0 - 3 billion; in OLTP very low; in DSS applications, a few thousand per second, so this variable is definitely a "wraparound" candidate |
| Significance | important |
| Related Variables | oraDbSysConsistentGets |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | 0 - 100,000,000 per day |
| Significance | important |
| Related Variables | oraDbSysTableFetchContinuedRow, oraDbSysTableFetchRowid |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. |
| Typical Range | in OLTP, typically 0 |
| Significance | important |
| Related Variables | oraDbSysTableScansShort |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. |
| Typical Range | 0 - 4294967295; can increment at a few hundred per second |
| Significance | important |
| Related Variables | oraDbSysTableScansLong |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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 Oracle7'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. | |
| Typical Range | 0 - 10,000,000 per day |
| Significance | very important |
| Related Variables | oraDbSysDbBlockChanges, oraDbSysParseCount, oraDbSysRecursiveCalls, oraDbSysUserCommits |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | In most real world applications, the value is less the 1 per second. |
| Significance | In OLTP, very important |
| Related Variables | oraDbBlockGets, oraDbSysConsistentGets, oraDbUserCalls |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. | |
| Typical Range | 0 - 4294967295 |
| Significance | important |
| Related Variables | oraDbSysUserCommits |
| Suggested Presentation | counter |
| Syntax | Counter32 |
| Max-Access | read-only |
| Explanation | 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. |
| Typical Range | 0 - 4294967295; unlikely to increment at a rate greaterthan a few hundred per second |
| Significance | less important |
| Related Variables | oraDbSysPhysWrites |
| Suggested Presentation | counter |