Enhancement in APM DB Extension Metrics (DB2 and Oracle)

Products

• DX SaaS

Issue/Introduction

Our database team is looking for below metrics in the DB monitoring\. Please create ER and let us know the further plans of implementation.

Oracle:

Category	Name	Description
OS Resource	Disk Activity	Disk Device Busy (%)
OS Resource	Load	Run Queue Length (5 minute average,per cpu)
ASM	ASM Alert Log	Archive Hung Error Stack
ASM	ASM Alert Log	Data Block Corruption Error Stack
ASM	ASM Alert Log	Media Failure Error Stack
ASM	ASM Cluster File System	ASM Cluster File System Used (%)
ASM	ASM Cluster File System State	ASM Cluster File System Availability
ASM	ASM Cluster File System State	ASM Cluster File System Mount State
ASM	Disk Group Usage	Disk Group Used %
Database	Archive Area	Archive Area Used (%)
Database	Database Job Status	Broken Job Count
Database	Database Job Status	Failed Job Count
Database	Data Guard Status	Data Guard Status
Database	Monitoring User Expiry	Account Expiry In Hours
Database	Temporary File Status	Status
Database	DB Alert Log	Archiver Hung Alert Log Error
Database	DB Alert Log	Data Block Corruption Alert Log Error
Database	DB Alert Log	Generic Alert Log Error
Database	DB Alert Log	Media Failure Alert Log Error

DB2:

Category	Name	Description
Locks	Percentage of lock waiting
	lock_waits
	deadlocks
	Lock upgrade
	lock_timeouts
Performance Insights	Read effectiveness(index read efficiency)	rows read Is the number of rows read and Rows selected is the returned result set reflecting how many rows DB2 needs to read in order to obtain a line of data. If much larger than the index value, it means that DB2 cannot filter the result set based on the valid index, meaning that SQL needs to be adjusted.
Performance Insights	Synchronous read percentage	the percentage for synchronous I/O Read
Performance Insights	package cache hit ratio	Indicates how many query statements can be found directly in the package cache.
Performance Insights	Catalogue the buffer insert scale	In each partition of the system, a space is allocated in CATALOGCACHE_SZ to buffer the catalog of information. In the daily operation of a database, the query cataloguing table is a very frequent operation.
Performance Insights	Each asynchronous reading time in the table space	The average physical read / write time is usually about 1 to 10 milliseconds (depending on the performance and cache of the storage system)
Performance Insights	The Direct I / O time	I / O accessed directly from the disk without passing through the buffer, mainly for access to the LONG / LOB data;From 1 to 10 ms for the in general
Performance Insights	Data, the index page  cleared	It represents whether the page clear thread can effectively swipe the dirty pages into the disk in the background
Performance Insights	Sorting overflow ratio	Sort overflow is that when the sorting memory is not enough, the data needs to be sorted using a temporary space. In general, expect the data to be done in memory as much as possible.
Performance Insights	Average ranking time	The execution time of a statement includes lock wait, data reading time (I / O) and sorting time (both logical reading time and sorting time are both user CPU time). So with the average sorting time in the database versus the speed at which the average statement returns, you can estimate roughly how much time you spend executing a statement.
Performance Insights	The average number of orders per transaction	For OLTP applications, due to the short nature of each transaction, we need to minimize the number of orders required for each exchange.
Performance Insights	Log write speed	The speed of log writing sometimes has a decisive impact on frequently committed application performance.
Performance Insights	Average outcome set size	Rows selected Represents the result set returned, and Select SQL statements executed represents the number of executions of the query statement. The average result set (Avg _ Result _ Set) is used to represent the average number of resulting rows returned by each Select SQL statement.
Performance Insights	Number of query SQL statements contained for each transaction	For OLTP systems, the number of queries performed per transaction is generally less than 10. If the transaction is too long, it may cause some locks, etc., affecting the concurrency performance.
Performance Insights	Number of add and deletion statements per transaction	In the OLTP system, it is recommended not to use too much data to change statements (insert, update, delete) in the same transaction. Too many such statements in a single transaction will cause a large number of locks, easy to cause lock waiting or even deadlock. At the same time, too long transactions may cause too long activity logs, resulting in full log space.
tablespace usage	Automatically resizing, tablespace utilization	When used to track memory consumption for each DMS tablespace, which has a maximum size and can be automatically sized to reach a maximum, the DMS tablespace is considered full.
tablespace usage	Table space utilization	If auto-resizing is not enabled on the tablespace, the metric can be used to track the memory consumption for each DMS tablespace;

Resolution

DE660291

- ER defect created