MODULE OraMibTables ();
DESCRIPTION "Interface INVENTORY information";
BEGIN
ALL-DEV BY DEV
{
SET-INDEX oraDbSysTable (rdbmsDbName [rdbmsDbIndex, INT]);
SET-INDEX oraDbLibraryCacheTable (rdbmsDbName [rdbmsDbIndex, INT]);
};
END
MODULE OraFilterTables ();
DESCRIPTION "Oracle DB Filter information";
BEGIN
ALL-DEV BY DEV
{
WITH-INDEX oraDbSysTable
{
POLL ( rdbmsDbPrivateMIBOID );
DEFINE rdbmsDbPrivateMIBOID DB DISPL;
IF (rdbmsDbPrivateMIBOID == "1.3.6.1.4.1.111")
THEN
STORE-INDEX "oraDbSysList";
############################################################
# “oraDbSysList“ index list now contains the list of ONLY Oracle Servers on the managed
# elements.
############################################################
ENDIF;
};
};
END
MODULE OraPerfRatios1 (DEV DEVICE);
DESCRIPTION "";
BEGIN
WITH-INDEX oraDbSysList
{
POLL ( oraDbSysDbBlockChanges;
oraDbSysUserCalls;
oraDbSysConsistentGets;
oraDbSysConsistentChanges;
oraDbSysDbBlockGets;
oraDbSysUserCommits;
oraDbSysRecursiveCalls;
oraDbSysTableFetchContinuedRow;
oraDbSysTableFetchRowid;
oraDbSysTableScanRows;
oraDbSysPhysReads;
oraDbSysRedoLogSpaceRequests;
oraDbSysRedoEntries;
oraDbSysSortsDisks;
oraDbSysSortsMemory;
oraDbSysParseCount;
oraDbSysUserRollbacks;
sysUpTime );
};
WITH-INDEX oraDbLibraryCacheTable
{
POLL ( oraDbLibraryCachePins;
oraDbLibraryCacheReloads );
};
END
MODULE OraPerfRatios2 (DEV DEVICE);
DESCRIPTION "";
BEGIN
MIB-GROUP oraDbSysObjects
{
oraDbSysDbBlockChanges;
oraDbSysUserCalls;
oraDbSysConsistentGets;
oraDbSysConsistentChanges;
oraDbSysDbBlockGets;
oraDbSysUserCommits;
oraDbSysRecursiveCalls;
oraDbSysTableFetchContinuedRow;
oraDbSysTableFetchRowid;
oraDbSysTableScanRows;
oraDbSysPhysReads;
oraDbSysRedoLogSpaceRequests;
oraDbSysRedoEntries;
oraDbSysSortsDisks;
oraDbSysSortsMemory;
oraDbSysParseCount;
oraDbSysUserRollbacks;
sysUpTime
};
WITH-INDEX oraDbSysList
{
DEFINE oraDbSysDbBlockChangesT1 INT;
DEFINE oraDbSysDbBlockGetsT1 INT;
DEFINE oraDbSysConsistentGetsT1 INT;
DEFINE oraDbSysConsistentChangesT1 INT;
DEFINE oraDbSysUserCallsT1 INT;
DEFINE oraDbSysUserCommitsT1 INT;
DEFINE oraDbSysRecursiveCallsT1 INT;
DEFINE oraDbSysTableFetchContinuedRowT1 INT;
DEFINE oraDbSysTableFetchRowidT1 INT;
DEFINE oraDbSysPhysReadsT1 INT;
DEFINE oraDbSysRedoLogSpaceRequests T1 INT;
DEFINE oraDbSysRedoEntriesT1 INT;
DEFINE oraDbSysSortsDisksT1 INT;
DEFINE oraDbSysSortsMemoryT1 INT;
DEFINE oraDbSysParseCountT1 INT;
DEFINE oraDbSysUserRollbacksT1 INT;
DEFINE oraDbSysDbBlockChanges DB INT;
DEFINE oraDbSysDbBlockGets DB INT;
DEFINE oraDbSysConsistentGets DB INT;
DEFINE oraDbSysConsistentChanges DB INT;
DEFINE oraDbSysUserCalls DB INT;
DEFINE oraDbSysUserCommits DB INT;
DEFINE oraDbSysRecursiveCalls DB INT;
DEFINE oraDbSysTableFetchContinuedRow DB INT;
DEFINE oraDbSysTableFetchRowid DB INT;
DEFINE oraDbSysPhysReads DB INT;
DEFINE oraDbSysRedoLogSpaceRequests DB INT;
DEFINE oraDbSysRedoEntries DB INT;
DEFINE oraDbSysSortsDisks DB INT;
DEFINE oraDbSysSortsMemory DB INT;
DEFINE oraDbSysParseCount DB INT;
DEFINE oraDbSysUserRollbacks DB INT;
oraDbSysDbBlockChangesT1 = oraDbSysDbBlockChanges;
oraDbSysDbBlockGetsT1 = oraDbSysDbBlockGets;
oraDbSysConsistentGetsT1 = oraDbSysConsistentGets;
oraDbSysConsistentChangesT1 = oraDbSysConsistentChanges;
oraDbSysUserCallsT1 = oraDbSysUserCalls;
oraDbSysUserCommitsT1 = oraDbSysUserCommits;
oraDbSysRecursiveCallsT1 = oraDbSysRecursiveCalls;
oraDbSysTableFetchContinuedRowT1 = oraDbSysTableFetchContinuedRow;
oraDbSysTableFetchRowidT1 = oraDbSysTableFetchRowid;
oraDbSysPhysReadsT1 = oraDbSysPhysReads;
oraDbSysRedoLogSpaceRequestsT1 = oraDbSysRedoLogSpaceRequests;
oraDbSysRedoEntriesT1 = oraDbSysRedoEntries;
oraDbSysSortsDisksT1 = oraDbSysSortsDisks;
oraDbSysSortsMemoryT1 = oraDbSysSortsMemory;
oraDbSysParseCountT1 = oraDbSysParseCount;
oraDbSysUserRollbacksT1 = oraDbSysUserRollbacks;
DEFINE nTime1 INT;
nTime1 = sysUpTime /100;
RMV-GROUP ( oraDbSysObjects );
POLL-GROUP ( oraDbSysObjects );
DEFINE oraDbSysDbBlockChangesT2 INT;
DEFINE oraDbSysDbBlockGetsT2 INT;
DEFINE oraDbSysConsistentGetsT12 INT;
DEFINE oraDbSysConsistentChangesT2 INT;
DEFINE oraDbSysUserCallsT2 INT;
DEFINE oraDbSysUserCommitsT2 INT;
DEFINE oraDbSysRecursiveCallsT2 INT;
DEFINE oraDbSysTableFetchContinuedRowT2 INT;
DEFINE oraDbSysTableFetchRowidT2 INT;
DEFINE oraDbSysPhysReadsT2 INT;
DEFINE oraDbSysRedoLogSpaceRequests T2 INT;
DEFINE oraDbSysRedoEntriesT2 INT;
DEFINE oraDbSysSortsDisksT2 INT;
DEFINE oraDbSysSortsMemoryT2 INT;
DEFINE oraDbSysParseCountT2 INT;
DEFINE oraDbSysUserRollbacksT2 INT;
oraDbSysDbBlockChangesT2 = oraDbSysDbBlockChanges;
oraDbSysDbBlockGetsT2 = oraDbSysDbBlockGets;
oraDbSysConsistentGetsT2 = oraDbSysConsistentGets;
oraDbSysConsistentChangesT2 = oraDbSysConsistentChanges;
oraDbSysUserCallsT2 = oraDbSysUserCalls;
oraDbSysUserCommitsT2 = oraDbSysUserCommits;
oraDbSysRecursiveCallsT2 = oraDbSysRecursiveCalls;
oraDbSysTableFetchContinuedRowT2 = oraDbSysTableFetchContinuedRow;
oraDbSysTableFetchRowidT2 = oraDbSysTableFetchRowid;
oraDbSysPhysReadsT2 = oraDbSysPhysReads;
oraDbSysRedoLogSpaceRequestsT2 = oraDbSysRedoLogSpaceRequests;
oraDbSysRedoEntriesT2 = oraDbSysRedoEntries;
oraDbSysSortsDisksT2 = oraDbSysSortsDisks;
oraDbSysSortsMemoryT2 = oraDbSysSortsMemory;
oraDbSysParseCountT2 = oraDbSysParseCount;
oraDbSysUserRollbacksT2 = oraDbSysUserRollbacks;
DEFINE nTime2 INT;
nTime2 = sysUpTime /100;
DEFINE DeltaT INT;
DeltaT = nTime2 - nTime1;
CounterData( DeltaOraDbSysDbBlockChanges,
oraDbSysDbBlockChangesT1,
oraDbSysDbBlockChangesT2);
CounterData( DeltaOraDbSysDbBlockGets,
oraDbSysDbBlockGetsT1,
oraDbSysDbBlockGetsT2);
CounterData( DeltaOraDbSysConsistentGets ,
oraDbSysConsistentGetsT1,
oraDbSysConsistentGetsT2);
CounterData( DeltaOraDbSysConsistentChanges,
oraDbSysConsistentChangesT1,
oraDbSysConsistentChangesT2);
CounterData( DeltaOraDbSysUserCalls,
oraDbSysUserCallsT1,
oraDbSysUserCallsT2);
CounterData( DeltaOraDbSysUserCommits,
oraDbSysUserCommitsT1,
oraDbSysUserCommitsT2);
CounterData( DeltaOraDbSysRecursiveCalls ,
oraDbSysRecursiveCallsT1,
oraDbSysRecursiveCallsT2);
CounterData( DeltaOraDbSysTableFetchContinuedRow,
oraDbSysTableFetchContinuedRowT1,
oraDbSysTableFetchContinuedRowT2);
CounterData( DeltaOraDbSysTableFetchRowid,
oraDbSysTableFetchRowidT1,
oraDbSysTableFetchRowidT2);
CounterData( DeltaOraDbSysPhysReads,
oraDbSysPhysReadsT1,
oraDbSysPhysReadsT2);
CounterData( DeltaOraDbSysRedoLogSpaceRequests,
oraDbSysRedoLogSpaceRequestsT1,
oraDbSysRedoLogSpaceRequestsT2);
CounterData( DeltaOraDbSysRedoLogSpaceRequests,
oraDbSysRedoLogSpaceRequestsT1,
oraDbSysRedoLogSpaceRequestsT2);
CounterData( DeltaOraDbSysRedoEntries,
oraDbSysRedoEntriesT1,
oraDbSysRedoEntriesT2);
CounterData( DeltaOraDbSysSortsDisks,
oraDbSysSortsDisksT1,
oraDbSysSortsDisksT2);
CounterData( DeltaOraDbSysSortsMemory,
oraDbSysSortsMemoryT1,
oraDbSysSortsMemoryT2);
CounterData( DeltaOraDbSysParseCount,
oraDbSysParseCountT1,
oraDbSysParseCountT2);
CounterData( DeltaOraDbSysUserRollbacks,
oraDbSysUserRollbacksT1,
oraDbSysUserRollbacksT2);
###############################################################
## 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.
DEFINE BlockChgPerTransaction DB INT;
BlockChgPerTransaction = DeltaOraDbSysDbBlockChanges / DeltaOraDbSysUserCalls;
###############################################################
## 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 timeunit
## typically used in this ratio is one second.
DEFINE BlockGetRate DB INT;
BlockGetRate = (DeltaOraDbSysConsistentGets + DeltaOraDbSysDbBlockGets)/ DeltaT;
###############################################################
## 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.
DEFINE BlckVisitsPerTransaction DB INT;
BlckVisitsPerTransaction = (DeltaOraDbSysDbBlockGets + DeltaOraDbSysConsistentGets) / DeltaOraDbSysUserCommits;
###############################################################
## Cache Hit Ratio
## This ratio measures the effectiveness of the buffer cache. The normally acceptable range is
## 70 - 85%.
DEFINE CacheHitRatio DB INT;
CacheHitRatio = (DeltaOraDbSysConsistentGets
+ DeltaOraDbSysDbBlockGets - DeltaOraDbSysPhysReads)
/ (DeltaOraDbSysConsistentGets + DeltaOraDbSysBlockGets);
##############################################################
## 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.
DEFINE CallRate DB INT;
CallRate = (DeltaOraDbSysRecursiveCalls + DeltaOraDbSysUserCalls) / DeltaT;
###############################################################
## 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.
DEFINE CallsPerTransaction DB INT;
CallsPerTransaction = DeltaOraDbSysUserCalls / DeltaOraDbSysUserCommits;
###############################################################
## 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.
DEFINE ChgedBlockRatio DB INT;
ChgedBlockRatio = DeltaOraDbSysDbBlockChanges
/ (DeltaOraDbSysDbBlockGets+ DeltaOraDbSysConsistentGets);
###############################################################
## 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.
DEFINE ConsistentChgeRatio DB INT;
ConsistentChgeRatio = DeltaOraDbSysConsistentChanges/ DeltaOraDbSysConsistentGets;
###############################################################
## 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.
DEFINE ContinuedRowRatio DB INT;
ContinuedRowRatio = DeltaOraDbSysTableFetchContinuedRow /
(DeltaOraDbSysTableFetchRowid+ DeltaOraDbSysTableScanRows);
###############################################################
## 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.
DEFINE RcrsvToUsrCallRatio DB INT;
RcrsvToUsrCallRatio = DeltaOraDbSysRecursiveCalls / DeltaOraDbSysUserCalls;
###############################################################
## 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.
DEFINE RedoLogSpaceWaitRatio DB INT;
RedoLogSpaceWaitRatio = DeltaOraDbSysRedoLogSpaceRequests / DeltaOraDbSysRedoEntries;
###############################################################
## 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.
DEFINE RowSrcRatio DB INT;
RowSrcRatio = DeltaOraDbSysTableScanRows/
(DeltaOraDbSysTableFetchRowid+ DeltaOraDbSysTableScanRows);
###############################################################
## Sort Overflow Ratio
## This 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.
DEFINE SortOvrFlowRatio DB INT;
SortOvrFlowRatio = DeltaOraDbSysSortsDisks /
(DeltaOraDbSysSortsMemory + DeltaOraDbSysSortsDisks);
###############################################################
## 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.
DEFINE SortOvrFlowRatio DB INT;
TransactionRate = DeltaOraDbSysUserCommits;
###############################################################
## 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.
DEFINE UsrCallRate DB INT;
UsrCallRate = DeltaOraDbSysUserCalls;
###############################################################
## 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.
DEFINE UsrCallPerParse DB INT;
UsrCallPerParse = DeltaOraDbSysUserCalls / DeltaOraDbSysParseCount;
###############################################################
## User Rollback Ratio
## This 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.
DEFINE UsrRollBckRatio DB INT;
UsrRollBckRatio = DeltaOraDbSysUserRollbacks /
(DeltaOraDbSysUserCommits + DeltaOraDbSysUserRollbacks);
};
WITH-INDEX oraDbLibraryCacheTable
{
DEFINE oraDbLibraryCachePinsT1 INT;
DEFINE oraDbLibraryCacheReloadsT1 INT;
DEFINE oraDbLibraryCachePins DB INT;
DEFINE oraDbLibraryCacheReloads DB INT;
oraDbLibraryCachePinsT1 = oraDbLibraryCachePins;
oraDbLibraryCacheReloadsT1 = oraDbLibraryCacheReloads;
RMV ( oraDbLibraryCachePins;
oraDbLibraryCacheReloads );
POLL ( oraDbLibraryCachePins;
oraDbLibraryCacheReloads );
DEFINE oraDbLibraryCachePinsT2 INT;
DEFINE oraDbLibraryCacheReloadsT2 INT;
oraDbLibraryCachePinsT2 = oraDbLibraryCachePins;
oraDbLibraryCacheReloadsT2 = oraDbLibraryCacheReloads;
CounterData( DeltaOraDbLibraryCachePins,
oraDbLibraryCachePinsT1,
oraDbLibraryCachePinsT2);
CounterData( DeltaOraDbLibraryCacheReloads,
oraDbLibraryCacheReloadsT1,
oraDbLibraryCacheReloadsT2);
###############################################################
## 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.
DEFINE LibCacheMissRatio DB INT;
LibCacheMissRatio = (DeltaOraDbLibraryCachePins - DeltaOraDbLibraryCacheReloads) /
DeltaOraDbLibraryCachePins;
};
END