Oracle: real-time monitoring is required for database O & M management

Welcome to the Oracle community forum and interact with 2 million technical staff to enter the core IT business module. The importance of the database is uncertain. After the management database is deployed in the early stage of the system, during the continuous operation of the system, you must always pay attention to the potential impact and possible risks of the system itself and the environment on the operation, and promptly handle the risks. Usually

Welcome to the Oracle community forum and interact with 2 million technical staff> as the core IT business module, there is no doubt about the importance of the database. After the management database is deployed in the early stage of the system, during the continuous operation of the system, you must always pay attention to the potential impact and possible risks of the system itself and the environment on the operation, and promptly handle the risks. Usually

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

As the core IT business module, there is no doubt about the importance of databases.

After the management database is deployed in the early stage of the system, during the continuous operation of the system, you must always pay attention to the potential impact and possible risks of the system itself and the environment on the operation, and promptly handle the risks.

We usually divide database O & M management into two types: reactive and forward-looking.

Reactive Monitoring refers to monitoring the database after a performance or management problem has occurred. For example, it is slow for an employee to report the application system to the database administrator. After consulting with other technical personnel, the data warehouse manager found that it was caused by the database. At this time, the database administrator needs to use related tools to collect database operation data to find out the cause of the problem. Although this can solve the problem smoothly, it is still a little far from the expectations of excellent database administrators. Therefore, reactive monitoring is a bit like a try. The problem has already occurred. Even if the database administrator can identify the cause and solve the problem in the shortest time, it has some adverse effects on the user. Therefore, the database administrator hopes to understand the cause of the fault before the fault occurs, and take effective measures in time to prevent the final occurrence of the fault. This enables forward-looking monitoring that the database administrator expects.

Temporary monitoring allows database administrators to investigate and respond to common database performance and management problems before, during, or after a problem occurs. To put it simply, there will be some signs before a database fault occurs. This is like some natural disasters, such as ant financial and swallow, will have some abnormal responses. It is necessary for the database administrator to understand these symptoms. In this case, we can eliminate these problems and prevent them from expanding.

Oracle Database designers have been working in this direction. For example, in database Versions later than 10 Gb, an automatic workload storage function is available to help database administrators collect abnormal data during database operation. With the help of the data, the database administrator can solve the problem before the database failure occurs.

I. Features of automatic workload Storage Library

The automatic workload repository is implemented by two rollback processes: memory monitor and memory monitoring lamp. These two processes are a pair of twin data, which can be of great help to the database administrator. For example, the two processes work together to directly collect performance statistics from the global zone of the database system. For example, the CPU memory usage of the database server. The memory monitor plays a major role in it. By default, the memory monitor starts every hour and collects performance statistics from the dynamic data performance view, database directory view, and database optimizer, the information is then stored in the database table. This table is called an automatic workload repository table. Generally, this table is owned by the Sysman user and stored in the Sysaux tablespace.

Ii. Enable and configure the automatic workload Repository

If the database administrator needs to enable this automatic workload repository function, it needs to be manually started. By default, the database does not start this function. The author's opinion is that this function does not need to be enabled during database design or testing. After all, it consumes a certain amount of resources on the server. However, it is best to enable this function on the production server (that is, the Oracle database that the enterprise is already using. To help the database administrator automatically collect database operation performance information to achieve the goal of prospective monitoring.

To enable the automatic workload repository function, you need to configure the Statistics_level parameter in the database. This parameter has three values to determine the depth and frequency of statistics collected by the memory monitor process. If the database size is small or the application time is not long, you can set this parameter to Basic. Under this parameter, although the database has enabled the automatic workload repository, it will disable most dispute monitoring and advisor activities for this feature. That is to say, when the database administrator starts a database instance, the system only collects statistics on a small amount of database runtime data. When the database size is large, such data often cannot help the database administrator troubleshoot the cause. If the database design is complex or the enterprise has high database performance requirements, the database administrator can set this parameter to ALL, which is the highest level for the automatic workload storage database to collect statistics. At this level, the memory monitor will capture most of the statistics and collect execution plans and timing information from the operating system. For example, automatic backup of Oracle databases can be completed only with the help of the task plan of the operating system. At this time, the database administrator needs to consider whether the cause of database performance degradation is related to the task plan of the operating system. In this case, the plans and timing information related to the operating system collected by the memory monitor are very useful. However, sometimes the database administrator only needs to collect the operation information of the database, rather than the operating system information. In this case, you can set this parameter to Typical. This parameter is the standard level of the automatic workload repository. It collects statistics related to the database depth.

The database administrator can consider the level to be selected based on factors such as the enterprise's requirements on database performance, the time allowed for database hosting, and server configuration. Generally, if other application services are deployed in addition to the Oracle database on the same server, we recommend that you use All. At this time, the database administrator can know as much information as possible to help the Administrator eliminate the problem as soon as possible.

3. configure appropriate time parameters

After the automatic workload repository is enabled, the database administrator needs to consider time allocation issues. For example, how long does the data in the table of the automatic load repository need to be stored. Because of the number of running statistics of the database, the amount of data varies with its level. However, after using the Typical or All levels, the amount of information is considerable. Therefore, if the data is cleared from time to time, the table does not have much time to occupy much space. Therefore, this table must be recycled to reduce storage space. By default, the content in the automatic load storage table is saved for only seven days, that is, the table stores the database operation information for the last seven days. However, if the database environment design is complex, a large amount of statistical information may be generated, the database administrator needs to shorten the time. Sometimes some abnormal data may occur periodically. Therefore, the database administrator needs to understand the data within several cycles and prolong the cycle. For this reason, the database administrator needs to be able to change the cycle to facilitate his/her work. If the database administrator needs to follow this cycle, he needs to execute the dbms_workload_repostserving program and set the retention parameter. Note that the unit is minute. If the database administrator wants to set this time to 10 days, set this parameter to 60*24*10 = 14400 minutes. Note that the unit is minute. If the Database Administrator does not know the content or mistakenly assumes that this parameter is in the unit of days and sets this parameter to 10, the workload repository table will not record valuable information.

In addition, by default, the memory monitor of the workload repository collects information every 60 minutes. However, sometimes the database administrator may need to shorten the statistical cycle or severe punishment cycle according to the actual situation. For example, you need to shorten the cycle to 10 minutes to determine whether the automatic workload repository is enabled properly. The above program is also used, but the parameter is interval. If you want to adjust the time to 30 minutes, you only need to set this parameter to 30. Note that the unit is minute. If the database administrator wants to keep the workload repository table record for 10 days and collects the database running information every 30 minutes, the following command can be executed: execute Dbms_Workload_Reposttory.modify_snapshot_settings (interval = 30, retention = 14400 ). That is, the above requirements can be fulfilled. I once again stressed that this command is in minutes. In addition, if the statistical cycle is shortened and the storage cycle of the workload storage database table is extended at the same time, the capacity of the table will quickly expand with the combination of the two factors. The database administrator must pay attention to this problem. In general, I suggest you shorten the statistical cycle, but we do not recommend you extend the table Storage cycle. If the database administrator needs performance data for a period of time, the table can be exported for backup. This can avoid the erosion of the workload storage table on the database tablespace. In addition, shortening the statistical cycle of the memory monitor to collect system statistics will increase the additional overhead of the database, which is also worth noting when the database administrator adjusts these two parameters.