Database Monitor

AppPerfect Agentless Monitor can monitor your Oracle, MS SQL Server, MySQL, DB2 and Sybase databases for performance and robustness.

Oracle 8/9/10g

The Oracle Database Monitor, monitors the following details about an Oracle database instance.

SGA related Attributes

• Buffer Cache Hit Rate: The buffer cache hit ratio is a measure of the proportion of requests for data which is satisfied by data already in the buffer cache. Higher ratios are better as access to data in memory is speedier than an I/O operation to disk.
• SQL Cache Hit Rate (also called Library Cache Hit ratio): This ratio indicates the number of pin requests which result in pin hits. A pin hit occurs when the SQL or PL/SQL code you wish to execute is already in the library cache and is valid to execute.
• Row Cache Hit Rate: The number of times an object was requested and not in memory versus the total number of requests for that data.
• Redo Log Space Requests: This counts the number of times that a server process had to wait to acquire an entry in the redo log.
• Shared Pool Size: Indicates the size (in bytes) of the shared pool. The shared pool contains shared cursors and stored procedures.
• DB Block Buffer: Indicates the number of database blocks cached in memory of the SGA.
• Log Buffer: Indicates the number of bytes allocated to the redo log buffer in the SGA.

System Statistic related Attributes

• 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.
• 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.
• Redo Log Space Wait Ratio: A redo space wait is when there is insufficient space in the redo buffer for a transaction to write redo information. It is an indication that the redo buffer is too small given the rate of transactions occurring in relation to the rate at which the log writer is writing data to the redo logs.
• 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.
• 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 recorded as set operations or vice versa. Use of the array interface will also affect this ratio.
• 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.
• 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.
• Continued Row Ratio: This ratio monitors the percentage of rows retrieved that extend over more than one block, which is called chained rows.
• 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.
• 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.
• 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.
• 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.
• 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.
• User Calls Per Parse Rate: 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.
• 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.

MS SQL Server

The following attributes are monitored for MS SQL Server:

SQL Statistics

• Batch Requests Rate: The number of batch requests that SQL Server receives per second.
• SQL Compilations Rate: Number of compilations performed by SQL Server per second.

Access Methods

• Full Scans Rate: Defined as the number of unrestricted full scans. These can either be base table or full index scans.
• Page Splits Rate: One cause of excess I/O on an SQL Server is page splitting. Page splitting occurs when an index or data page becomes full, and then is split between the current page and a newly allocated page. While occasional page splitting is normal, excess page splitting can cause performance issues.

Buffer Manager

• Buffer Cache Hit Ratio: Defined as the percentage of pages that were found in the buffer pool without having to incur a read from disk. When this percentage is high your server is operating at optimal efficiency (as far as disk I/O is concerned).
• Checkpoint Pages Rate: Number of pages flushed by checkpoint or other operations that require all dirty pages to be flushed.
• Page Life Expectancy: This tells you, on average, how long data pages are staying in the buffer.
• Lazy Writes Rate: This tracks how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space.
• Free Pages: Total number of pages on all free lists.

Memory Manager

• Memory Grants Pending: Defined as the current number of processes waiting for a workspace memory grant.
• Total Server Memory: This tells you how much memory (in KB) the SQL Server is currently using.
• Target Server Memory: This tells you how much memory (in KB) the SQL Server would like to have.

Databases

• Total Log Growths: Defined as the total number of log growths.
• Total Percent Log Used: Defined as the percentage of space in the log that is in use.
• Total Transaction Rate: Defined as the number of transactions started.
• Total Log Flushes Rate: This measures the number of log flushes per second.

Note 1: The above 4 attributes give summary value for all the databases. All the attributes are also available for individual databases.

Note 2: The list of database names is dynamically generated.

General Statistics

• User Connections: Defined as the number of users connected to the system. Dramatic shifts in this value should be researched.

Latches

• Average Latch Wait Time: Defined as the average latch wait time (in milliseconds) for latch requests that had to wait. If this number is high, your server may be facing contention for its resources.
• Latch Wait Rate: Defined as the number of latch requests that could not be satisfied immediately and required the caller to wait before being granted the latch.
• Total Latch Wait Time: Defined as the number of latch requests that resulted in a deadlock.

Locks

• Average Wait Time: Defined as the average amount of wait time (milliseconds) for each lock request that resulted in a wait.
• Lock Wait Rate: Defined as the number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.
• Number of Deadlocks Rate: Defined as the number of lock requests that resulted in a deadlock.

Backup Device

• Device Throughput Rate: This counter will give you a good feel for how fast your backups are performing.

Replication logreader

• LogReader Delivery Latency: The latency that it takes the Log Reader to move transactions from a database's transaction log until it puts it in the distribution database.

Replication Distributor

• Distribution Delivery Latency: The latency it takes the Distributor Agent to move transactions from the distribution database to the Subscriber database.

MySql

Server Status

• Bytes Received: Number of bytes received from all clients.
• Bytes Sent: Number of bytes sent to all clients.
• Connections: Number of connection attempts to the MySQL server.
• Flush Commands: Number of executed FLUSH commands.
• Handler Commit: Number of internal COMMIT commands.
• Handler Read First: Number of times the first entry was read from an index.
• Handler Read Key: Number of requests to read a row based on a key.
• Handler Read Next: Number of requests to read next row in key order.
• Handler Read Prev: Number of requests to read previous row in key order.
• Handler Read Random: Number of requests to read a row based on a fixed position.
• Handler Read Random Next: Number of requests to read the next row in the datafile.
• Handler Rollback: Number of internal ROLLBACK commands.
• Key Blocks Used: The number of used blocks in the key cache.
• Key Read Requests: The number of requests to read a key block from the cache.
• Key Reads: The number of physical reads of a key block from disk.
• Key Write Requests: The number of requests to write a key block to the cache.
• Key Writes: The number of physical writes of a key block to disk.
• Max Used Connections: The maximum number of connections in use simultaneously.
• Not Flushed Key Blocks: Key blocks in the key cache that have changed but haven't yet been flushed to disk.
• Not Flushed Delayed Rows: Number of rows waiting to be written in INSERT DELAY queues.
• Open Tables: Number of tables that are open.
• Opened Tables: Number of tables that have been opened.
• Select Full Join: Number of joins without keys.
• Select Full Range Join: Number of joins where we use a range search on reference table.
• Select Range: Number of joins where we use ranges on the first table.
• Select Scan: Number of joins where we did a full scan of the first table.
• Select Range Check: Number of joins without keys where we check for key usage after each row.
• Slow Queries: Number of queries that have taken more than long_query_time seconds.
• Sort Merge Passes: Number of Merge Passes the sort algorithm has had to do.
• Sort Range: Number of sorts that were done with ranges.
• Sort Scan: Number of sorts that were done by scanning the table.
• Table Locks Immediate: Number of times a table lock was acquired immediately.
• Table Locks Waited: Number of times a table lock could not be acquired immediately and a wait was needed.
• Threads Cached: Number of threads in the thread cache.
• Threads Connected: Number of currently open connections.
• Threads Created: Number of threads created to handle connections.
• Threads Running: Number of threads that are not sleeping.
• Uptime: How many seconds the server has been up.

Server Attributes

• Back Log: The number of outstanding connection requests MySQL can have.
• BDB Cache Size: The size of the buffer that is allocated for caching indexes and rows for BDB tables.
• BDB Log Buffer Size: The size of the buffer that is allocated for logs for BDB tables.
• BDB Max Lock: The maximum number of locks you can have active on a BDB table.
• Binlog Cache Size: The size of the cache to hold the SQL statements for the binary log during a transaction.
• Bulk Insert Buffer Size: MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE. This variable limits the size of the cache tree in bytes per thread.
• Join Buffer Size: The size of the buffer that is used for full joins (joins that do not use indexes).
• Key Buffer Size: Index blocks for MyISAM and ISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks.
• Transaction Alloc Block Size: The allocation size of memory blocks that are allocated for storing queries that are part of a transaction to be stored in the binary log when doing a commit.
• Transaction Prealloc Block Size: The size of the persistent buffer for transaction_alloc_blocks that is not freed between queries.
• Table Cache: The number of open tables for all threads.
• Read Buffer Size: Each thread that does a sequential scan allocates a buffer of this size for each table it scans.
• Read Random Buffer Size: When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks.
• Sort Buffer Size: Each thread that needs to do a sort allocates a buffer of this size.
• Query Alloc Block Size: The allocation size of memory blocks that are allocated for objects created during query parsing and execution.
• Query Cache Limit: This limit indicates that cache results bigger than this should not be stored.
• Query Cache Size: The amount of memory allocated for caching query results.

DB2

Buffer Pool Related Attributes

• Buffer Pool Hit Ratio: Indicates the number of physical reads against number of logical reads, includes index plus data activity that have gone through the buffer pool.
• Buffer Pool Index Hit Ratio: Indicates the number of physical reads against number of logical reads that have gone through the buffer pool.
• Data Page Hit Ratio: Indicates the number of physical read requests against number of logical read requests for data pages that have gone through the buffer pool.
• Buffer Pool Data Writes: Indicates the number of times a buffer pool data page was physically written to disk.
• Buffer Pool Index Writes: Indicates the number of times a buffer pool index page was physically written to disk.
• Total Buffer Pool Physical Read Time: Provides the total amount of elapsed time spent processing read requests that caused data or index pages to be physically read from disk to buffer pool.
• Total Buffer Pool Physical Write Time: Provides the total amount of time spent physically writing data or index pages from the buffer pool to disk.
• Buffer Pool Asynchronous Data Reads: The number of pages read asynchronously into the buffer pool.
• Buffer Pool Asynchronous Data Writes: The number of times a buffer pool data page was physically written to disk by either an asynchronous page cleaner, or a prefetcher.
• Buffer Pool Asynchronous Index Writes: The number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner, or a prefetcher.
• Buffer Pool Asynchronous Index Reads: The number of index pages read asynchronously into the buffer pool by a prefetcher.
• Buffer Pool Asynchronous Read Time: The total elapsed time spent reading by database manager prefetchers.
• Buffer Pool Asynchronous Write Time: The total elapsed time spent writing data or index pages from the buffer pool to disk by database manager page cleaners.
• Data Pages Copied to Extended Storage: Number of buffer pool data pages copied to extended storage.  
• Index Pages Copied to Extended Storage: Number of buffer pool index pages copied to extended storage.
• Data Pages Copied FROM Extended Storage: Number of buffer pool data pages copied from extended storage.
• Index Pages Copied FROM Extended Storage: Number of buffer pool index pages copied from extended storage.
• Time Waited for Prefetch:  The time an application spent waiting for an I/O server (prefetcher) to finish loading pages into the buffer pool.

Non-Buffered I/O Data Related Attributes

• Direct Reads From Database: The number of read operations that do not use the buffer pool.
• Direct Writes to Database:  The number of write operations that do not use the buffer pool.
• Direct Read Reqs:  The number of requests to perform a direct read of one or more sectors of data.
• Direct Write Reqs: The number of requests to perform a direct write of one or more sectors of data.
• Direct Read Time: The elapsed time (in milliseconds) required to perform the direct reads.
• Direct Write Time: The elapsed time (in milliseconds) required to perform the direct writes.

Catalog Cache Related Attributes

• Catalog Cache Hit Ratio: Indicates the number of cache inserts against cache lookups in the catalog.
• Catalog Cache Overflows: The number of times that an insert into the catalog cache failed due to the catalog cache being full.
• Catalog Cache Heap Full: The number of times that an insert into the catalog cache failed due to a heap-full condition in the database heap.
• Package Cache Hit Ratio: The package cache hit ratio tells you whether or not the package cache is being used effectively.
• Package Cache Overflows: The number of times that the package cache overflowed the bounds of its allocated memory.
• Package Cache High Water Mark: This element indicates the maximum number of bytes the package cache required for the workload run against the database since it was activated.

Log Info Related Attributes

• Total Log Space Used:  The total amount of active log space currently used (in bytes) in the database.
• Total Log Available: The amount of active log space in the database that is not being used by uncommitted transactions (in bytes).
• Maximum Secondary Log Space Used: The maximum amount of secondary log space used (in bytes).
• Maximum Total Log Space Used: The maximum amount of total log space used (in bytes).
• Secondary Logs Currently Allocated: The total number of secondary log files that are currently being used for the database.
• Number of Log Pages Read: The number of log pages read from disk by the logger.
• Number of Log Pages Written: The number of log pages written to disk by the logger.

Application Status Related Attributes

• Connects Since Database Activation: Total number of connections since first database connection
• Maximum Connections: The highest number of simultaneous connections to the database since the first application connected to the database.
• Secondary Connections: The number of connections made by a subagent to the database at the node.
• Applications Connected Currently: Indicates the number of applications that are currently connected to the database.
• Applications Executing Currently: Indicates the number of applications that are currently connected to the database, and for which the database manager is currently processing a request.
• Number of Associated Agents: The number of subagents associated with this application.

Database Manager Configuration Related Attributes

• Remote Connections: The total number of current connections initiated from remote clients to the instance of the database manager that is being monitored.
• Remote Connections Executing: The number of remote applications that are currently connected to a database and are currently processing a unit of work within the database manager instance being monitored.
• Local Connections: The number of local applications that are currently connected to a local database within the database manager instance being monitored.
• Local Connections Executing: The number of local applications that are currently connected to a local database within the database manager instance being monitored and are currently processing a unit of work.
• Committed Private Memory: The amount of private memory that the instance of the database manager has currently committed at the time of the snapshot.
• Maximum Coordinated Agents: The maximum number of coordinating agents working at one time.
• Agents Registered: The number of agents registered in the database manager instance that is being monitored (coordinator agents and subagents).
• Agents Waiting: The number of agents waiting for a token so they can execute a transaction in the database manager.
• Maximum Agents Registered: The maximum number of agents that the database manager has ever registered, at the same time, since it was started (coordinator agents and subagents).
• Maximum Agents Waiting: The maximum number of agents that have ever been waiting for a token, at the same time, since the database manager was started.
• Number of Idle Agents:  The number of agents in the agent pool that are currently unassigned to an application and are, therefore, "idle".
• Agents Assigned From Pool: The number of agents assigned from the agent pool.
• Agents Created Due to Empty Pool: The number of agents created because the agent pool was empty.

Sort Related Attributes

• Total Sort Heap Allocated: The total number of allocated pages of sort heap space for all sorts at the level chosen and at the time the snapshot was taken.
• Average Sort Time: This value shows the average sort time for all sorts performed by all applications connected to a particular database in the past interval.
• Sort Overflows: Sort overflows are sorts that ran out of sort heap and required disk space for temporary storage.
• Active Sorts: Number of sort operations running in the Database Manager

Hash_Joins_Related_Attributes

• Total Hash Joins:  The total number of hash joins executed.
• Hash Join Threshold: The total number of times that a hash join heap request was limited due to concurrent use of shared or private sort heap space.
• Total Hash Loops: The total number of times that a single partition of a hash join was larger than the available sort heap space.
• Hash Join Overflows: The number of times that hash join data exceeded the available sort heap space.
• Hash Join Small Overflows: The number of times that hash join data exceeded the available sort heap space by less than 10%.

Fast Communication Manager

• FCM Buffers Currently Free: This element indicates the number of FCM buffers currently free
• Minimum FCM Buffers Free: The lowest number of free FCM buffers reached during processing.

Locks and Deadlocks

• Locks Held: The number of locks currently held.
• Total Lock List Memory In Use: The total amount of lock list memory (in bytes) that is in use.
• Deadlocks Detected: The total number of deadlocks that have occurred.
• Number of Lock Escalations: The number of times that locks have been escalated from several row locks to a table lock.
• Exclusive Lock Escalations: The number of times that locks have been escalated from several row locks to one exclusive table lock, or the number of times an exclusive lock on a row caused the table lock to become an exclusive lock.
• Number of Lock Timeouts: The number of times that a request to lock an object timed-out instead of being granted.
• Lock Waits: The total number of times that applications or connections waited for locks.
• Time Waited On Locks: The total time elapsed waiting for a lock.
• Agents Waiting on Lock: Indicates the number of agents waiting on a lock.

SQL Statement Activity

• Commit Statements Attempted: The total number of SQL COMMIT statements that have been attempted.
• Rollback Statements Attempted: The total number of SQL ROLLBACK statements that have been attempted.
• Internal Automatic Rebinds: The number of automatic rebinds.

Sybase

The following attributes are monitored for the Sybase database.

• Connections : The number of User Connections.
• NumDeadlocks : The number of Deadlocks.
• LockWaits : The number of processes that have waited longer than LockWaitThreshold seconds.

CPU

• CPU Time : The total time (in seconds) the engine has been running.
• System CPU Time : The total time (in seconds) the engine has been executing system database services.
• User CPU Time : The total time (in seconds) the engine has been executing user commands.

Data Cache

• Physical Reads : The number of buffers read into the cache from disk.
• Logical Reads : The number of buffers retrieved from the cache.
• Physical Writes : The number of buffers written from the cache to disk.

Procedure Cache

• Requests : The number of stored procedures requested.
• Loads : The number of stored procedures loaded into cache.
• Writes : The number of times a procedure was normalized and the tree written back to sysprocedures

Worker Thread

• Parallel Queries : The number of parallel queries that were attempted.
• Plans Altered : The number of plans altered due to worker processes not being available.
• Worker Memory : The amount of memory currently in use by worker processes.
• Total Worker Memory : The amount of memory configured for use by worker processes.

Cache Pool

• Physical Reads : The number of buffers that have been read from disk into the pool.
• Pages Touched : The number of bytes that are currently being used within the pool.
• Pages Read : The number of pages read into the pool.

Locks

• Average Wait Time : The average time (in seconds) that the lock request has not been granted.

Deadlocks

• Average Wait Time : The average time (in milliseconds) that the waiting processes were blocked before the deadlock was resolved.

Databases

• Append Log Requests : The number of semaphore requests when attempting to append to the database transaction log.
• Append Log Waits : The number of times a task had to wait for the append log semaphore to be granted.

Note 1: The above 2 attributes give summary value for all the databases. All the attributes are also available for individual databases.

Note 2: The list of database names is dynamically generated.