SmartMIB Professional Solution:

Oracle Database Instance Performance Ratios

(SM-Pro-OraPerf)

(SM-ProUpd-OraPerf)

(SM-ProPlus-OraPerf)

Solution Introduction, Details Overview, and related Thresholds:

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

The solution presentation is based on the Oracle Server Tuning Guide specific to Oracle7 or Oracle8 systems. The MIB variables that are used to calculate these ratios are obtained from the "Oracle Database MIB Reference."

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 per 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 + oraDbSysBlockGets - oraDbSysPhysReads) / 

(oraDbSysConsistentGets + oraDbSysBlockGets)

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

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 / (oraDbSysBlockGets + 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

----------------------------------------------------

(oraDbSysTableFetchRowid+ 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.

(oraDbLibraryCachePins - oraDbLibraryCacheReloads) / oraDbLibraryCachePins             

Recursive to User Call Ratio

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

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

[Small Solutions], [Professional Solutions], [Security Solutions], [Development Solutions]

[Home], [About], [Solutions Center], [NMS Market], [Products & Services],
[Management Technology], [Technical Support], [Contact us], [Site Map]