SAP MaxDB Monitoring (Add-On)


SAP MaxDB - An Overview

The SAP MaxDB database (formerly SAP DB), developed and supported by SAP AG, is a compliant relational enterprise database system  available on Microsoft Windows, Linux and Unix, and other prominent hardware platforms.

Monitoring SAP MaxDB - What we do

Applications Manager aims to help database admins with tuning their SAP MaxDB, ensuring the availability and performance of production databases, and gathering operating system level data to understand performance issues from your infrastructure and applications to your end-user experience.

  • System And Memory Details - Check which operational state the database is in, gather information about the size and efficiency of the caches of the database. Track the number of accesses to caches and cache hit rates
  • Data and Log Area Stats - Regularly check data and log volume and confirm that there is sufficient free space in the data and log areas and know if the maximum number of database sessions has been reached.  
  • Data Cache and OMS Heap Usage - Analyze the Data Cache with Hit-rate metrics and OMS Heap consumption
  • Lock Activity and Session Management - Get information about the maximum number of available database locks (entries) so you can raise the value of maximum locks allowed when the set number approaches the number of available locks. Monitor Schema Statistics like table sizes and growth. Identify the top tables in MaxDB query.
  • Backups and Backup Template Metrics - Regularly check whether your backups are intact and have not failed. Generate the backup history to display backups have been carried out and whether they were successful. Monitor the log backup and the archiving for the backup files. Check regularly that automatic log backup is activated.

Creating a new SAP MaxDB monitor

Prerequisites for setting up SAP MaxDB monitoring:

  • sapdbc.jar should be copied to Appmanager_Home\working\classes folder. By default, this jar will be available under  <MaxDB_InstallationPath>\runtime\jar\sapdbc.jar.

Note: X-Server in SAP MaxDB should be running when the user sets up the SAP MaxDB Monitor

Creating a new Monitor

To create a new SAP MaxDB monitor, follow the steps given below:

  1. Click on New Monitor link.
  2. Select SAP MaxDB under the Database category.
  3. In the add monitor window, enter the Host Name of MaxDB server.
  4. Enter the Port as X-Server port (default port is 7210).
  5. Enter your Database Username and Password.
  6. Enter the Schema /Database Name.
  7. Enter Timeout in minutes i.e the Session Timeout.
  8. Click Add Monitor.

Monitored Parameters

Overview

Parameter Description
SYSTEM DETAILS
Database State The State of the Database. A database can be in one of the following operational states.
  • ONLINE: The database is running and can be used by database system administrator, DBM operators and database users with the corresponding authorizations.
  • ADMIN: The database is only available for administration tasks.
  • OFFLINE: The database is not running.
Instance Type The database instance type:
  • OLTP
  • LiveCache (You can only use liveCache technology with SAP applications)
DB Speed  
Running Directory The run directory of the MaxDB database where the database system saves configuration and log files.
Kernel Version The database kernal version.
MEMORY OVERVIEW
I/O Buffer Cache The I/O buffer cache is the alloted working memory area used by the database system for I/O operations (data cache, converter and other caches).
Data Cache The alloted size of the data cache.
Converter The alloted size of the converter cache.
File/Directory Used Size The space currently used by the file directories in MB.
Catalog Cache The size of the catalog cache that is available for the database user to maintain the frequently accessed sections of the catalog.
Shared SQL Data The size of shared SQL caches. The shared SQL caches hold already executed SQL queries, thus memory is kept to a minimum.
Sequence Cache The size of sequence cache. The sequence cache stores current values of database sequences.
OMS Heap The size of the OMS heap portion of main memory.
Log Queue Cache The size of the log queues. The log queue contains the log entries of the current transactions (redo log entries) and writes them to the log volumes using the log writer.
Block Allocator The amount of memory currently used by block allocators in MB.
Unused Size The free space in the I/O buffer cache in MB. Ideally, this value should be high. A very low value of this measure indicates the need to reset the CACHE_SIZE.
IO Management The current size of the I/O management.
Index Creation The current rate at which indexes were being created.
DATA CACHE
Data Cache The alloted size of the data cache.
OMS Data The size of the OMS heap.
SQL Data The size of area occupied by SQL data.
Used Pin Area The size of the used pin area. (Data that is to be kept for a longer time from being displaced from the data cache is held in a special area of the data cache, the pin area.)
History/Undo  
Changed Data  

Data Area

Parameter Description
DATA AREA INFO
Total Data Area The total size of the data area in MB.
Used Data Area The memory in data area that is currently used for data.
Used Data Area The percentage of memory in data area that is currently used for data.
Free Data Area The data area that is currently available for data.
Free Data Area The percentage of data area that is currently available for data.
Permanent Data Size The data area that is currently used for permanent data.
Temporary Data Size The data area that is currently used for temporary data.
Permanent Converter Size The space in the I/O buffer cache that is permanently used by the converter.
Temporary Converter Size The space in the I/O buffer cache that is temporarily used by the converter.
Size of an incremental backup The size of an incremental backup. An incremental backup contains only data that is new or has changed since the last backup, regardless of the type.
Shadow Data Size  
DATA VOLUME
Volume Id The unique identifier of the Data Volume.
Total Volume Size (MB) The total size of the data volume, both used and free.
Used Volume Size (MB) The amount of data that has been written to the volume in the data area.
Used Volume Size (%) The percentage amount of data that has been written to the volume in the data area.
Path   The Data Volume Path.

Log Area

Parameter Description
LOG AREA INFO
Logging Status The logging status, i.e if logging is currently happening (The database system writes the redo log entries of transactions in the log segments of the log area.) Possible values are:
  • Yes= Logging is currently happening.
  • No= Logging is not happening.
Auto Overwrite Specifies whether overwrite mode is activated for the log area. Possible Values are:
  • YES= The Log area is overwritten cyclically, log backup is not possible;
  • NO= The log area is not in overwrite mode.
Log Writer Status Specifies if Log Writer is Enabled. The log writer writes the redo log entries from the log queues to the log area. The log writer is a task in the user kernel thread. Possible Values are:
  • YES= The Log Writer is not enabled.
  • NO= The Log Writer is enabled.
Device State The state of the device. Possible Values are:
  • Active
  • Passive
Is Log Area Full Specifies if the log area is full (no space left on the log volumes). Possible Values are:
  • YES - The log area is full.
  • NO - The log area is not full.
Flush Mode  
Total Log Area The total area taken up by all the log volumes in a database instance.
Used Log Area The size of the log area currently utilized in MB.
Used Log Area The percentage size of the log area currently utilized.
LOG VOLUMES
Volume Id The unique identifier of the Log Volume.
Total Size (MB) The total size of the log volume, both used and free in MB.
Used Volume Size  (MB) The amount of logs that has been written to the volume in the log area in MB.
Used Volume Size  (%) The percentage amount of logs that have been written to the volume in the log area.
Path   The Log Volume path.

Session

Parameter Description
SESSION
Max No.of Sessions The maximum number of sessions a user can open on the database.
Used Sessions The number of currently active database sessions.
Used Sessions Percentage The percentage of currently active database sessions.

Cache Stats

Parameter Description
CACHE HIT RATE
Cache Name The name of the cache.
Hit Rate The time data was retrieved from the database cache in the last measurement period.
CACHE STATISTICS
Name The name of the cache.
Access Count The number of times the cache was accessed.
Successful Access Count The number of times the cache was successfully accessed.
Unsuccessful Access Count The number of times the cache access was unsuccessful.
Hit Rate (%)   The percentage of time data was retrieved from the database cache in the last measurement period.

OMS Heap

Parameter Description
OMS HEAP STATISTICS
Allocator Name The name of the memory allocating process.
Reserved Memory (KB) The total memory allocated by the process.
Used Heap (KB) The current memory usage.
Used Heap (%) The percentage of current memory usage.
Max Heap Usage (KB)    The maximum value allowed for the size of the heap.

Schema and Tables

Parameter Description
SCHEMA DETAILS
Schema Name The name of the schema.
Tables Count The number of system tables in the Schema.
Schema Used Size (MB) The space used for tables and indexes that belong to the schema in MB.
TOP 10 LARGE TABLES
Schema Name The name of the schema.
Table Name The name of the table.
Table Used Size (KB) The amount of space used by the table in KB.
Row Count The number of rows in the table.

Resource Monitor

Parameter Description
TOP 10 FREQUENTLY CALLED QUERIES
Cmd ID The command ID.
SQL Query The SQL Query called.
Called Count The number of queries called.
Row Reads The number of full-row reads.
Virtual Reads The number of reads (page accesses).
Collision on SQL Locks The number of SQL lock requests that could not be satisfied.
Collision on internal Locks The number of internal lock requests that could not be satisfied.
I/O access The number of I/O accesses to disk (reading and writing).
Rows fetched The number of rows fetched.

Locks

Parameter Description
LOCKS USAGE
Maximum Locks The number of available locks in the lock list
Used Locks The number of used locks.
LOCK STATISTICS
DeadLocks The number of situations where at least two transactions collided with each other due to held and requested locks in such a way that this collision can be resolved only by implicitly ending a transaction.
SQL Lock Collisions The number of SQL lock requests that could not be satisfied (immediately).
OMS Lock Collisions The number of OMS lock requests that could not be satisfied (immediately).
Lock Escalations The number of lock escalations
Row Locks The number of row locks held.
Table Locks The number of table locks held.
SQL Request Timeout Locks The number of SQL lock requests that could not be satisfied because they had exceeded the maximum wait time.
OMS Request Timeout Locks The number of OMS lock requests that could not be satisfied because they had exceeded the maximum wait time.

Backup

The number of pages read/written during the I/O operation

Parameter Description
LAST/CURRENT BACKUP DETAILS
Latest ThreadID The Id of the latest SAP MaxDB thread created inside the SAP MaxDB kernel process.
Latest Backup Thread Name The name of the latest SAP MaxDB thread created inside the SAP MaxDB kernel process.
Automatic Log Backup Specifies if Automatic Log Backup is enabled. If you have activated Automatic Log Backup, the database system backs up the redo log entries automatically
Latest Backup IO Count The number of I/O operations.
Latest Backup IO Page Count The number of pages read/written during the I/O operation.
Latest Backup IO Time The total time of the I/O operations.
Latest Backup Pending IO Count The number of pending I/O operations.
Latest Backup Path The latest data backup path.
RECENT BACKUP TEMPLATE DETAILS
Backup Template Name The name of the backup template. This has no influence on the name of the backup created later using this backup template.
Backup ID The template ID.
Backup Action The backup action that is scheduled in the template (for example, data backups, log backups, updates of SQL Optimizer statistics, and database structure checks). These are then performed at the specified time.
Backup Start Time The start time of the backup.
Backup End Time The end time of the backup.
Backup Size (Pages) The size of full and incremental backups together.
Backup Result Result obtained in last template backup.
Backup Error Result obtained in last template backup, if any.