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's SAP Monitoring 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:

Click on New Monitor link.
Select SAP MaxDB under the Database category.
In the add monitor window, enter the Host Name of MaxDB server.
Enter the Port as X-Server port (default port is 7210).
Enter your Database Username and Password.
Enter the Schema /Database Name.
Enter Timeout in minutes i.e the Session Timeout.
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.