The Log Utilization alarm becomes active when the percentage of log space currently used by the database is approaching the total amount of log space available for the database.
The underlying metrics for this alarm can be adjusted to suit your environment by using the Metric Editor.
Access the Databases drilldown. The Log Status tab identifies the values of key log configuration parameters as well as the log transaction rate and the average amount of log space used per transaction.
Consider adjusting the LOGFILSZ , LOGPRIMARY , and LOGSECOND database configuration parameters.
The Package Cache Hit Rate alarm becomes active when the package cache hit rate, expressed as a percentage, is low.
Package cache is memory that temporarily stores package and section information required for the execution of static and dynamic SQL statements. When applications obtain static SQL information from package cache, they eliminate I/O on system catalogs. When applications obtain dynamic SQL information from package cache, they avoid the cost of compiling the SQL statements.
Low package cache hit rate means that you are incurring more overhead to reload static SQL and recompile dynamic SQL.
The default thresholds are predefined as follows:
Threshold 1—The zero to 69 percent range. This raises High severity alarm stating, The package cache hit ratio is extremely low.
Threshold 2—The 70 to 79 percent range. This raises a Medium severity alarm stating, The package cache hit ratio is low.
Threshold 3—The 80 to 89 percent range. This raises a Low severity alarm stating, The package cache hit ratio is moderate.
Threshold 4—The 90 to 100 percent range. The package cache hit ratio is high. No alarms are raised.
The underlying metricsfor this alarm can be adjusted to suit your environment by using the Metric Editor.
You can consider increasing the size of package cache using the PCKCACHESZ database configuration parameter. If your database supports heavy transaction processing volumes, your current package cache size might not be able to accommodate these volumes.
The Package Cache Overflows alarm becomes active when package cache inserts have resulted in overflows. An overflow occurs when the limit set by the PCKCACHESZ database configuration parameter is exceeded. The package cache is forced to borrow memory from other entities in database shared memory, causing possible lock list escalation, loss of concurrency, out of memory errors, and overall performance degradation.
This alarm has been predefined with a severity level of 4 (low alarm). The underlying metric for this alarm can be adjusted to suit your environment by using the Metric Editor.
Determine whether or not you need to increase the size of the package cache to avoid overflows. Clicking on the Package Cache element opens the Databases drilldown where you can review statistics for the package cache.
The Lock List Utilization alarm becomes active when the amount of lock list storage used, expressed as a percentage, approaches its maximum size, as defined by the LOCKLIST database configuration parameter.
Lock list is the memory allocated for locks in a database. Once the lock list is full, database performance degrades. Lock escalation increases, reducing concurrency on shared objects in the database. Additionally, because applications are waiting on a limited number of table locks, more deadlocks between applications can occur, causing transactions to be rolled back.
The thresholds are predefined as follows:
Threshold 1—The zero to 69 percent range. The lock list utilization percentage is very low. No alarms are raised.
Threshold 2—The 70 to 79 percent range. This raises a Low severity alarm stating, The lock list utilization percentage is moderate.
Threshold 3—The 80 to 89 percent range. This raises a Medium severity alarm stating, The lock list utilization percentage is high.
Threshold 4—The 90 to 100 percent range. This raises a High severity alarm stating, The lock list utilization percentage is very high.
The underlying metricsfor this alarm can be adjusted to suit your environment by using the Metric Editor.
Perform frequent COMMITs to release locks.
When performing many updates, lock the entire table before updating (using the SQL LOCK TABLE statement). This tactic uses only one lock and keeps others from interfering with the updates. However, it does reduce concurrency of the data.
Use the LOCKSIZE parameter of the ALTER TABLE statement to control how locking is performed for a specific table.
Access the Databases drilldown. The Locking tab allows you to profile the database’s lock list utilization.
Access the Client Applications drilldown. The Locking tab identifies those applications holding locks as well as those waiting for locks.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
