Oracle self-monitoring using measurement baseline Technology

For example, the database administrator needs to enable the Oracle database system to perform self-monitoring. When the server's CPU usage reaches, the system automatically sends an alarm to the database administrator. When the tablespace usage reaches 90% of the total capacity, the system automatically notifies the database administrator. These functions are very useful for database administrators. The Enterprise Manager of the Oracle database system provides multiple ways for administrators to monitor the Oracle application environment. The Oracle management agent on each monitored host can monitor the status, performance, and health status of all targets on the host. If the target object fails or its performance reaches the warning threshold, the database system generates an alert and notifies the database administrator through some channels. The self-monitoring function of the database system can replace the eyes of the database administrator.

However, if this self-monitoring function is to take effect, it is inseparable from the measurement baseline. A measurement baseline is a technical indicator that tells the Enterprise Manager to execute an action when an indicator reaches or exceeds this measurement baseline. Therefore, the management of measurement baselines is critical to the self-monitoring function of the database system. If this metric baseline is set to a high value, it cannot be monitored. When the system sends an alarm, the database may have crashed. If this metric baseline is set relatively low, the database will frequently report invalid alerts. Instead, it will cause unnecessary trouble to the database administrator because it is too sensitive. Therefore, the setting of measurement baselines is critical for Oracle databases.

1. Basic Steps for measuring baseline settings

It is not difficult to set a measurement baseline. You can set the measurement baseline in just a few steps. The difficulty lies in how to set it properly. Next I will talk about how to set a reasonable measurement baseline based on the measurement baseline settings.

Go to the Oracle Enterprise Manager and open the create measurement baseline tab. On this tab, you can set all the content of the measurement baseline. Then the database system automatically calculates the threshold value based on the settings here. When creating a measurement baseline, select the date and time range in which the target performance is within the acceptable range under a typical workload. This is the basic criterion for establishing a reasonable measurement baseline. Specifically, to set a measurement baseline, you need to set specific parameters.

The first is the name of the measurement baseline. Because the database can monitor multiple database objects and the Oracle application environment at the same time, a proper name must be given to the measurement baseline. This is of great significance for the subsequent maintenance of the database administrator. The name of the Monitored object is displayed in the baseline name. In this way, the database administrator can clearly know what objects the measurement baseline is mainly used to monitor. In addition, the database administrator can monitor the performance of an object at different times. For example, you may set a baseline to monitor the job at pm to back up the database. Helps Administrators determine the Oracle application environment when database backup jobs occur. In this case, it is best to include time information when defining the metric baseline name. These suggestions can improve the readability of the metric baseline name.

2. Set "hour in one day"

I personally think that this parameter is critical. If the Administrator selects the "hour in one day" parameter, the result value of the user measurement baseline is the cumulative value of the previous few hours. This is useful in database testing. For example, during database design, you may have designed some automated jobs in a certain period of time. The database administrator needs to test whether the system alarm task will be triggered when these jobs occur. The database administrator can set this parameter. For example, the number of hours in a day selected by the Administrator is set to 12. The database system uses data between ten o'clock A.M. and as the basis for measurement. If the average measurement value in an hour exceeds the user-defined percentage, the system will trigger an alarm. Therefore, the flexible setting of this parameter can help administrators understand the database performance of a specific time period or job in a timely manner. It is very useful to measure the impact of Oracle subprograms such as triggers on database performance.

3. Set the warning percentage or critical percentage

Before the system reaches the limit value, it will trigger an alarm after it exceeds a certain percentage. This requires the database administrator to set this parameter based on actual conditions. For example, a user's tablespace capacity may be 10 Gb. When the Data Warehouse administrator monitors this object and sets the baseline of this object to 10 Gb, the Administrator certainly does not want to trigger an alarm when the baseline of this object has reached 10 Gb. At this time, the database system has lost data because the tablespace reaches the maximum limit. The administrator wants to send an alarm to the Administrator when the tablespace usage reaches 90%. This allows administrators to intervene early to prevent unexpected losses. Therefore, the lower and upper limits of metric data can be defined. In a measurement data table, there are two text boxes: lower limit and upper limit. After entering the data in these two text boxes, the system can use these percentages based on the measurement baseline value to calculate the recommended measurement threshold. Note: Enter the percentage value here. If you want to set an alarm when the baseline value is lower than 20%, enter 20. You do not need to enter the percent sign.

This percentage is the most skillful part in the measurement baseline value setting process. Because it is directly related to when an alarm job is triggered. This value is usually set to a reasonable value. There is no standard answer. The author believes that this needs to be adjusted according to the actual situation of the enterprise. For example, the database administrator can set a low baseline threshold at the beginning, and then adjust the threshold based on the validity of the alarm event. This adjustment process may take six months or even longer. Although this process is relatively long, it is worthwhile compared with the benefits of automated monitoring for database administrators.

Iv. Convenient methods for setting measurement baselines

It is difficult to set the measurement baselines of objects one by one. Without basic data, it is difficult for trees to have no rice, and this measurement baseline is more difficult to set. Is there any convenient way to set the measurement baseline? I will introduce some methods here.

First, select an appropriate monitoring object based on the actual application of the enterprise. Enterprise Management has dozens of monitoring objects. The Database Administrator does not need to set the measurement values one by one. The Administrator only needs to set according to the actual situation of the database server. For example, the restrictions on tablespaces should be treated according to different situations. If the user's tablespace has no capacity limit, there is no need to monitor the table space objects. However, the tablespace size is still limited by the maximum disk space capacity. Although there is no need to set a metric value for the user's tablespace size, it is still necessary to monitor the disk usage. If the disk usage reaches a certain level, you still need to warn the database administrator. However, this saves a lot of effort. Because if a database administrator has multiple tablespaces and sets a metric value for each tablespace, the workload will be relatively large. Now, you only need to set a measurement value for a hard disk. Therefore, the first convenient way to set a measurement baseline is to select an appropriate monitoring object. In most cases, you do not need to set a metric value for all objects.

Second, set the measurement value based on the task process of the server operating system. In the operation process of the database server, the database administrator can collect some useful values for the measurement baseline settings. Such as CPU and memory usage. If the database administrator finds that the database performance is degraded, the CPU and memory usage is too high. Then the database administrator can set this metric value on the top of the suspect object. You can also upgrade the memory and other hardware before setting the metric value. This helps the database administrator track the effects of hardware upgrades. Database systems and operating systems are difficult to understand. The performance of the two systems has a great impact on each other. Therefore, the data collected by the server operating system can be used as a reference for Orcle database measurement values. In addition, many server operating systems have configured relevant performance detection parameters. In this case, these parameters can be used as reference data for the database administrator to set the database measurement baseline.

Third, you do not need to set a measurement baseline at the beginning. When the database is running in an enterprise for a period of time, the database administrator can collect related bottleneck resources that affect the database performance. Based on the previous performance, administrators can set reasonable measurement baselines. This may be a practical method for setting measurement baselines for most enterprises. With the development of application software, the database system will become more and more challenging. After a period of use, the database administrator Collects performance-related data based on the previous data. With the support of this pile of data, it is much easier for the Administrator to set a reasonable measurement curve.

1. Auto-tuning in Oracle 11g
2. In-depth analysis of Oracle data block principles
3. Five rules for Oracle database design to improve performance