Performance metrics provide information regarding the performance at various snapshot levels. Performance metrics consist of the following categories:
The metrics in this category provide performance metrics for the Agent at the application snapshot level.
Default Collection Interval — Every 15 minutes
Table 2-21 IO Agents Performance Metrics
| Metric | Description and User Action |
|---|---|
|
Database Name (key column) |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Cleans for Steals |
Number of times a page cleaner was invoked because a synchronous write was needed during the victim buffer replacement for the database. |
|
Cleans for Threshold |
Number of times a page cleaner was invoked because a buffer pool had reached the dirty page threshold criterion for the database. |
The metrics in this category provide performance metrics for all the bufferpools in the database.
Default Collection Interval — Every 15 minutes
Table 2-22 Database Performance Metrics
| Metric | Description and User Action |
|---|---|
|
Database Name (key column) |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Average Data Write Rate |
Pool data write rate, which equals: (pool_data_writes/pool_write_time) |
|
Average Index Write Rate |
Pool index write rate, which equals: (pool_index_writes/pool_write_time) |
|
Average Page Read Rate |
The rate equals: (pool_data_p_reads + pool_index_p_reads )/pool_read_time |
|
Average Page Write Rate |
The rate equals: (pool_data_writes + pool_index_writes)/pool_write_time |
|
Database Buffer Pool Data Hit Ratio (%) |
The ratio equals:((1-(pool_data_p_reads/pool_data_l_reads))*100)) |
|
Database Buffer Pool Hit Ratio (%) |
The ratio equals: (1-((pool_data_p_reads + pool_index_p_reads)/ (pool_data_l_reads + pool_index_l_reads )))*100 The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Logical Data Read Rate |
The rate equals: (pool_data_l_reads /pool_read_time) |
|
Logical Index Read Rate |
The rate equals: (pool_index_l_reads /pool_read_time) |
|
Physical Data Read Rate |
The rate equals: (pool_data_p_reads/pool_read_time) |
|
Physical Index Read Rate |
The rate equals: pool_index_p_reads/pool_read_time |
|
Physical Data Reads |
Number of read requests that required I/O to get data pages into the buffer pool. |
|
Physical Index Reads |
Number of physical read requests to get index pages into the buffer pool. |
|
Pool Asynchronous Data Reads |
Number of data pages read asynchronously to the buffer pool by prefetchers. |
|
Pool Asynchronous Data Writes |
Number of times a buffer pool data page was physically written to disk by either an asynchronous page cleaner or a prefetcher. |
|
Pool Asynchronous Index Reads |
Number of index pages read asynchronously to the buffer poll by prefetchers. |
|
Pool Asynchronous Index Writes |
Number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner or a prefetcher. |
|
Pool Asynchronous Read Time (microseconds) |
Number of times a buffer pool data page was physically read from disk by an asynchronous page prefetcher. |
|
Pool Asynchronous Write Time (microseconds) |
Number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner or prefetcher. |
|
Pool Data Writes |
Number of times the buffer pool data page was physically written to the disk. |
|
Pool Index Writes |
Number of times the buffer pool index page was physically written to the disk. |
|
Logical Data Reads |
This count includes accesses to data that is already in the buffer pool when the database manager needs to process the page and read into the buffer pool before the database manager can process the page. |
|
Logical Index Reads |
Indicates the number of logical read requests to get index pages into the buffer pool. |
|
Pool Read Time (microseconds) |
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. |
|
Pool Write Time (microseconds) |
Total amount of time spent physically writing data or index pages from the buffer pool to disk. |
|
Synchronous Data Read Rate |
Total synchronous read rate, which equals: ((pool_read_time-pool_async_read_time == 0) ? 0 :((pool_data_p_ reads-pool_async_data_reads )/(pool_read_time-pool_async_read_ time))) |
|
Synchronous Data Write Rate |
Pool data synchronous write rate, which equals: ((pool_data_writes - pool_async_data_writes )/(pool_write_time-pool_async_write_time)) |
|
Synchronous Index Read Rate |
Index synchronous read rate, which equals: (pool_data_p_reads-pool_async_index_reads )/(pool_read_time-pool_async_read_time) |
|
Synchronous Index Write Rate |
Index synchronous write rate, which equals: ((pool_index_writes - pool_async_index_writes )/(pool_write_time-pool_async_write_time)) |
The metrics in this category provide performance metrics for the individual bufferpools in the database.
Default Collection Interval — Every 15 minutes
Table 2-23 Bufferpool Performance Metrics
| Metric | Description and User Action |
|---|---|
|
Buffer Pool Name (key column) |
Name of the buffer pool. |
|
Individual Buffer Pool Hit Ratio (%) |
Buffer pool hit ratio, which equals: (1-((pool_data_p_reads + pool_index_p_reads)/ (pool_index_l_reads + pool_index_l_reads )))*100) The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Database Name |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Database Alias |
Alias for the database. |
|
Database Path |
Physical location of the database. |
|
Pool Asynchronous Data Reads |
Number of data pages read asynchronously to the buffer poll by prefetchers. |
|
Pool Asynchronous Index Reads |
Number of index pages read asynchronously to the buffer poll by prefetchers. |
|
Pool Asynchronous Index Writes |
Number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner or prefetcher. |
|
Pool Asynchronous Read Time (microseconds) |
Number of times a buffer pool data page was physically read from disk by an asynchronous page prefetcher. |
|
Pool Asynchronous Write Time (microseconds) |
Number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner or prefetcher. |
|
Pool Data Writes |
Number of times the buffer pool data page was physically written to the disk. |
|
Pool Index Writes |
Number of times the buffer pool index page was physically written to the disk. |
|
Logical Data Reads |
This count includes accesses to data that is already in the buffer pool when the database manager needs to process the page and read into the buffer pool before the database manager can process the page. |
|
Logical Index Reads |
Indicates the number of logical read requests to put index pages into the buffer pool. |
|
Physical Index Reads |
Indicates the number of physical read requests to put index pages into the buffer pool. |
|
Physical Data Reads |
Number of read requests that required I/O to put data pages into the buffer pool. |
|
Pool Read Time (microseconds) |
Provides the total amount of elapsed time spent processing read requests that caused data or index pages to be physically read from the disk to the buffer pool. |
|
Pool Write Time (microseconds) |
Total amount of time spent physically writing data or index pages from the buffer pool to the disk. |
The metrics in this category provide performance information for the package and the catalog cache of the database.
Default Collection Interval — Every 15 minutes
Table 2-24 Cache Performance Metrics
| Metric | Description and User Action |
|---|---|
|
Database Name (key column) |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Catalog Cache Hit Ratio (%) |
Catalog cache hit ratio, which equals: 1-(cat_cache_inserts /cat_cache_lookups))*100 The hit ratio is a percentage indicating how well the catalog cache is helping to avoid actual accesses to the catalog on disk. A high ratio indicates it is successful in avoiding actual disk I/O accesses. |
|
Catalogue Cache Heapfull |
The number of times that an insert into the catalog cache failed due to a heap-full condition in the database heap. |
|
Catalog Cache Inserts |
Number of inserts performed. The hit ratio is 1-(CCI/CCL). |
|
Catalog Cache Lookups |
Number of times the catalog cache was referenced to obtain table description information. |
|
Catalog Cache Overflows |
Number of times that the catalog cache overflowed the bounds of its allocated memory. |
|
Package Cache Hit Ratio (%) |
Package cache hit ratio, which equals: 1-(pkg_cache_inserts /pkg_cache_lookups))*100 The hit ratio is a percentage indicating how well the package cache is helping to avoid reloading packages and sections for static SQL from the system catalogs as well as helping to avoid recompiling dynamic SQL statements. A high ratio indicates it is successful in avoiding these activities. |
|
Package Cache Inserts |
Number of inserts performed. The hit ratio is 1-(PCI/PCL). |
|
Package Cache Lookups |
Number of times the package cache was referenced to obtain a section or a package. |
|
Package Cache Max Used (Bytes) |
Largest size reached by the package cache. |
|
Package Cache Overflows |
Number of times that the package cache overflowed the bounds of its allocated memory. |
The metrics in this category provide performance information for the log input and output including the number of reads and writes in the logs.
Default Collection Interval — Every 15 minutes
Table 2-25 Log IO Performance Metrics
| Metric | Description |
|---|---|
|
Database Name (key column) |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Cleans Logging Threshold |
Number of times a page cleaner was invoked because the logging space used had reached a predefined criterion for the database. |
|
Log Reads |
Number of log reads. |
|
Log Writes |
Number of log writes. |
The metrics in this category provide the values of the workspace provided and the locklist set in the database.
Default Collection Interval — Every 15 minutes
Table 2-26 Memory Manager Performance Metrics
| Metric | Description |
|---|---|
|
Database Name (key column) |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Private Workspace Max Used |
Largest size that can be reached by private workspace. |
|
Shared Workspace Max Used |
Largest size that can be reached by shared workspace. |
|
Locklist Current Value |
Current value for the total amount of lock list memory that is in use. |
The metrics in this category display the various performance values associated with the sortheap.
Default Collection Interval — Every 15 minutes
Table 2-27 SortHeap Performance Metrics
| Metric | Description and User Action |
|---|---|
|
Database Name (key column) |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Active Sorts |
Number of sorts in the database that currently have an allocated sort heap. |
|
Average Active Sorts Rate |
Rate of active sorts in the last interval, which equals: (active_sorts-_active_sorts )/__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Hash Join Overflow Rate |
Rate of hash join overflow in the last interval, which equals: (hash_join_overflow-_hash_join_overflow) /__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Hash Join Rate |
Rate of hash joins in the last interval, which equals: (total_hash_joins-_total_hash_joins )/__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Hash Join Small Overflow Rate |
Rate of the small hash join overflow in the last interval, which equals: (hash_join_small_overflow-_hash_join_small_overflow )/__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Hash Join Small Overflow Ratio |
Ratio of the small hash join overflow. |
|
Average Hash Loops Rate |
Rate of hash loops in the last interval, which equals: (total_hash_loops-_total_hash_loops)/ __interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Piped Sorts Rejected Rate |
Rate of piped sorts rejection in the last interval, which equals: ((piped_sorts_requested-piped_sorts_accepted)-(_piped_sorts_requested-_piped_sorts_accepted)) /__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Post Threshold Joins Rate |
Rate of post threshold joins in the last interval, which equals: (post_threshold_ hash_joins-_post_threshold _hash_joins)/__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Post Threshold Sorts Rate |
Rate of post threshold sorts in the last interval, which equals: (post_threshold_sorts-_post_threshold_sorts ) /__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Sort Heap Pages Used |
Average sort heap space used, which equals: (sort_heap_allocated /active_sorts) The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Sort Time (ms) |
Average time per sort, which equals: (total_sort_time/total_sorts) The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Sorts Overflow Rate |
Rate of sort overflow in the last interval, which equals: (sort_overflow-_sort_overflow )/__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Sorts Overflow Ratio |
Ratio of sort overflow. |
|
Average Sorts Rate |
Rate of sorts in the last interval, which equals: (total_sorts-_total_sorts )/__interval The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Piped Sorts Accepted |
Number of piped sorts that have been accepted. |
|
Piped Sorts Requested |
Number of piped sorts that have been requested. |
|
Post Threshold Hash Joins |
Total number of times that a hash join heap request was limited due to concurrent use of shared or private sort heap space. |
|
Post Threshold Sorts |
Number of sorts that have requested heaps after the sort heap threshold has been exceeded. |
|
Sort Heap Pages Allocated |
Total number of allocated pages of sort heap space for all sorts at the level chosen and at the time the snapshot was taken. |
|
Sort Overflows |
Total number of sorts than ran out of sort heap and may have required disk space for temporary storage. |
|
Total Hash Join Overflows |
Number of times that hash join data exceeded the available sort heap space. |
|
Total Hash Join Small Overflow |
Number of times that hash join data exceeded the available sort heap space by less than 10%. |
|
Total Hash Joins |
Total number of hash joins executed. |
|
Total Hash Loops |
Total number of hash loops executed. |
|
Total Sorts |
Number of sorts that have been executed. |
|
Total Sort Time (ms) |
Time spent in sorts. |
The metrics in this category display the various performance values related to the non-buffered I/O activities that do not use the buffer pool.
Default Collection Interval — Every 15 minutes
Table 2-28 Non-Buffered IO Performance Metrics
| Metric | Description |
|---|---|
|
Database Name (key column) |
Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created. |
|
Average Data Read Rate |
Direct read rate, which equals: direct_reads/direct_read_time The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Average Direct Write Rate |
Direct write rate, which equals: direct_writes/direct_write_time The required value can be set for the warning and critical thresholds to monitor any adverse conditions. |
|
Direct Read Requests |
Number of requests to perform a direct read of one of more sectors of data. |
|
Direct Read Time (ms) |
Elapsed time in milliseconds required to perform the direct reads. |
|
Direct Reads |
Number of read operations that do not use the buffer pool. |
|
Direct Write Requests |
Number of requests to perform a direct write of one or more sectors of data. |
|
Direct Write Time (ms) |
Elapsed time in milliseconds required to perform the direct writes. |
|
Direct Writes |
Number of write operations that do not use the buffer pool. |