Forward-looking introduction to Oracle Database Maintenance

As a qualified Oracle Database Administrator, pay close attention to issues related to Oracle databases that may affect the availability or performance of each system they manage. Under normal circumstances, we can divide database administrators into two ways to monitor and maintain the system: reactive monitoring and proactive monitoring.

As shown in, 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 Oracle 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.

Proactive monitoring allows database administrators to investigate and respond to common Oracle 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 Oracle database fails.

Features of an automatic workload Repository

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 Oracle 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.

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, the data often cannot help the Oracle database administrator to troubleshoot the fault.

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 Oracle database administrator can know as much information as possible to help the Administrator eliminate the problem as soon as possible.