5 Designing Management Applications Based on Oracle MIBs

Using MIB Variables for Different Purposes

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

Using a Sufficient Sample Size to Calculate Tuning Ratios

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.

Presenting Performance Ratios Graphically

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.

Scaling MIB Variable Values Dynamically

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.

Working Around the Wraparound

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.

Most Useful Database Instance Performance Ratios

This section describes those ratios that are most useful in tuning the performance of an Oracle database instance. These are the performance measures of greatest interest to potential customers of management applications for the Oracle database. 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 Database Performance Tuning Guide specific to your system. For information on the MIB variables that are used to calculate these ratios, see Appendix B, "Interpreting Variables of the Oracle Database MIB".

Block Changes Per Transaction

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 

Block Get Rate

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

Block Visits Per Transaction

This ratio measures the work database load imposed for each transaction; if it is moving independently, then this strongly indicates that there has been a change in the application workload.

(oraDbSysDbBlockGets + oraDbSysConsistentGets) / oraDbSysUserCommits

Cache Hit Ratio

This ratio measures the effectiveness of the buffer cache. The normally acceptable range is 70 - 85%.

(oraDbSysConsistentGets + oraDbSysDbBlockGets - oraDbSysPhysReads) / (oraDbSysConsistentGets + oraDbSysDbBlockGets)

Call Rate

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

Calls Per Transaction

This ratio measures the number of client requests made for each transaction. Calls for each 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

Changed Block Ratio

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 / (oraDbSysDbBlockGets + oraDbSysConsistentGets)

Consistent Change Ratio

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

Continued Row Ratio

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)

Library Cache Miss Ratio

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.

oraDbLibraryCacheReloads) / oraDbLibraryCachePins             

Recursive to User Call Ratio

Under Oracle version 7 and Oracle version 8, 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

Redo Log Space Wait Ratio

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

Row Source Ratio

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) 

Sort Overflow Ratio

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)

Transaction Rate

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

User Call Rate

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

User Calls Per Parse

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

User Rollback Ratio

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)