Oracle DB Monitoring

Overview

The Oracle Database is an object-relational database management system. The Oracle Database monitor captures performance metrics from Oracle databases (version 10g or later).

It retrieves metrics from the data dictionary and groups them in four categories:
•Activity: It provide data of throughput of transactions, SQL executes, I/O reads and writes, average active sessions, etc.
•Resource Utilization: What database resources are currently in use (sessions, open cursors, shared pool, etc.).
•Efficiency: Ratios and response times as indicators of the instance’s efficiency.
•WaitClass Breakdown: Every wait event belongs to a class of wait event.

Oracle DB Metrics

Oracle DB Activity Stats

Graph NameData TypeDescription
Oracle Current Active SessionsRateNumber of active sessions.
Oracle Average Active SessionsRateAverage number of active sessions.
Oracle Average Active Sessions/ logical CPURateThis shows the average load the database imposes on each logical CPU (i.e. cores or hyperthreads).
Oracle Current OS LoadRateHost CPU load.
Oracle DB Block Changes/SecRateDatabase blocks changed in the buffer cache.
Oracle DB Block Changes/TxnRateDatabase blocks changed in the buffer cache per SQL transaction.
Oracle DB Block Gets /SecRateDatabase blocks read from the buffer cache.
Oracle DB Block Gets/ TxnRateDatabase blocks read from the buffer cache per SQL transaction.
Oracle Executions/SecRateSQL executions per second
Oracle Executions/TxnRateSQL executions per SQL transaction.
Oracle I/O MB/SecRateI/O Megabytes per Second.
Oracle Logical Reads/SecRateLogical Reads Per Second
Oracle Physical Reads/SecRatePhysical Reads Per Second
Oracle Physical Read Total Bytes/SecRatePhysical read total bytes per second
Oracle Physical Write Total Bytes/SecRatePhysical write total bytes per second
Oracle User Calls/SecRateUser calls per second.
Oracle User Calls/TxnRateUser calls per transaction.
Oracle User Commits/SecRateUser commits per Second.
Oracle User Rollbacks/SecRateUser rollbacks per Second.
Oracle GC CR Block Received/SecRateGC CR block received per second.

Oracle DB Resource Utilization Stats

Graph NameData TypeDescription
Oracle Open SessionsSampleNumber of all database open sessions.
Oracle Open Sessions (Pct)SampleIt shows open sessions vs. DB parameter sessions in percentage.
Oracle Open Cursors (Pct)SampleIt shows open cursors in a session vs. DB parameter open_cursors in percentage.
Oracle Free Shared Pool (Pct)SampleFree Shared Pool in percentage
Oracle UsedTemp Space (MB)SampleUsed temporary tablespace in Megabytes.
Oracle Total PGA Allocated (MB)SampleRAM used for sorts, hashes and other operation in Megabytes.
Oracle Network Traffic Volume/SecRateNetwork traffic volume per second.

Oracle DB Waitevents Stats

Graph NameData TypeDescription
Oracle Administrative Wait Time (Sec)RateTime waited in Administrative wait class in last minute.
Oracle Application Wait Time (Sec)RateTime waited in Application wait class in last minute.
Oracle Commit Wait Time (Sec)RateTime waited in Commit wait class in last minute.
Oracle Concurrency Wait Time (Sec)RateTime waited in Concurrency wait class in last minute.
Oracle Configuration Wait Time (Sec)RateTime waited in Configuration wait class in last minute.
Oracle CPU Wait Time (Sec)RateTime waited in CPU wait class in last minute.
Oracle Network Wait Time (Sec)RateTime waited in Network wait class in last minute.
Oracle Scheduler Wait Time (Sec)RateTime waited in Scheduler wait class in last minute.
Oracle System I/O Wait Time (Sec)RateTime waited in System I/O wait class in last minute.
Oracle User I/O Wait Time (Sec)RateTime waited in User I/O wait class in last minute.
Oracle Cluster Wait Time (Sec)RateTime waited in Cluster wait class in last minute.
Oracle Other Wait Time (Sec)RateTime waited in Other wait class in last minute.

Oracle DB Efficiency Stats

Graph NameData TypeDescription
Oracle Database CPU Time RatioSamplePercentage of CPU time against database time.
Oracle Database Wait Time RatioSampleComplementary to “Database CPU Time Ratio” (% of non-CPU waits).
Oracle Memory Sorts RatioSamplePercentage of sort operations that were done in RAM (as opposed to disk).
Oracle Execute Without Parse RatioSamplePercentage of (soft and hard) parsed SQL against all executed SQL.
Oracle Soft Parse RatioSampleRatio of soft parses to hard parses.
Oracle Response Time /Txn (ms)SampleResponse time per transaction in millisecond.
Oracle Service Response Time (ms)SampleSQL service response time (ms) in millisecond.
Oracle Full Index Scans/SecRateFull index scans per second.