Release 8.0.5
A64404-01
Library |
Product |
Contents |
Index |
| Oracle
SNMP Support Reference Guide
Release 8.0.5 A64404-01 |
|
This chapter covers the following topics:
MIB variables can be used for two different purposes. If
they are used for fault management, the DBA is interested in significant
changes in a variable's value or in a ratio over a short period of time.
Once the DBA becomes aware of that change, its cause can be determined
and corrected, if necessary. However, MIB variables can also be used to
tune performance. In this case, the goal is to establish benchmark performance,
as measured by the appropriate variable(s) or ratio(s).
When MIB variables are used for performance tuning, make
sure that you poll enough instances of a variable to measure performance
with some certainty. Polling 1,000 separate statistics is considered acceptable
for any database instance performance ratio mentioned in this chapter.
When designing graphic presentations for these ratios, consider alerting
the user when less than 1,000 separate instances of a variable have been
used to calculate a tuning ratio.
In general, users will be most interested in monitoring changes
in performance ratios over time. Thus, a basic time series graph that plots
changes in the values of these ratios against time is very useful.
The scalar values for MIB variables (and ratios based on
these variables) measuring database performance can vary tremendously,
depending on the type of database application, the number of users accessing
the database, and the amount of data being processed. This fact poses a
challenge to the developer of a management application presenting this
information. Graphic elements intended to accommodate the full range of
scalar values can mislead when presenting scalar values at the low end
of the range; even large changes relative to their size can appear insignificant
when plotted on a graph designed to accommodate much greater values achievable
at other installations.
One good way to accommodate users at both ends of the data
spectrum is to scale the presentation of data dynamically. This involves
adjusting the scale of the graph measuring the data as the data attains
certain thresholds. For example, if you were to plot against time the speed
of a jet from takeoff to its cruising level, you might set the upper limit
of the graph upon takeoff at 200 mph; when that threshold is reached, you
could dynamically adjust the upper limit to 400 mph, and then adjust it
twice more as thresholds of 400 and 600 mph are attained.
Given the tremendous processing power available today, and
given the enormous upper limit to the size of an Oracle database, it may
not be surprising that the values of some MIB variables do in fact exceed
(wrap around) the SNMP-defined upper limit for 32-bit counter and integer
(4294967295) values. A management application designer can best work around
this problem by always showing instance startup time (applUpTime) to the
user, and encouraging the user to poll the values of such variables frequently
enough to determine whether a wrap around has occurred. A minimum of once
every one-half hour for "wrap around candidates" is suggested.
If the value of a counter variable has decreased,
even though the database instance has continued to run, one can assume
that the value has wrapped around the upper limit of the range. However,
one must poll frequently enough to ensure that the delta of the value is
less than the theoretical limit of the range. The values and ratios you
present to the user can then take the wraparound into account.
This section describes those ratios that are most useful
in tuning the performance of an Oracle7 or Oracle8 database instance. These
are the performance measures of greatest interest to potential customers
of management applications for the Oracle7 and Oracle8 servers. Each of
these ratios is based on variables of the private Oracle Database MIB.
These ratios are listed alphabetically, not in order of importance.
For more information on these ratios, see the Oracle Server Tuning Guide specific to your system. For information on the MIB variables that are used to calculate these ratios, see Appendix A, "Oracle Database MIB Reference."
This ratio measures the amount of Data Manipulation Language (DML) work that each transaction performs. Creating or dropping indexes impacts this value, because changes to index blocks increment it.
oraDbSysDbBlockChanges / oraDbSysUserCalls
This ratio determines the block get rate. The block get rate is a basic measure of the rate at which the application system references the database. The time unit typically used in this ratio is one second.
(oraDbSysConsistentGets + oraDbSysDbBlockGets)/ time unit
This ratio measures the work database load imposed per transaction; if it is moving independently, then this strongly indicates that there has been a change in the application workload.
(oraDbBlockGets + oraDbSysConsistentGets) / oraDbSysUserCommits
This ratio measures the effectiveness of the buffer cache. The normally acceptable range is 70 - 85%.
(oraDbSysConsistentGets + oraDbSysBlockGets - oraDbSysPhysReads) / (oraDbSysConsistentGets + oraDbSysBlockGets)
This ratio measures the work demand rate being placed on the instance from all work sources. It should be noted, however, that this rate may not be directly comparable across application system version changes where row at a time loop constructs have been recoded as set operations or vice versa. Use of the array interface will also affect this ratio.
(oraDbSysRecursiveCalls + oraDbSysUserCalls) / time unit
This ratio measures the number of client requests made per transaction. Calls per transaction can be used to detect changes in the application, or in the ways in which it is being used. This value may rise sharply as ad hoc queries increase.
oraDbSysUserCalls / oraDbSysUserCommits
This ratio measures the balance between queries and DML within this database application. Changes in this ratio indicate and/or quantify changes in indexation or application usage.
oraDbSysDbBlockChanges / (oraDbSysBlockGets + oraDbSysConsistentGets)
This ratio measures the extent to which applications are having to exercise the read consistency mechanism. In this connection, it is important to realize that the query processing parts of UPDATE and/or DELETE operations are subject to read consistency.
oraDbSysConsistentChanges / oraDbSysConsistentGets
This ratio should be very close to zero, except in applications handling long LONG columns. If this ratio increases over time, usually PCTFREE has been set too low on one or more tables.
oraDbSysTableFetchContinuedRow / (oraDbSysTable FetchRowid + oraDbSysTableScanRows)
If this ratio begins to rise, then resource usage can be expected to increase. A rising library cache miss ratio may be due to wider use of application functionality causing more SQL statements and stored procedures to be active than had previously been the case.
(oraDbLibraryCachePins - oraDbLibraryCacheReloads) / oraDbLibraryCachePins
Under Oracle7 and Oracle8, a change in this ratio can reflect an application change, or indicate a need to adjust the size of the shared pool. Any marked change in the DDL load also affects this ratio.
oraDbSysRecursiveCalls / oraDbSysUserCalls
This ratio measures memory allocation. If it is greater than 1 / 5,000, then the redo log buffer should be increased until the redo log space wait ratio stops failing.
oraDbSysRedoLogSpaceRequests / oraDbSysRedoEntries
This ratio measures the percentage of the total rows retrieved which came from full table scans. As soon as this percentage starts to rise much above 0, the interpretation of other statistics may need to be reviewed.
oraDbSysTableScanRows / (oraDbSysTableFetchRowid + oraDbSysTableScanRows)
oraDbSysSortsDisks / (oraDbSysSortsMemory + oraDbSysSortsDisks) is the sort overflow ratio. This ratio yields the ratio of the number of sorts which are using temporary segments. Under restricted circumstances when there is a predominance of medium size sorts, increasing the sort area size may be effective.
oraDbSysSortsDisks / (oraDbSysSortsMemory + oraDbSysSortsDisks)
The transaction rate is a basic measure of application work, and would be calibrated in transactions per second (tps) for a typical OLTP benchmark. Administrators should be particularly concerned if a fall in this value is associated with a rise in the number of connected users or vice versa. Changes in application structure or work patterns can also distort this figure.
oraDbSysUserCommits
This rate measures the work demand rate being posed by client side applications running under the instance. It should be noted, however, that this may not be directly comparable across application system version changes where code has been moved from client to server side or vice versa.
oraDbSysUserCalls
This ratio indicates how well the application is managing
its context areas. If it changes, then application change is the most likely
explanation, but it may also indicate that usage patterns are changing
and users are moving from one module to another either more frequently
or less frequently.
Although the shared SQL area makes the maximizing of this ratio less important than with earlier versions of Oracle, it is still possible to reduce resource usage by raising this ratio.
oraDbSysUserCalls / oraDbSysParseCount
oraDbSysUserRollbacks / (oraDbSysUserCommits + oraDbSysUserRollbacks) is the user rollback ratio. The user rollback ratio indicates the rate at which application transactions are failing. Rolling back a transaction uses significant resources, and would seem to indicate that all of the resources expended in executing the transaction have been wasted.
oraDbSysUserRollbacks / (oraDbSysUserCommits + oraDbSysUserRollbacks)
