Wanted: Oracle Metrics
From: Vince Laurent <vlaurent_at_networkusa.net>
Date: Mon, 20 May 2002 14:23:30 -0500
Message-ID: <48jieu0bd9o8omvc58618fjbv8i6he9k6f_at_4ax.com>
This was passed to me by our OpenView admin. Our management likes metrics. Can someone help me show some that might mean something?
I have been asked to identify Oracle metrics collected by OVO (Oracle Plug-in For OpenView) to be displayed on the Metrics database web page. Could someone give some direction on which metrics may be useful. If you have time, scan the list below and let me know which of the metrics could be used for capacity planning or fault prevention.
# of tablespaces not ONLINE (E007_TblSpcStatusCnt) # of tblspaces with high ratio of block to physcial reads (E008_TSBReadRatio
Cnt)
# of tablespaces with high use of temp segments to total (E009_TSTmpExntPctCnt)
% of memory sorts (E020_SortMemoryPct)
% of buffer busy waits to logical reads (E021_BufferBusyPct)
Total buffer cache hit % (E022_TotBufCacHitPct) Current buffer cache hit % (E023_CurBufCacHitPct)
% of enqueue waits to enqueue requests (E024_EQWaitsReqPct)
% of cache getmisses to gets in dictionary cache
(E026_DictCacheHitPct)
% of library cache misses to executions (E027_LibCachRelodPct)
% of DML locks used to total configured (E028_LocksUsedPct)
# of sessions waiting for release of a lock (E029_SessWaitLckCnt) Rate at which full table scans (long tables) occur (E030_FulLgTblScnRate)
# of users with % of open cursors to maximum configured (E031_OpenCrsrPctCnt)
# of waits for redo log buffer space (E032_RedoLgSpcReqCnt)
% of redo allocation latch misses (E033_RedoAlocLtchPct)
% of redo copy latch misses (E034_RedoCopyLtchPct)
Rate of background checkpoints completed (E035_BckgndCkptRate) Number of latches with high contention ratio > threshold (E038_LtchOvrLimitCnt)
% of gethits to gets in dictionary cache (E039_LibCacGetHitPct)
% of pinhits to pins in dictionary cache (E040_LibCacPinHitPct)
% of never analyzed tables and indexes (E042_UnlyzTblIndxPct)
% of enqueue timeouts to enqueue requests (E043_EQTimeoutReqPct)
% of free pool memory (E045_ShrdPoolFreePct)
% rows fetched by index (E046_RowFetcbyIdxPct)
% of chained rows fetched (E048_ChandRowFtchPct)
Ratio of recursive calls to user calls (E050_RcsvUsrCalRatio) Rate of total sorts on disk and in memory (E052_SortTotalRate) Rate at which rollbacks are being generated (E054_RollbackRate) # of archive logs that fit in archive device (E056_ArchvFreeSpcCnt) Avg time in minutes between archive log writes (E057_ArchiveFreqRate)
% of free space on archive device (E058_ArchvFreeSpcPct)
% of cursors in cache parameter (E059_CursorCachePct)
# of redo logs not yet archived (E060_RedoUnarchvdCnt) Status of auto archiving (E061_AutoArchvStatus)
% of space used on background_dump device (E062_BkgrDumpSpcePct)
Number of trace files in bdump/udump/cdump (E063_TraceFileAddCnt)
% of space used on user dump device (E064_UserDumpSpacPct)
% of space used on core dump device (E065_CoreDumpSpacPct)
Size in MB of alert log (E066_AlertLogSize) # of rollback segments not online (E067_RBSegmntStatCnt) # of rollback segment shrinks (E068_RBSgmntShrnkCnt)
% of rollback segment waits to gets (E069_RBSegWaitPctCnt)
% of parallel query servers busy (E070_PQServrsBusyPct)
% of parallel query servers busy highwatermark (E071_PQSrvHighwtrPct)
Rate of parallel queries initiated (E074_PQQueryRate) Ratio of recursive calls to cumulative opened cursors (E075_RcrsvCursrRatio)
% of full table scans via rowid range compared to total
(E076_PQRangeScanPct)
SYS.DUAL status (E077_DualExssRowStat)
% of current transactions to configured (E085_TransactionPct)
% of current processes to configured (E087_ProcessPct)
% of enqueues to configured (E089_EnqueuePct)
% Busy (average) for all dispatchers (E090_DsptchrBusyPct )
# clients currently connected to all dispatchers (E091_NumDsptchrClnts)
% shared servers waiting for requests (E092_ShrSrvrReqWtPct)
% of busy to max shared server processes (E093_SharedServerPct)
% of total UGA mem in MB allocated by current processes
(E094_SesUGAMemCurPct)
% maximum UGA memory in MB allocated by current processes
(E095_SesUGAMemMaxPct)
% of highwater to max shared server processes (E096_ShrdSrvHWMPct)
# tables with table locks disabled (E097_DisbldTblLckNum)
Date: Mon, 20 May 2002 14:23:30 -0500
Message-ID: <48jieu0bd9o8omvc58618fjbv8i6he9k6f_at_4ax.com>
This was passed to me by our OpenView admin. Our management likes metrics. Can someone help me show some that might mean something?
I have been asked to identify Oracle metrics collected by OVO (Oracle Plug-in For OpenView) to be displayed on the Metrics database web page. Could someone give some direction on which metrics may be useful. If you have time, scan the list below and let me know which of the metrics could be used for capacity planning or fault prevention.
Here are some I selected (let me know if you agree or disagree with their usefulness)
# of segments approaching max extent (E017_SegMaxExtentCnt)
Disk sort rate (E019_SortDiskRate)
Total buffer cache hit % (E022_TotBufCacHitPct)
Rate of background checkpoints completed (E035_BckgndCkptRate)
Table spaces with low free space ; drill down (E206_TblSpFreePct)
Here is the list of all available metrics. Definitions are available in "VantagePoint SMART Plug-ins for Databases Oracle Referece" http://ovweb.external.hp.com/lpe/doc_serv/
Database status (E001_DbInstanceStat)
Database process check (E002_ProcessStatus)
# of table spaces with free extents low (E003_TblSpaceFreeCnt) # of users w/default tablespace set to SYSTEM (E004_UsersTmpDfltCnt) # of foreign objects in SYSTEM tblspc (E005_ObjctsForignCnt) # of table spaces with low free space percentage (E006_TblSpFreePctCnt)
# of tablespaces not ONLINE (E007_TblSpcStatusCnt) # of tblspaces with high ratio of block to physcial reads (E008_TSBReadRatio
Cnt)
# of tablespaces with high use of temp segments to total (E009_TSTmpExntPctCnt)
# of fragmented tablespaces (E011_TblSpcFrgmntCnt) # of segments that cannot extend (E016_SegmntExtendCnt) # of segments approaching max extent (E017_SegMaxExtentCnt) # of segments adding extents rapidly (E018_SegExtRapidCnt)Disk sort rate (E019_SortDiskRate)
% of memory sorts (E020_SortMemoryPct)
% of buffer busy waits to logical reads (E021_BufferBusyPct)
Total buffer cache hit % (E022_TotBufCacHitPct) Current buffer cache hit % (E023_CurBufCacHitPct)
% of enqueue waits to enqueue requests (E024_EQWaitsReqPct)
% of cache getmisses to gets in dictionary cache
(E026_DictCacheHitPct)
% of library cache misses to executions (E027_LibCachRelodPct)
% of DML locks used to total configured (E028_LocksUsedPct)
# of sessions waiting for release of a lock (E029_SessWaitLckCnt) Rate at which full table scans (long tables) occur (E030_FulLgTblScnRate)
# of users with % of open cursors to maximum configured (E031_OpenCrsrPctCnt)
# of waits for redo log buffer space (E032_RedoLgSpcReqCnt)
% of redo allocation latch misses (E033_RedoAlocLtchPct)
% of redo copy latch misses (E034_RedoCopyLtchPct)
Rate of background checkpoints completed (E035_BckgndCkptRate) Number of latches with high contention ratio > threshold (E038_LtchOvrLimitCnt)
% of gethits to gets in dictionary cache (E039_LibCacGetHitPct)
% of pinhits to pins in dictionary cache (E040_LibCacPinHitPct)
% of never analyzed tables and indexes (E042_UnlyzTblIndxPct)
% of enqueue timeouts to enqueue requests (E043_EQTimeoutReqPct)
% of free pool memory (E045_ShrdPoolFreePct)
% rows fetched by index (E046_RowFetcbyIdxPct)
% of chained rows fetched (E048_ChandRowFtchPct)
Ratio of recursive calls to user calls (E050_RcsvUsrCalRatio) Rate of total sorts on disk and in memory (E052_SortTotalRate) Rate at which rollbacks are being generated (E054_RollbackRate) # of archive logs that fit in archive device (E056_ArchvFreeSpcCnt) Avg time in minutes between archive log writes (E057_ArchiveFreqRate)
% of free space on archive device (E058_ArchvFreeSpcPct)
% of cursors in cache parameter (E059_CursorCachePct)
# of redo logs not yet archived (E060_RedoUnarchvdCnt) Status of auto archiving (E061_AutoArchvStatus)
% of space used on background_dump device (E062_BkgrDumpSpcePct)
Number of trace files in bdump/udump/cdump (E063_TraceFileAddCnt)
% of space used on user dump device (E064_UserDumpSpacPct)
% of space used on core dump device (E065_CoreDumpSpacPct)
Size in MB of alert log (E066_AlertLogSize) # of rollback segments not online (E067_RBSegmntStatCnt) # of rollback segment shrinks (E068_RBSgmntShrnkCnt)
% of rollback segment waits to gets (E069_RBSegWaitPctCnt)
% of parallel query servers busy (E070_PQServrsBusyPct)
% of parallel query servers busy highwatermark (E071_PQSrvHighwtrPct)
Rate of parallel queries initiated (E074_PQQueryRate) Ratio of recursive calls to cumulative opened cursors (E075_RcrsvCursrRatio)
% of full table scans via rowid range compared to total
(E076_PQRangeScanPct)
SYS.DUAL status (E077_DualExssRowStat)
# of invalid objects (E078_ObjctsInvaldCnt) # of disabled triggers (E079_DisbldTrigrsCnt) # of disabled constraints (E080_DisbldCnstrtCnt) # of snapshot errors (E081_SnapshotErrCnt)Maximum number of sessions since startup (E082_SessHighWatrCnt) Rate of DBWR checkpoints (E083_DbwrCkptrate)
% of current transactions to configured (E085_TransactionPct)
% of current processes to configured (E087_ProcessPct)
% of enqueues to configured (E089_EnqueuePct)
% Busy (average) for all dispatchers (E090_DsptchrBusyPct )
# clients currently connected to all dispatchers (E091_NumDsptchrClnts)
% shared servers waiting for requests (E092_ShrSrvrReqWtPct)
% of busy to max shared server processes (E093_SharedServerPct)
% of total UGA mem in MB allocated by current processes
(E094_SesUGAMemCurPct)
% maximum UGA memory in MB allocated by current processes
(E095_SesUGAMemMaxPct)
% of highwater to max shared server processes (E096_ShrdSrvHWMPct)
# tables with table locks disabled (E097_DisbldTblLckNum)
Table spaces with low free space ; drill down (E203_TableSpaceFree) Table spaces with low free space ; drill down (E206_TblSpFreePct) # of segments that cannot extend ; drill down (E216_SegmntExtendCnt) # of segments approaching max extent ; drill down (E217_SegMaxExtentCnt) # of segments adding extents rapidly ; drill down (E218_SegExtRapidCnt)Received on Mon May 20 2002 - 21:23:30 CEST