Prospective role in Oracle Database Maintenance

Database administrators must always pay close attention to potential database problems that may negatively affect the availability or performance of each system they manage. Generally, the database administrator can monitor and maintain the system in two ways: 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 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 the 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 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.

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

1. Notes for Oracle Database Support
2. Considerations for modifying Oracle Database initialization parameters
3. Three criteria for creating an Oracle database index
4. Character Processing skills in Oracle databases
5. Detailed description of Oracle Database full-text index (1)