DB2 anomalies

The DB2 collector allows to insert the following types of anomalies in an Anomalies Profile:

Buffer Pool Hit Ratio.

Increasing buffer pool size will generally improve the hit ratio, but you will reach a point of diminishing return. Ideally, if you could allocate a buffer pool large enough to store your entire database, then once the system is up and running you would get a hit ratio of 100%. However, this is unrealistic in most cases. the significance of the hit ratio really depends on the size of your data, and the way it is accessed. A very large database where data is accessed evenly would have a poor hit ratio. There is little you can do with very large tables. In such case, you would focus your attention on smaller, frequently accessed tables, and on the indices. Perhaps, assigning them to an individual buffer pools, for which you can aim for higher hit ratios.

This alarm is raised when the buffer pool hit rate falls below the ranges defined by the thresholds. The default anomaly conditions are:

Severity 1: < 99%
Severity 2: < 95%
Severity 3: < 90%

Index hit ratio.

If the hit ratio is low, increasing the number of buffer pool pages may improve performance.

This alarm is raised when the index hit rate falls below the ranges defined by the thresholds.

Severity 1: < 99%
Severity 2: < 95%
Severity 3: < 90%

Package hit ratio.

The package cache hit ratio tells you whether or not the package cache is being used effectively. If the hit ratio is high (more than 0.8), the cache is performing well. A smaller ratio may indicate that the package cache should be increased.

You will need to experiment with the size of the package cache to find the optimal number for the pckcachesz configuration parameter. For example, you might be able to use a smaller package cache size if there is no increase in the pkg_cache_inserts element when you decrease the size of the cache. Decreasing the package cache size frees up system resources for other work. It is also possible that you could improve overall system performance by increasing the size of the package cache if by doing so, you decrease the number of pkg_cache_inserts. This experimentation is best done under full workload conditions.

You can use this element with ddl_sql_stmts to determine whether or not the execution of DDL statements is impacting the performance of the package cache. Sections for dynamic SQL statements can become invalid when DDL statements are executed. Invalid sections are implicitly prepared by the system when next used. The execution of a DDL statement could invalidate a number of sections and the resulting extra overhead incurred when preparing those sections could significantly impact performance. In this case, the package cache hit ratio reflects the implicit recompilation of invalid sections. It does not reflect the insertion of new sections into the cache, so increasing the size of the package cache will not improve overall performance. You might find it less confusing to tune the cache for an application on its own before working in the full environment.

It is necessary to determine the role that DDL statements are playing in the value of the package cache hit ratio before deciding on what action to take. If DDL statements rarely occur, then cache performance may be improved by increasing its size. If DDL statements are frequent, then improvements may require that you limit the use of DDL statements (possibly to specific time periods).

The static_sql_stmts and dynamic_sql_stmts counts can be used to help provide information on the quantity and type of sections being cached.

This alarm is raised when the package hit rate falls below the ranges defined by the thresholds.

Severity 1: < 80%
Severity 2: < 75%
Severity 3: < 70%

Catalog hit ratio.

It indicates how well the catalog cache is avoiding catalog accesses. If the ratio is high (more than 0.8), then the cache is performing well. A smaller ratio might suggest that the catalogcache_sz should be increased. You should expect a large ratio immediately following the first connection to the database.

The execution of Data Definition Language (DDL) SQL statements involving a table, view, or alias will evict the table descriptor information for that object from the catalog cache causing it to be re-inserted on the next reference. In addition, GRANT and REVOKE statements for database authorization and execute privilege of routines will evict the subject authorization information from the catalog cache. Therefore, the heavy use of DDL statements and GRANT/REVOKE statements may also increase the ratio.

This alarm is raised when the catalog hit rate falls below the ranges defined by the thresholds.

Severity 1: < 80%
Severity 2: < 75%
Severity 3: < 70%





Go back to the Learning center:


Copyright © AgileLoad. All rights reserved.
Agile Load testing tool| Contact AgileLoad | Terms of Use | Sitemap