Skip Headers
Oracle® Enterprise Manager SNMP Support Reference Guide
11g Release 1 (11.1.0.1.0)

E14091-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

B Interpreting Variables of the Oracle Database MIB

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 5, "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 Database Reference and the Oracle Database Concepts specific to your system.

oraDbSysTable

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 Database Performance Tuning Guide specific to your system.

Table B-1 lists each variable of the oraDbSysTable and its corresponding object ID.

Table B-1 oraDbSysTable Variables and Corresponding Object IDs

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


A description of each of these variables follows.

oraDbSysConsistentChanges

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

oraDbSysConsistentGets

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

oraDbSysDbBlockChanges

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

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

oraDbSysDbBlockGets, oraDbSysConsistentGets, oraDbSysUserCalls

Suggested Presentation

counter

oraDbSysDbBlockGets

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.

oraDbSysDbBlockGets 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

oraDbSysFreeBufferInspected

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 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 because 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

oraDbSysFreeBufferRequested

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 was requested to create or load a block.

Related Variables

oraDbSysFreeBufferInspected

Suggested Presentation

counter

oraDbSysParseCount

Syntax

Counter32

Max-Access

read-only

Explanation

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 Oracle, 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

oraDbSysPhysReads

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

oraDbSysPhysWrites

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 DBWR or LGWR, 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

oraDbSysRedoEntries

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

oraDbSysRedoLogSpaceRequests

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

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

oraDbSysRedoSyncWrites

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 per second)

Significance

less important

Related Variables

oraDbSysRedoEntries, oraDbSysRedoLogSpaceRequest, oraDbSysUserCommits

Suggested Presentation

counter

oraDbSysSortsDisk

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

oraDbSysSortsMemory

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

oraDbSysSortsRows

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

oraDbSysTableFetchRowid

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

oraDbSysTableFetchContinuedRow

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

oraDbSysTableScanBlocks

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

oraDbSysTableScanRows

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

oraDbSysTableScansLong

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

oraDbSysTableScansShort

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

oraDbSysUserCalls

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

Typical Range

0 - 10,000,000 per day

Significance

very important

Related Variables

oraDbSysDbBlockChanges, oraDbSysParseCount, oraDbSysUserCommits

Suggested Presentation

counter

oraDbSysUserCommits

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

oraDbSysDbBlockGets, oraDbSysConsistentGets, oraDbUserCalls

Suggested Presentation

counter

oraDbSysUserRollbacks

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

oraDbTablespaceTable

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 B-2 lists each of these variables and its corresponding object.

Table B-2 oraDbTablespaceTable Variables and Corresponding Object IDs.

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


oraDbTablespaceIndex

Syntax

INTEGER (1..2147483647)

Max-Access

not-accessible

Explanation

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.

Typical Range

1 - 1,000

Significance

very important

Related Variables

oraDbTablespaceName, rdbmsDbIndex

Suggested Presentation

not applicable

oraDbTablespaceName

Syntax

DisplayString

Max-Access

read-only

Explanation

Indicates the name of this tablespace, as retrieved from the value for TABLESPACE-NAME in DBA_TABLESPACES.

Typical Range

0 - 30 characters

Significance

important

Related Variables

oraDbTablespaceIndex

Suggested Presentation

simple string

oraDbTablespaceSizeAllocated

Syntax

Integer32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 10 gigabytes

Significance

important

Related Variables

oraDbTablespaceSizeUsed

Suggested Presentation

simple string

oraDbTablespaceSizeUsed

Syntax

Integer32

Max-Access

read-only

Explanation

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 data files associated with the tablespace and the sum of the size of the free spaces associated with the tablespace.

Typical Range

0 - value of oraDbTablespaceSizeAllocated Significance

Very important

Significance

Very important

Related Variables

oraDbTablespaceSizeAllocated

Suggested Presentation

gauge

oraDbTablespaceState

Syntax

INTEGER (1) online (2) offline, or (3) invalid

Max-Access

read-only

Explanation

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.

Typical Range

enumerated 1 - 3

Significance

very important

Related Variables

none

Suggested Presentation

simple string or icon

oraDbTablespaceLargestAvailableChunk

Syntax

Integer32

Max-Access

read-only

Explanation

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.

Typical Range

Less than or equal to (oraDbTablespaceSizeAllocated minus oraDbTablespaceSizeUsed)

Significance

important

Related Variables

oraDbTablespaceSizeAllocated, oraDbTablespaceSizeUsed

Suggested Presentation

gauge

oraDbDataFileTable

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 D, "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 B-3 lists each variable of the oraDbDataFileTable and its corresponding object ID.

Table B-3 oraDbDataFileTable Variables and Corresponding Object IDs

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


oraDbDataFileIndex

Syntax

INTEGER(1..2147483647)

Max-Access

not-accessible

Explanation

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 data files in a tablespace. The upper limit is hard coded as 1023.

Typical Range

Significance

very important

Related Variables

rdbmsDbIndex

Suggested Presentation

not applicable

oraDbDataFileName

Syntax

DisplayString

Max-Access

read-only

Explanation

Indicates the fully-qualified name of this data file. The value is retrieved from the value of FILE_NAME in DBA_DATA_FILES.

Typical Range

10 - 100 characters; about 15 for raw partitions.

Significance

important

Related Variables

oraDbTablespaceName, because the file is a member of a tablespace.

Suggested Presentation

simple string

oraDbDataFileSizeAllocated

Syntax

Integer32

Max-Access

read-only

Explanation

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.

Typical Range

100 Mbytes - 2 Gbytes

Significance

very important

Related Variables

none

Suggested Presentation

simple string

oraDbDataFileDiskReads

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

10,000 - 100,000 or much more, depending on the life of the instance

Significance

less important

Related Variables

oraDbDataFileDiskReadBlocks, oraDbDataFileDiskReadTimeTicks

Suggested Presentation

counter

oraDbDataFileDiskWrites

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

10,000 - 100,000 or much more, depending on the life of the instance

Significance

less important

Related Variables

oraDbDataFileDiskWriteBlocks, oraDbDataFileDiskWriteTimeTicks

Suggested Presentation

counter

oraDbDataFileDiskReadBlocks

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

10,000 - 100,000 or much more, depending on the life of the instance

Significance

important

Related Variables

oraDbConfigDbBlockSize, oraDbDataFileDiskReads

Suggested Presentation

counter

oraDbDataFileDiskWrittenBlocks

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

10,000 - 100,000 or much more, depending on the life of the instance

Significance

important

Related Variables

oraDbDataFileDiskWrites

Suggested Presentation

counter

oraDbDataFileDiskReadTimeTicks

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

less important

Related Variables

oraDbDataFileDiskReads

Suggested Presentation

clock

oraDbDataFileDiskWriteTimeTicks

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

Can wrap around the upper limit of 2147483647 in about three days of continuous instance operation for a busy database

Significance

less important

Related Variables

oraDbDataFileDiskWrites

Suggested Presentation

clock

oraDbLibraryCacheTable

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 D, "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 B-4 lists each variable of the oraDbLibraryCacheTable and its corresponding object ID.

Table B-4 oraDbLibraryCacheTable Variables and Corresponding Object IDs

Variable Name Object ID

oraDbLibraryCacheIndex

1.3.6.1.4.1.111.4.1.4.1.1

oraDbLibraryCacheNameSpace

1.3.6.1.4.1.111.4.1.4.1.2

oraDbLibraryCacheGets

1.3.6.1.4.1.111.4.1.4.1.3

oraDbLibraryCacheGetHits

1.3.6.1.4.1.111.4.1.4.1.4

oraDbLibraryCachePins

1.3.6.1.4.1.111.4.1.4.1.5

oraDbLibraryCachePinHits

1.3.6.1.4.1.111.4.1.4.1.6

oraDbLibraryCacheReloads

1.3.6.1.4.1.111.4.1.4.1.7

oraDbLibraryCacheInvalidations

1.3.6.1.4.1.111.4.1.4.1.8


oraDbLibraryCacheIndex

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

A unique integer for each row of the oraDbLibraryCacheTable.

Typical Range

8 (only)

Significance

very important

Related Variables

oraDbLibraryCacheNameSpace

Suggested Presentation

not applicable

oraDbLibraryCacheNameSpace

Syntax

DisplayString

Max-Access

read-only

Explanation

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.

Typical Range

0 - 30 characters

Significance

important

Related Variables

oraDbLibraryCacheIndex

Suggested Presentation

simple string

oraDbLibraryCacheGets

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 500,00 per day

Significance

important

Related Variables

oraDbLibraryCacheGetHits

Suggested Presentation

counter

oraDbLibraryCacheGetHits

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

less important

Related Variables

oraDbLibraryCacheGets

Suggested Presentation

counter

oraDbLibraryCachePins

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

less important

Related Variables

oraDbLibraryPinHits, oraDbLibraryCacheReloads

Suggested Presentation

counter

oraDbLibraryCachePinHits

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

important

Related Variables

oraDbLibraryCachePins

Suggested Presentation

counter

oraDbLibraryCacheReloads

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

important

Related Variables

oraDbLibraryCachePins

Suggested Presentation

counter

oraDbLibraryCacheInvalidations

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

less important

Related Variables

none

Suggested Presentation

counter

oraDbLibraryCacheSumTable

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.

Table B-5 oraDBLibraryCacheSum Table Variables and Corresponding Object IDs

Variable Name Object ID

oraDbLibraryCacheSumGets

1.3.6.1.4.1.111.4.1.5.1.1

oraDbLibraryCacheSumGetHits

1.3.6.1.4.1.111.4.1.5.1.2

oraDbLibraryCacheSumPins

1.3.6.1.4.1.111.4.1.5.1.3

oraDbLibraryCacheSumPinHits

1.3.6.1.4.1.111.4.1.5.1.4

oraDbLibraryCacheSumReloads

1.3.6.1.4.1.111.4.1.5.1.5

oraDbLibraryCacheSumInvalidations

1.3.6.1.4.1.111.4.1.5.1.6


oraDbLibraryCacheSumGets

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 500,00 per day

Significance

important

Related Variables

oraDbLibraryCacheSumGetHits

Suggested Presentation

counter

oraDbLibraryCacheSumGetHits

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

less important

Related Variables

oraDbLibraryCacheSumGets

Suggested Presentation

counter

oraDbLibraryCacheSumPins

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

less important

Related Variables

oraDbLibraryPinHits, oraDbLibraryCacheSumReloads

Suggested Presentation

counter

oraDbLibraryCacheSumPinHits

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

important

Related Variables

oraDbLibraryCacheSumPins

Suggested Presentation

counter

oraDbLibraryCacheSumReloads

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

important

Related Variables

oraDbLibraryCacheSumPins

Suggested Presentation

counter

oraDbLibraryCacheSumInvalidations

Syntax

Counter32

Max-Access

read-only

Explanation

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.

Typical Range

0 - 4294967295

Significance

less important

Related Variables

none

Suggested Presentation

counter

oraDbSGATable

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 B-6 lists each variable of the oraDbSGATable and its corresponding object ID.

Table B-6 oraDbSGATable Variables and Corresponding Object IDs

Variable Name Object ID

oraDbSGAFixedSize

1.3.6.1.4.1.111.4.1.6.1.1

oraDbSGAVariableSize

1.3.6.1.4.1.111.4.1.6.1.2

oraDbSGADatabaseBuffers

1.3.6.1.4.1.111.4.1.6.1.3

oraDbSGARedoBuffers

1.3.6.1.4.1.111.4.1.6.1.4


oraDbSGAFixedSize

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

The size in kilobytes of the fixed-size portion of the SGA as shown in V$SGASTAT.

Typical Range

1 - 2147483647

Significance

less important

Related Variables

oraDbSGAVariableSize

Suggested Presentation

as a simple integer value

oraDbSGAVariableSize

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

The size in kilobytes of the variable-size portion of the SGA as shown in V$SGASTAT.

Typical Range

1 - 2147483647

Significance

less important

Related Variables

oraDbSGAFixedSize

Suggested Presentation

as a simple integer value

oraDbSGADatabaseBuffers

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

The number of database buffers allocated in the SGA as shown in V$SGASTAT.

Typical Range

4 Kbytes - 2 Gbytes

Significance

less important

Related Variables

oraDbSGARedoBuffers

Suggested Presentation

as a simple integer value

oraDbSGARedoBuffers

Syntax

INTEGER (1..2147483647)

Max-Access

read/write

Explanation

The number of redo buffers allocated in the SGA as shown in V$SGASTAT.

Typical Range

1 - 2147483647

Significance

less important

Related Variables

oraDbSGADatabaseBuffers

Suggested Presentation

as a simple integer value

oraDbConfigTable

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 Database Reference and also the Oracle Database Concepts 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 B-7 lists each variable of the oraDbConfigTable and its corresponding object ID.

Table B-7 oraDbConfigTable Variables and Corresponding Object IDs

Variable Name Object ID

oraDbConfigDbBlockBuffers

1.3.6.1.4.1.111.4.1.7.1.1

oraDbConfigDbBlockSize

1.3.6.1.4.1.111.4.1.7.1.3

oraDbConfigDbMultiBlockReadCount

1.3.6.1.4.1.111.4.1.7.1.5

oraDbConfigLogBuffer

1.3.6.1.4.1.111.4.1.7.1.11

oraDbConfigLogCheckpointInteval

1.3.6.1.4.1.111.4.1.7.1.12

oraDbConfigLogCheckpointTimeout

1.3.6.1.4.1.111.4.1.7.1.13

oraDbConfigOpenCursors

1.3.6.1.4.1.111.4.1.7.1.19

oraDbConfigOpenLinks

1.3.6.1.4.1.111.4.1.7.1.20

oraDbConfigOptimizerMode

1.3.6.1.4.1.111.4.1.7.1.21

oraDbConfigProcesses

1.3.6.1.4.1.111.4.1.7.1.22

oraDbConfigSessions

1.3.6.1.4.1.111.4.1.7.1.24

oraDbConfigSharedPool

1.3.6.1.4.1.111.4.1.7.1.25

oraDbConfigSortAreaSize

1.3.6.1.4.1.111.4.1.7.1.26

oraDbConfigSortAreaRetainedSize

1.3.6.1.4.1.111.4.1.7.1.27

oraDbConfigTransactions

1.3.6.1.4.1.111.4.1.7.1.28

oraDbConfigTransactionsPerRollback

1.3.6.1.4.1.111.4.1.7.1.29


oraDbConfigDbBlockBuffers

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

1 - 2147483647

Significance

very important

Related Variables

oraDbConfigDbBlockSize

Suggested Presentation

gauge or simple string

oraDbConfigDbBlockSize

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

1024 - 8192 (O/S dependent)

Significance

important

Related Variables

none

Suggested Presentation

gauge or simple string

oraDbConfigDbMultiBlockReadCount

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

Values in the range of 4 to 32 are reasonable. Actual maximums vary by operating system

Significance

important

Related Variables

oraDbConfigDbBlockBuffers, oraDbConfigDbProcesses

Suggested Presentation

gauge or simple string

oraDbConfigLogBuffer

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

1,000 - 100,000

Significance

important

Related Variables

none

Suggested Presentation

gauge or simple string

oraDbConfigLogCheckpointInterval

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

1 - 2147483647

Significance

important

Related Variables

oraDbConfigLogCheckpointTimeout

Suggested Presentation

clock or simple string

oraDbConfigLogCheckpointTimeout

Syntax

INTEGER (0..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

0 - 2147483647

Significance

important

Related Variables

oraDbConfigLogCheckpointInterval

Suggested Presentation

clock or simple string

oraDbConfigOpenCursors

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

1 - 2147483647

Significance

important

Related Variables

none

Suggested Presentation

gauge or simple string

oraDbConfigOpenLinks

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

0 - 255

Significance

important

Related Variables

none

Suggested Presentation

gauge or simple string

oraDbConfigOptimizerMode

Syntax

DisplayString

Max-Access

read-only

Explanation

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

Typical Range

RULE/COST

Significance

important

Related Variables

oraDbConfigSessions

Suggested Presentation

icon or simple string

oraDbConfigProcesses

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

6 - O/S dependent

Significance

important

Related Variables

oraDbConfigSessions, oraDbConfigTransactions

Suggested Presentation

gauge or simple string

Suggested Presentation

gauge or simple string

oraDbConfigSessions

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

1 - 2147483647

Significance

important

Related Variables

oraDbConfigProcesses

Suggested Presentation

gauge or simple string

oraDbConfigSharedPool

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

300 Kbytes - O/S dependent

Significance

important

Related Variables

none

Suggested Presentation

gauge or simple string

oraDbConfigSortAreaSize

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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 external (disk) sort (in bytes). The default (O/S dependent) is adequate for most DB operations.

Typical Range

1 - 2147483647

Significance

none

Related Variables

oraDbConfigRetainedSize, oraDbConfigSortSpaceMapSize

Suggested Presentation

gauge or simple string

oraDbConfigSortAreaRetainedSize

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

0..value of oraDbConfigSortAreaSize

Significance

important

Related Variables

oraConfigSortAreaSize

Suggested Presentation

gauge or simple string

oraDbConfigTransactions

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

1 - 2147483647

Significance

important

Related Variables

oraDbConfigProcesses

Suggested Presentation

gauge or simple string

oraDbConfigTransactionsPerRollback

Syntax

INTEGER (1..2147483647)

Max-Access

read-only

Explanation

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.

Typical Range

1..O/S dependent

Significance

important

Related Variables

oraDbConfigTransactions

Suggested Presentation

gauge or simple string